[AccessD] Problem Making Query

[Rocky Smolin]

The query when both boxes are checked was easy - just a select - unique
values=True.

The problem came when I tried to make a list of projects where ALL
activities in a project are Complete  and another one which would make a
list of projects where ANY activity in the project was Active.

R


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
David McAfee
Sent: Friday, December 11, 2015 10:16 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Problem Making Query

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]![frmDa
> tabaseMapping]![fraShowProjects])
> or
>
> IIf([forms]![frmDatabaseMapping]![fraShowProjects]=2,0,[forms]![frmDat
> abaseMapping]![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
>>
>
>
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus