CODEWITHSUNDEEP
sqlsql-serverms-accessvba
user4095610
user4095610

Reputation: 41

String concatenation in Access VBA

I am trying to write a macro that will create a query based on user input. The user needs to input a date at the start of the macro and the macro will then incorporate that date into a SQL query. The problem is I am unsure how to incorporate the date into the SQL SELECT statement. I was thinking of using string concatenation but I am unsure how to do so in the SQL statement.

Sub RevH()
Dim dte As String, clientQry As String, db As Database, clientQry1 As Variant


Set db = CurrentDb


dte = InputBox("What date was the Data Dump run?", "Please Input a date")


clientQry = "SELECT DISTINCT " & _
"FN_DataDump_ALL_11032014.[CLIENT ID], " & _
"FN_DataDump_ALL_11032014.[CLIENT NAME] " & _
"FROM " & _
"FN_DataDump_ALL_11032014 " & _
"WHERE (((FN_DataDump_ALL_11032014.[CLIENT NAME]) Not Like  ""*Test*"" ));"

clientQry1 = db.CreateQueryDef("NewIDs", clientQry)
End Sub

Upvotes: 0

Views: 841

Answers (1)

Tim Williams
Tim Williams

Reputation: 166835

It will be much easier if you use a table alias:

dte = InputBox("What date was the Data Dump run?", "Please Input a date (MMDDYYYY)")


clientQry = "SELECT DISTINCT t.[CLIENT ID], t.[CLIENT NAME] " & _
            "FROM FN_DataDump_ALL_" & dte & " as t WHERE " & _ 
            " (((t.[CLIENT NAME]) Not Like  ""*Test*"" ));"

Upvotes: 2

Related Questions