Sending a date parameter in a sql string call

G

Guest

I am selecting a table called Holiday and I need to loop thru 'x' amount of
days to see if days fall on any holidays. There is a holiday but the sql
statement I am running doesn't find it. It returns no records when there IS
at least one.

I know it has to do with the " " again but I can't figure this out. Any
help is appreciated!!!! :)

Here is the code:
strSQL = "SELECT Holiday.* FROM Holiday WHERE Holiday.HolidayDate = " &
dCheckDate

Thanks!

Janis in Minne-SNOW-ta
 
J

John Spencer

You need to surround the dCheckDate with date delimiters (# signs)

strSQL = "SELECT Holiday.* FROM Holiday WHERE Holiday.HolidayDate = #" &
dCheckDate & "#"
 
D

Douglas J Steele

And if your Short Date format (in Regional Settings in the Control Panel)
isn't mm/dd/yyyy, you could run into problems.

To prevent problems, I'd suggest

strSQL = "SELECT Holiday.* FROM Holiday " & _
"WHERE Holiday.HolidayDate = " & _
Format(dCheckDate, "\#mm\/dd\/yyyy\#")
 
G

Guest

Thanks - I will make sure! :)

Douglas J Steele said:
And if your Short Date format (in Regional Settings in the Control Panel)
isn't mm/dd/yyyy, you could run into problems.

To prevent problems, I'd suggest

strSQL = "SELECT Holiday.* FROM Holiday " & _
"WHERE Holiday.HolidayDate = " & _
Format(dCheckDate, "\#mm\/dd\/yyyy\#")
Click to expand...
 

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

Similar Threads

Sorting in a SQL statement 5
SQL query expression - syntax error 4
Does date in Cell A1 occur between the date in M1 and Date in X1 1
returning a date 7 business day excluding holidays in the future 2
Working out a future date with formulas 3
Date Question! 3
SQL string in VBA 4
Apostrophe Messing Up SQL String 2

Top