String concatenation in Access VBA

Question

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

blogs.msdn.com/b/developingfordynamicsgp/archive/2011/12/16/… — Tim Williams
– Tim Williams, Commented Nov 11, 2014 at 21:06
Sorry I forgot to mention in the question. The dte variable will replace the 11032014 in the table name. Is there any way to do that? — user4095610
– user4095610, Commented Nov 11, 2014 at 21:12

Tim Williams · Accepted Answer · 2014-11-11 21:23:48Z

2

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*"" ));"

answered Nov 11, 2014 at 21:23

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

String concatenation in Access VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related