I always have a hard time with the quotes.
Yes, they can cause confusion at times
This is the same problem - you are closing the quote before the /and/:
... & "A" & "'" and ...
It should be:
"empid = '" & PassID & "' and leavetype = '" _
& "A" & "' and leavedate Between " _
& "#9/1/2004# And DateSerial(Year(Date()),Month(Date())-2,0)"
Will you always be checking for leavetype='A', or will that 'A' be replaced
by a variable? If it's always 'A' then you can include it in the main
string:
"empid = '" & PassID & "' and leavetype = 'A' and leavedate Between " _
& "#9/1/2004# And DateSerial(Year(Date()),Month(Date())-2,0)"
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
JMorrell said:
Thanks for the quick reply, that part works just fine. The problem now is
when I try to apply more criteria - another "and" statement. There is a
compile error when I use the following:
Nz(DSum("LeaveHours", "tblEmpLeave", _
"empid = '" & PassID & "' and leavetype = '" & "A" & "'"
and
Leavedate Between " & "#9/1/2004# And
DateSerial(Year(Date()),Month(Date())-2,0)"))
I always have a hard time with the quotes.
thanks again.
--
JMorrell
Graham Mandeno said:
Hi JM
Your criteria argument string has the quotes in the wrong place.
Specifically, the /And/ is outside of the quotes, so it is being treated
as
the logical And operator by VBA and never gets to SQL.
Try this:
"empid = '" & PassID & "' And leavedate Between " _
& "#9/1/2004# And DateSerial(Year(Date()),Month(Date())-2,0)"
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
in an event for a report, I need to capture numbers from a table. The
following is what I'm using, which doesn't work. I think the problem
is
within the "between 09/01/05 and dateserial..... "
PrevNumber = Nz(DSum("AUpdateVal", "tblEmpLeave", _
"empid = '" & PassID & "' " And "leavedate Between
#9/1/2004# And DateSerial(Year(Date()),Month(Date())-2,0)"))
Can someone please help me out with the proper syntax?
tia
