Function to return dates prior to inputed, or if null, prior to no

G

Guest

As the subject says, I am trying to get a query to return anything prior to a
date that is input or if no date is input, I would like it to return those
dates that are prior to now(). I have this working for a combo box on the
form with the following
Like IIf(nz([forms]![PAST_DUE]![cboINSPECTIONTYPE],"")<>"","*" &
[forms]![PAST_DUE]![cboINSPECTIONTYPE] & "*","*")

but I am having trouble getting something to work for a date text box. The
date text box is on the same form [PAST_DUE] and is txtDATE. If someone
could help me out with getting this to work and maybe explain a little more
to me about what I used above as I got help to put it together and I
obviously don't understand it :) Thanks.
 
J

John Spencer

Use Nz and Date()

Nz replaces a null value with the specified value, so the criteria could be

< NZ(Forms!Past_Due!txtDate,Date())

Your other criteria seems to be unnecessarily complex to me. It should work
the same with this simpler version

Like "*" & Forms!Past_Due!cboInspectionType & "*"

Explanation:
"*" is a wildcard character that means any number of any character
(including zero characters)
The above concatenates (combines together) an * with whatever is selected in
cboInspectionType and another *. If cboInspectionType is null or blank you
end up with "**" which basically tells the program to search for a field
that has any number of characters in it (including zero characters). The
one trick here is that NULL is not zero characters so fields that have null
as a value will not be selected.
 
G

Guest

Thank you very much, can't believe it was that easy.....thanks for the advice
Use Nz and Date()

Nz replaces a null value with the specified value, so the criteria could be

< NZ(Forms!Past_Due!txtDate,Date())

Your other criteria seems to be unnecessarily complex to me. It should work
the same with this simpler version

Like "*" & Forms!Past_Due!cboInspectionType & "*"

Explanation:
"*" is a wildcard character that means any number of any character
(including zero characters)
The above concatenates (combines together) an * with whatever is selected in
cboInspectionType and another *. If cboInspectionType is null or blank you
end up with "**" which basically tells the program to search for a field
that has any number of characters in it (including zero characters). The
one trick here is that NULL is not zero characters so fields that have null
as a value will not be selected.

kcsims said:
As the subject says, I am trying to get a query to return anything prior
to a
date that is input or if no date is input, I would like it to return those
dates that are prior to now(). I have this working for a combo box on the
form with the following
Like IIf(nz([forms]![PAST_DUE]![cboINSPECTIONTYPE],"")<>"","*" &
[forms]![PAST_DUE]![cboINSPECTIONTYPE] & "*","*")

but I am having trouble getting something to work for a date text box.
The
date text box is on the same form [PAST_DUE] and is txtDATE. If someone
could help me out with getting this to work and maybe explain a little
more
to me about what I used above as I got help to put it together and I
obviously don't understand it :) Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top