Are " not allowed in SQL?

[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.

 

[Douglas J. Steele]

Try:

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

 

[John Vinson]

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.

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()) & "#")

 

[Steve Schapel]

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

 

[Jesper F]

Thanks very much all of you. I got it working