dateadd embedded in dlookup problem

[crusty]

Hi,
I have a query with a dlookup, in the dlookup is a dateadd adding days to a
parameter date, which is not working. The problem seems to be with the
Dateadd.
here is the dlookup:
Expr3: DLookUp("[approved]","tblLeave"," tblLeave.EmployeeID=5 AND [Start
Date]<= dateadd('d',17," & [mdate] & " ) AND Day([End Date])>=18")
mdate is the date parameter.
Any ideas?
Thanks
Adrian

 

[John Spencer]

Try the following. You need to add the "#" delimiter to the criteria so MDate
will be interpreted as a date.

Expr3: DLookUp("approved","tblLeave"
,"EmployeeID=5 AND [Start Date]<= dateadd('d',17,#" & [mdate] & "#) AND
Day([End Date])>=18")

 

[crusty via AccessMonster.com]

Thanks for the response John,
Unfortunatly, that still doesn't work.
I have confirmed that
dateadd('d',17, [mdate] )
works ok, and all the rest of the querie works ok,
It just seems to be the dateadd within the Dlookup having [mdate]
outside of the inverted commas that seems to be the problem.
I've tried putting mdate inside the inverted commas, but access complains.
[mdate] has been declared a date/time in parameters.

Thanks
Adrian

Try the following. You need to add the "#" delimiter to the criteria so MDate
will be interpreted as a date.

Expr3: DLookUp("approved","tblLeave"
,"EmployeeID=5 AND [Start Date]<= dateadd('d',17,#" & [mdate] & "#) AND
Day([End Date])>=18")

 

[John Spencer]

Try a couple things.

--Use two quotation marks in place of the apostrophe.
--Use CDate around MDate to make sure MDate is being treated as a date.

Expr3: DLookUp("approved","tblLeave"
,"EmployeeID=5 AND [Start Date]<= DateAdd(""d"",17, #" & CDate([mdate]) & "#) AND
Day([End Date])>=18")

If that doesn't work, try posting the entire SQL statement. (Menu View: SQL)

Thanks for the response John,
Unfortunatly, that still doesn't work.
I have confirmed that
dateadd('d',17, [mdate] )
works ok, and all the rest of the querie works ok,
It just seems to be the dateadd within the Dlookup having [mdate]
outside of the inverted commas that seems to be the problem.
I've tried putting mdate inside the inverted commas, but access complains.
[mdate] has been declared a date/time in parameters.

Thanks
Adrian

Try the following. You need to add the "#" delimiter to the criteria so MDate
will be interpreted as a date.

Expr3: DLookUp("approved","tblLeave"
,"EmployeeID=5 AND [Start Date]<= dateadd('d',17,#" & [mdate] & "#) AND
Day([End Date])>=18")

 

[crusty via AccessMonster.com]

Thanks for the advise, John.
Still couldn't get it to work,
Time constraints led me to abandon the dLookUp and reworked Tables and
queries to avoid using it.

All working now.

Still don't know why it didn't work, maybe a bug?
Anyway, thanks again for your time and imput.

Regards
Adrian

 

[John Spencer]

Could have been something as simple as EmployeeID being a text field and not
a number field?