[AccessD] Problem Making Query

[David McAfee]

if you can't get the where clause working, you can make a 3rd query, which
shows all, and use that querywhen both checkboxes are checked.

On Fri, Dec 11, 2015 at 9:59 AM, David McAfee <davidmcafee at gmail.com> wrote:

> Wow, I didn't even press send! WTF!
>
>
> I have a form where I used a list box with 3 optional boxes in a frame.
> Active, Complete and Both are the options (0, -1 & 2 are the values
> respectively).
>
> The query has a Boolean ID field that it looks at:
>
> IIf([forms]![frmDatabaseMapping]![fraShowProjects]=2,-1,[forms]![frmDatabaseMapping]![fraShowProjects])
> or
>
> IIf([forms]![frmDatabaseMapping]![fraShowProjects]=2,0,[forms]![frmDatabaseMapping]![fraShowProjects])
>
>
>
> If the client doesn't want a framed optional box, you can have a hidden
> text box with a value.
>  In the after_update event of each check box, call a sub named CalcStatus:
> (warning all air code)
>
> Private Sub CalcStatus
>    me.txtShowStatus = abs(nz(me.chkActive,0)) +  abs(nz(me.chkComplete,0))
> end sub
>
> Then in your query
> switch([forms]![frmMyForm]![txtShowStatus ] = 1,"COMPLETE",
> [forms]![frmMyForm]![txtShowStatus ] = 2, "Active",
> [forms]![frmMyForm]![txtShowStatus ] =3,"*")
>
>
>
> On Fri, Dec 11, 2015 at 8:50 AM, Rocky Smolin <rockysmolin at bchacc.com>
> wrote:
>
>> Dear List:
>>
>> I need some help structuring 2 queries and can't seem to figure how to do
>> it.
>>
>> I have two tables: Projects and Project Activities, where Project
>> Activities
>> has the FK of Project ID - PK in the project table.
>>
>> Activities table has a status field 'ACTIVE' or 'COMPLETE'
>>
>> The client wants to add checkboxes on the calling form - one labeled
>> 'Active', one  labeled 'Complete', so that the combo box of Projects on
>> the
>> called form will show only Active projects, only Complete projects or
>> both.
>>
>> Showing both is easy - I already had that before he thought up this dandy
>> enhancement
>>
>> A project is Active if ANY project activity is status = ACTIVE.
>>
>> A project is complete if ALL activities are status = COMPLETE.
>>
>> So I'm trying to create two queries - one which would list active projects
>> and one which would list complete project per the definition above then
>> I'll
>> change the row source of the combo box based on which boxes are checked.
>>
>> But I've been fooling around with it long enough without success that I
>> think I need some help.
>>
>> (help!)
>>
>> MTIA,
>>
>> Rocky Smolin
>> Beach Access Software
>> 858-259-4334
>> www.bchacc.com <http://www.bchacc.com/>
>> www.e-z-mrp.com <http://www.e-z-mrp.com/>
>> Skype: rocky.smolin
>>
>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>
>
>