Date Criteria

[chippyles]

I would like to put a BETWEEN START DATE AND END DATE in the criteria of a query I have. The start and end will be from an unbound form, but the person does not have to enter the start or end date all the time.

Between (Like nz([forms].[fQA_Raw_Materials].[START],"*")) And (Like nz([forms].[fQA_Raw_Materials].[END],"*"))

This does not work for me. What cado to make this work?

 

[ChopinFan]

Try this:

IIF(not isnull([forms]![fQA_Raw_Materials]![start]),between [forms]![fQA_Raw_Materials]![start] and [forms]![fQA_Raw_Materials]![end])

It isn't real pretty, but it should do the job. Note two things here:
1. If someone enters a start date, this assumes there should be a corresponding end date, so it only tests for one of them.
2. There is no else clause in the iif statement, so it should (I would think) ignore the entire parameter if the start date is null and just pull all values.

Hope this helps

 

[PHV]

Between Nz([Forms]![fQA_Raw_Materials]![START],#01/01/1900#) And Nz([Forms]![fQA_Raw_Materials]![END],#12/31/2999#)



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244