Conditional Query using Between

[wardster]

Hi - I wonder if anyone could possibly help, I have a list box that is populated by a query, which currently populates the list according to which site is selected within a combobox - what i would like to do in addition is to have a check box which controls how much data is shown, ie, if the checkbox is ticked show all data from April 1st onwards, if not ticked to only show the last 12 weeks (84 days)

I have tried to acheive this using the code below:

[Forms]![frmPccAnalysis]![cboSelect] --is used to select the site--

IIf([Forms]![frmPccAnalysis]![chkFilterLimit]=-1,(Between #01/04/2006# And Now()),(Between (Now()-84) And Now())) --is what i am trying to use to either restrict or show all data--

when i run the code i just get empty fields, if i manually enter the "Between #01/04/06# and Now()" statement in the criteria it works fine, just not with the conditial checkbox. Can anyone please point me in the right direction?

Many Thanks

Ian

 

[neileg]

You'll have to build the query in code, I think.

 

[wardster]

Hi Neil,

Thanks for the reply, where you say i would probably have to build the query in code, i am a little unsure of what you mean, would i have to create the code as SQL in the vb window on an event for example on load or on update?

Thanks

Ian

 

[neileg]

No, I mean you'll need to build up a text string, concatenating your parameters and then use DoCmd.RunSQL to treat this text string as a query.

If you do a search on DoCmd.RunSQL you'll find lots of examples in these forums.

 

[wardster]

Thanks for your help Neil, will have a read up on that

Regards

Ian