CODEWITHSUNDEEP
sqlvbams-access
finny
finny

Reputation: 21

Convert Access query with DateDiff() to run from VBA

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.

Upvotes: 1

Views: 1479

Answers (1)

Smandoli
Smandoli

Reputation: 7019

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.

Upvotes: 2

Related Questions