Need some help with a IIF statement.

[soupisgood84]

I have a IIF statement (seen below) that will request input but still run the query if no input is received. I would like to incorporate this into a "between...and..." for a date range and also a price range. I have tried quite a few combinations but cant seem to get it right.

Any suggestions will be greatly appreciated.

Statement:
IIf(IsNull([Forms]![frmBudget-Unbound]![txtReqNumber]),[RequisitionNumber],[Forms]![frmBudget-Unbound]![txtReqNumber])

 

[soupisgood84]

I probably should have stated that this is running from a form, not directly from a query.

 

[Remou]

You can build your own SQL string.

strSQL="SELECT * FROM tblTable "

If Nz([Forms]![frmBudget-Unbound]![txtReqNumber])>0 Then
   strSQL=strSQL & " WHERE [RequisitionNumber]=" & [Forms]![frmBudget-Unbound]![txtReqNumber]
End If

Set qdf=CurrentDB.QueryDefs("qryQuery")
qdf.SQL=strSQL
DoCmd.OpenQuery qdf.Name

 

[soupisgood84]

Thats not quite what i am looking for.
I would like to incorporate the IIF statement directly into the query so that I can search via date, but so that I am not required if I don't want to.

 

[Remou]

The code above includes the If statement in a query and then opens that query. It just starts from a different place.

 

[soupisgood84]

I have the statement working fine for the other criteria, but I need to work out something like this:

IIf(IsNull [b]Between[/b]([Forms]![frmBudget-Unbound]![txtStartDate]),[StartDate],[Forms]![frmBudget-Unbound]![txtStartDate]) [b]And[/b]([Forms]![frmBudget-Unbound]![txtEndDate]),[EndDate],[Forms]![frmBudget-Unbound]![txtEndDate] )

But obviously this doesn't work.

 

[PHV]

Try this criteria:
Between IIf(IsNull([Forms]![frmBudget-Unbound]![txtStartDate]),[StartDate],[Forms]![frmBudget-Unbound]![txtStartDate]) And IIf(IsNull([Forms]![frmBudget-Unbound]![txtEndDate]),[EndDate],[Forms]![frmBudget-Unbound]![txtEndDate])

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

 

[PHV]

Another possible criteria:
Between Nz([Forms]![frmBudget-Unbound]![txtStartDate],[StartDate]) And Nz([Forms]![frmBudget-Unbound]![txtEndDate],[EndDate])

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

 

[soupisgood84]

Thanks for the quick responce.
I have tried those combinations before, but with those you are required to enter a range. What I am trying to shoot for is the option to enter a range and still query on other criteria.

For example: if I left the date range blank along with all other ranges, the query would display all data in its respective table.

 

[soupisgood84]

bump