DateDiff in SQL

[E. D.]

Hi
I'm using DateDiff() in an expression in a query which works well until I
try to use the SQL as the source of a report. (I'm using Access 2K)

I get an error - the "d" is highlighted and an error messgae of "Expected
end of statement"
DateDiff("d", [InDate], Now())

I've included a cut down version of the SQL cade as the original code is too
long

SELECT Tbl_JobCodes.JobCode, Tbl_CouponsDates.InDate,
DateDiff("d",[InDate],Now()) AS [Days since InDate]
FROM Tbl_JobCodes LEFT JOIN Tbl_CouponsDates ON Tbl_JobCodes.JobCode =
Tbl_CouponsDates.JobCode
WHERE (((DateDiff("d",[InDate],Now()))<=90))
ORDER BY Tbl_CouponsDates.InDate;

Can anyone help?
Thanks in anticipation

 

[Allen Browne]

Is the SQL statement part of a string in code?

If so, you have to double-up the embedded quotes, so VBA knows it is not the
end of the string, i.e.:
strSQL = "SELECT ... WHERE (((DateDiff(""d"",[InDate],Now()))<=90)) ..."

As an analogy, it's like:
strMyString = "This string has a ""word"" in quotes."

 

[E. D.]

Thanks Allen
amazing what a quotation mark or two can do! Problem solved.
Cheers

Is the SQL statement part of a string in code?

If so, you have to double-up the embedded quotes, so VBA knows it is not the
end of the string, i.e.:
strSQL = "SELECT ... WHERE (((DateDiff(""d"",[InDate],Now()))<=90)) ...."

As an analogy, it's like:
strMyString = "This string has a ""word"" in quotes."

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi
I'm using DateDiff() in an expression in a query which works well until I
try to use the SQL as the source of a report. (I'm using Access 2K)

I get an error - the "d" is highlighted and an error messgae of
"Expected
end of statement"
DateDiff("d", [InDate], Now())

I've included a cut down version of the SQL cade as the original code is
too
long

SELECT Tbl_JobCodes.JobCode, Tbl_CouponsDates.InDate,
DateDiff("d",[InDate],Now()) AS [Days since InDate]
FROM Tbl_JobCodes LEFT JOIN Tbl_CouponsDates ON Tbl_JobCodes.JobCode =
Tbl_CouponsDates.JobCode
WHERE (((DateDiff("d",[InDate],Now()))<=90))
ORDER BY Tbl_CouponsDates.InDate;

Can anyone help?
Thanks in anticipation