Are " not allowed in SQL?

  • Thread starter Thread starter Jesper F
  • Start date Start date
J

Jesper F

I have this SQL:

Set rs = db.OpenRecordset("SELECT tbl.* FROM tbl WHERE
((tbl.field) = DateAdd("d", 7, Date())

apparently the syntax is not accepted. Can I have a
dateadd in SQL? Specifically it's the quotes that cause
the problem.
 
Try:

Set rs = db.OpenRecordset("SELECT tbl.* FROM tbl WHERE
((tbl.field) = " & DateAdd("d", 7, Date())))
 
I have this SQL:

Set rs = db.OpenRecordset("SELECT tbl.* FROM tbl WHERE
((tbl.field) = DateAdd("d", 7, Date())

apparently the syntax is not accepted. Can I have a
dateadd in SQL? Specifically it's the quotes that cause
the problem.
Click to expand...

You have unbalanced quotes. The " before the d in the DateAdd() is
being taken as the closing quote, and the " after it opens a new
quoted string.

Try doubling up the single quotes:

Set rs = db.OpenRecordset("SELECT tbl.* FROM tbl WHERE
((tbl.field) = DateAdd(""d"", 7, Date())")

or as suggested, pull the date reference out of the SQL string
altogether: to do so you need the # delimiter for date literals.

Set rs = db.OpenRecordset("SELECT tbl.* FROM tbl WHERE
((tbl.field) = #" & DateAdd("d", 7, Date()) & "#")
 
Jesper,

I don't think it's the ""s in the DateAdd function that are causing
the problem. I think it's the lack of the closing " in the SQL
string, and the lack of two closing )s. Try...
Set rs = db.OpenRecordset("SELECT tbl.* FROM tbl WHERE
((tbl.field) = DateAdd("d", 7, Date()))")

- Steve Schapel, Microsoft Access MVP
 
Back
Top