Convert Access query with DateDiff() to run from VBA

Question

I have been searching for a solution for days now but could not find anything anywhere. I have Excel interacting with an Access Database. The following query works in Access:

SELECT DISTINCT A.Timestamp, A.Identifier, A.Remarks, A.ActiveIndicator, 
    A.S4PDate, B.Timestamp, B.Identifier, B.Remarks, B.ActiveIndicator, B.S4PDate
FROM AAEPData AS A INNER JOIN AAEPData AS B ON A.Identifier = B.Identifier
WHERE (((A.Timestamp = '20131028' AND B.Timestamp = '20131014')) 
    AND ((datediff('d', A.S4PDate, B.S4PDate)) <> 0))
    ORDER BY A.Identifier;

Problem is how to convert this to "VBA-SQL". It should look something like this but I am having trouble with the datediff part of it. How do I handover the interval in days correctly? Here is my VBA code:

sql = "SELECT DISTINCT A.Identifier, A.Remarks, A.ActiveIndicator, A.S4PDate,"
sql = sql & " B.Identifier, B.Remarks, B.ActiveIndicator, B.S4PDate "
sql = sql & "FROM AAEPData AS A "
sql = sql & "INNER JOIN AAEPData AS B ON ((A.Identifier = B.Identifier))"
sql = sql & "WHERE (((A.Timestamp = '20131028' AND B.Timestamp = '20131014')) "
sql = sql & "AND (Datediff('d', A.S4PDate, B.S4PDate) <> 0))) "
sql = sql & "ORDER BY B.Identifier ASC;"

When I do it like this it does not return values but does also not give an error. And I definetly do have changes in the date values S4PDate that I am querying for. Any ideas? Your help is really very much appreciated. Thanks.

Single quotes count as Comment indicators in VBA. Change them to double quotes (DateDiff("d",...) and let us know what happens. — craig.white, Nov 25 '13 at 17:02
@craig.white -- changing those quote marks will definitely cause an error -- it will devide the string on a single line, without syntax support. — Smandoli, Dec 02 '13 at 22:52

score 2 · Answer 1 · edited May 23 '17 at 10:25

Right after the SQL variable is complete, add this line:

Debug.Print sql

The complete SQL will appear in the Immediate window. Take that and paste it in as the basis for an ordinary query (not VBA) in Access. See if the query runs.

I see a defect -- you need a space at the end of one line, as seen here:

"FROM AAEPData AS A INNER JOIN AAEPData AS B ON ((A.Identifier = B.Identifier)) "

If after fixing that your test query still fails, simplify your query. Omit the use of DateDiff(). Get it down to something that runs. Then build back up again, making corrections as needed. This is normal trouble shooting procedure.

Convert Access query with DateDiff() to run from VBA

1 Answers1