SQL in VBA with a string variable??

[option]

Hey guys,

I'm trying to run the module below, but this whole "SQL in VBA" thing is pretty foreign to me. Basically, I have a query that I need to run using a variable (TrNum). Rather than have the user manually change the variable and run the query, I have the TrNum in a table that the user populates via multi-select list box. Whatever the user selects is added to a temporary table (tblCriteria) to be passed through the query, into tblOutput. I keep getting the error that there is a missing operator for one, and for two...can this query be executed using the TrNum string I have put in there?

Option Compare Database
Option Explicit


Public Function SelecTran()
Dim TrNum As String
Dim StrSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ro As DAO.Recordset

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblCriteria")
    Set ro = db.OpenRecordset("tblOutput")
    
    [B]TrNum [/B]= rs!TranCode
    
    Do Until rs.EOF
    
        StrSQL = "INSERT INTO tblCriteria ( AccountNumb, PacketNumb, AppliedDte, TranCode, EffctDate, TransactionName, Opeid, Amt1,   Amt2, RefDate, Amt3FromDate, ToDate, Account2TraceNBR, CodesASTC, ProcCode, BatchNbr )" & _
                 "SELECT tblResults.AccountNumb, tblResults.PacketNumb, tblResults.AppliedDte, tblResults.TranCode,   tblResults.EffctDate, tblResults.TransactionName, tblResults.Opeid, tblResults.Amt1, tblResults.Amt2,   tblResults.RefDate, tblResults.Amt3FromDate, tblResults.ToDate, tblResults.Account2TraceNBR,    tblResults.CodesASTC, tblResults.ProcCode, tblResults.BatchNbr" & _
                 "FROM tblResults" & _
                 "WHERE (((tblResults.TranCode)= [B]TrNum[/B]));"
                 
        DoCmd.RunSQL StrSQL
Loop

End Function

 

[pbaldy]

You're making two common errors. For starters, you have to watch out when concatenating strings together that you include spaces between words. You will end up with this type of thing:

...BatchNbr )SELECT...

Also, you have to concatenate variables in so their value at runtime can be evaluated. Presuming that field is numeric:

"WHERE (((tblResults.TranCode)= " & TrNum & "));"

 

[MSAccessRookie]

Hey guys,

I'm trying to run the module below, but this whole "SQL in VBA" thing is pretty foreign to me. Basically, I have a query that I need to run using a variable (TrNum). Rather than have the user manually change the variable and run the query, I have the TrNum in a table that the user populates via multi-select list box. Whatever the user selects is added to a temporary table (tblCriteria) to be passed through the query, into tblOutput. I keep getting the error that there is a missing operator for one, and for two...can this query be executed using the TrNum string I have put in there?

Option Compare Database
Option Explicit
 
 
Public Function SelecTran()
Dim TrNum As String
Dim StrSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ro As DAO.Recordset
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblCriteria")
    Set ro = db.OpenRecordset("tblOutput")
 
    [B]TrNum [/B]= rs!TranCode
 
    Do Until rs.EOF
 
        StrSQL = "INSERT INTO tblCriteria ( AccountNumb, PacketNumb, AppliedDte, TranCode, EffctDate, TransactionName, Opeid, Amt1,   Amt2, RefDate, Amt3FromDate, ToDate, Account2TraceNBR, CodesASTC, ProcCode, BatchNbr )" & _
                 "SELECT tblResults.AccountNumb, tblResults.PacketNumb, tblResults.AppliedDte, tblResults.TranCode,   tblResults.EffctDate, tblResults.TransactionName, tblResults.Opeid, tblResults.Amt1, tblResults.Amt2,   tblResults.RefDate, tblResults.Amt3FromDate, tblResults.ToDate, tblResults.Account2TraceNBR,    tblResults.CodesASTC, tblResults.ProcCode, tblResults.BatchNbr" & _
                 "FROM tblResults" & _
                 "WHERE (((tblResults.TranCode)= [B]TrNum[/B]));"
 
        DoCmd.RunSQL StrSQL
Loop
 
End Function

You are missing a Space at the end of each line. The SQL String that you are building will be interpreted as follows (Formatted for Display):

[COLOR=black][FONT=Verdana][COLOR=black]INSERT INTO[/COLOR] tblCriteria ( AccountNumb, PacketNumb, AppliedDte, TranCode, EffctDate, [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]TransactionName, Opeid, [/FONT][/COLOR][COLOR=black][FONT=Verdana]Amt1,   Amt2, RefDate, Amt3FromDate, ToDate, Account2TraceNBR, [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]CodesASTC, ProcCode, BatchNbr [B][COLOR=red])[/COLOR][COLOR=red]SELECT[/COLOR][/B] tblResults.AccountNumb, tblResults.PacketNumb, [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]tblResults.AppliedDte, tblResults.TranCode,   tblResults.EffctDate, tblResults.TransactionName, [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]tblResults.Opeid, tblResults.Amt1, tblResults.Amt2,   tblResults.RefDate, tblResults.Amt3FromDate, [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]tblResults.ToDate, tblResults.Account2TraceNBR,    tblResults.CodesASTC, tblResults.ProcCode, [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]tblResults.[B][COLOR=red]BatchNbrFROM [/COLOR][COLOR=red]tblResults[/COLOR][COLOR=red]WHERE[/COLOR][COLOR=red] [/COLOR][/B](((tblResults.TranCode)= [B]TrNum[/B]));[/FONT][/COLOR]

Add the missing spaces and it should work

 

[option]

Thanks for the quick response! I applied both solutions and it works. Now for a little tweaking and I'm set! Thanks a ton!!