Adding multiple Truepart Value in IIF statement.

[binbin]

Hello everyone,

I need help with this. Forgive me if this has been asked before, but I could not find a correct solution when googling.

I have a query that has a DateArrived field. In it's criteria, I wanted to return records equal or between certain dates if a condition has been met, but returns all the records if the condition was not met.

Like IIf([TempVars]![tempType]="Dates",>=[Forms]![frmSearch]![txtStartDate] And <=[Forms]![frmSearch]![txtEndDate],"*")

The "*" value works and will return all records when the condition was not met, however, when the condition has been met or true, no records are returned.



So basically, I'm asking how to correctly put multiple "trueparts" in an IIf statement?

Thank you for your time.

[lfpm062010]

Your codes are mixing "LIKE" and ">= and <=".

This is one way to get around it.
Between IIf([TempVars]![tempType]="Dates",[Forms]![frmSearch]![txtStartDate],CDate("1/1/1900")) And IIf([TempVars]![tempType]="Dates",[Forms]![frmSearch]![txtEndDate],CDate("12/31/9999"))

[binbin]

Thank you so much lfpm062010 for this reply and solution, I have tried it and it works beautifully!

Also I found another solution while waiting for an answer, I tried going the VBA way (I was avoiding VBA codes) and found out that I can use the where condition in the docmd.openform function and did it like this... I forgot to say that the query was for a form.

DoCmd.OpenForm "frmSearchResultList", , , "[DateArrived]>=#" & [txtStartDate] & "# and [DateArrived]<=#" & [txtEndDate] & "#"

This one also works.

Thanks for the time and solution!