CODEWITHSUNDEEP
sqlvbams-access
honey_badgerzz
honey_badgerzz

Reputation: 115

How to assign SQL MAX(date) to a VBA variable

I am attempting to insert records into a table(cohort) based off of a date(backdate). The backdate variable in VBA should first grab the max date from the SQL query to later be used in the INSERT INTO query in the WHERE clause.

When I attempt to run the function I get a type mismatch error when I try to declare backdate. My code is below.

Dim backdate As Date
Dim sqlString As String

backdate = "SELECT MAX(letter.report_date) " & _
            "FROM fadav_letter_recipients as letter ;"

sqlString = "INSERT INTO cohort(person_id, report) " & _
                "SELECT letter.person_id, letter.report_type " & _
                "FROM fadav_letter_recipients as letter " & _
                "WHERE " & backdate & " > letter.report_date;"
DoCmd.RunSQL sqlString

Any help is appreciated

Upvotes: 0

Views: 1057

Answers (2)

Alvaro CC
Alvaro CC

Reputation: 142

you get the type mismatch error because you declared backdate as "Date" and you later tried to assign a "string" value to it

There is also a conception error in your code , you need to use a "recordset" object to get what you want

Could you try this code ?:

Dim rec As ado.recordset
Dim sqlString As String
Dim backdate As Date

set rec= currentdb.openrecordset ("SELECT MAX(letter.report_date) " & _
            "FROM fadav_letter_recipients as letter ;")
backdate=rec(0)

sqlString = "INSERT INTO cohort(person_id, report) " & _
                "SELECT letter.person_id, letter.report_type " & _
                "FROM fadav_letter_recipients as letter " & _
                "WHERE #" & backdate & "# > letter.report_date;"
DoCmd.RunSQL sqlString

Upvotes: 1

Gustav
Gustav

Reputation: 56016

Lookup the value:

backdate = DMax("report_date", "fadav_letter_recipients")

Then format the date value as a date expression:

"WHERE #" & Format(backdate, "yyyy\/mm\/dd") & "# > letter.report_date;"

Upvotes: 1

Related Questions