IIF Statement on Between Dates - From Form 1

[filthepitome]

I have a user form with two fields

StartDate
EndDate

I have a query that returns results based on this users input into these fields. If the user does not enter a value into the [StartDate] field I would like the query to return everything. I have tried everything I can think of with no luck. This is my most recent attempt

IIf([Forms]![CS_QueryForm]![DateOpenedStart] Is Null,Between #1/1/1900# And #12/31/3000#,Between [Forms]![CS_QueryForm]![DateOpenedStart] And [Forms]![CS_QueryForm]![DateOpenedEnd])

 

[SkipVought]

Hi,

Between is not an operator in the IIF() function.

Use the form...
[tt]
IIF([field]>=value1 AND [field]<=value2,trueresult,falseresult)
[/tt]


Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[PHV]

You may use this:
Between Nz([Forms]![CS_QueryForm]![DateOpenedStart],#1900-01-01#) And Nz([Forms]![CS_QueryForm]![DateOpenedEnd],#3000-12-31#)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[filthepitome]

AWESOME!!!!!! Thanks for the assistance