Query criteria to pick range of date dependent on variable

[hughesje]

Hello,

I am trying to write a query from a main table which will show records which have a date of less than or equal to a Variable (tempvar), however the variable (selected from a form) may be left blank in which case all records should be shown.

I have successfully used the following but the records returned are only the specific date choosen not that date and all prior

Like Nz(([TempVars]![tvcldate]),"*")

So I have tried the following but it doesn't work, any ideas pelase.

Like Nz(<=([TempVars]![tvcldate]),"*")

Thanks,

 

[bob fitz]

Have you tried:
<=[TempVars]![tvcldate]

 

[hughesje]

Have you tried:
<=[TempVars]![tvcldate]

That works where a variable is selected but causes an error (or returns no records) where variable is NULL.

 

[bob fitz]

How about:
<=Nz([TempVars]![tvcldate],"*")

 

[hughesje]

How about:
<=Nz([TempVars]![tvcldate],"*")

Yes I tried this first, it works where a date is selected but when left blank i get the error message per attached

 

[bob fitz]

What records do you want the query to return if the variable is Null.

 

[hughesje]

What records do you want the query to return if the variable is Null.

All records should show if no date is selected in the Nz I believe this is represented by the "*", this bit does seem to work fine with the original formula Like Nz(([TempVars]![tvcldate]),"*") but off course without the date range when a date is selected.

Was thinking perhaps could do some other logical test eg:

Iif ([TempVars]![tvcldate]) is null,"*",<= ([TempVars]![tvcldate]))
... but cant get this to work either

 

[bob fitz]

Try:
<=Nz([forms]![TempVars]![tvcldate],"'*'")

 

[hughesje]

Try:
<=Nz([forms]![TempVars]![tvcldate],"'*'")

Tempvars is not in the form itself, it is a variable set by the form, so that doesn't work, it needs to work that way as there are multiple users and the tempvars is user specific whilst the form value is not and one users change would effect the others.

 

[hughesje]

ok think I have it, it was a logic problem really, I am using :
<=Nz(([TempVars]![tvcldate]),#31/12/2100#)

using #31/12/2100# instead of "*", it would seems it was testing <= against "*", which errors, whereas the original function was using "*" to just return all records

Thanks for your help Bob

 

[bob fitz]

Your welcome. Sorry I couldn't be of more help. Glad youv'e got it working. Good luck with your project.