Passing variables through SQL Statements in VBA

[UBSDev]

I have a loop that runs a SQL statment, and I'd like to run a variable through this statement, but I can't seem to find the correct syntax.

For example,

Dim T As String
T = "Table1"

DoCmd.RunSQL "SELECT " & T & ".* FROM " & T

The variable seems to disappear when I do this, so the statement goes through as SELECT .* FROM ... which of course fails.

I've tried putting brackets and parentheses around the variable but that doesn't make any difference. I need to use a variable as I'm running the statement through a loop and the name of the table changes with each run-through.

Any ideas?

 

[Norie]

That code looks perfectly fine to me.

Is that all the code?

 

[Gates Is Antichrist]

I agree with Norie and would add that - if I am not mistaken - a simple SELECT is not available to .RunSQL (SELECT INTO is okay). Correct me if I'm wrong.

 

[UBSDev]

Actually, that was an example...this is the actual code I'm working with -- the variable 'TName' just doesn't seem to register.

LastDate = DLookup("[Date]", "TblLastDate")

EmailDate = LastDate

SQLCode = "INSERT INTO TblEmail ( RunDate, Account, [Primary Broker], [Billing Symbol], [Security Number], [Share Amount], [Dollar Amount], [Waiver Reason]) " & _
"SELECT (SELECT Max(" & TName & ".Field3) AS RD FROM " & TName & " HAVING ((Left([Field3],1) Between '0' And '1'))) AS RD, " & TName & ".Field4, " & TName & ".Field5, " & TName & ".Field6, " & TName & ".Field9, " & TName & ".Field12, " & TName & ".Field13, " & TName & ".Field14 " & _
"FROM " & TName & " WHERE (((" & TName & ".Field4) Is Not Null And (" & TName & ".Field4) Not Like ('*ACCOUNT*')) AND ((" & TName & ".Field5) Is Not Null And (" & TName & ".Field5) Not Like ('*PRIMARY BROKER*')));"





Do While EmailDate < Now()

M = IIf(Len(Month(EmailDate)) = 2, Month(EmailDate), 0 & Month(EmailDate))
D = IIf(Len(Day(EmailDate)) = 2, Day(EmailDate), 0 & Day(EmailDate))
Y = Right(EmailDate, 2)

EMailFile = M & D & Y
TName = EMailFile
MyDir = Dir(EmailDir & EMailFile & ".txt")

If MyDir <> "" Then

DoCmd.TransferText acImportFixed, "Email Specification", TName, EmailDir & EMailFile & ".txt"
DoCmd.RunSQL SQLCode
End If



EmailDate = EmailDate + 1

Loop

 

[Gates Is Antichrist]

Again, insufficient code is shown to say anything. Starting in the middle of your code like that, you can't see how TName is assigned before its first use. And are you using Dim statements?

I suggest tracing TName in VBE, or sprinkling

Debug.Print "<" &  TName & ">"

The courtesy of snipping and shipping us only relevant code is appreciated, but methinks you just cut out the wrong stuff

 

[UBSDev]

Thanks for the reply....here's the whole thing; I step through the code and TName is definitely showing up with the correct value; it's just not registering in the SQL Statement.

Private Sub Command0_Click()

Dim LastDate As Date
Dim EmailDate As Date
Dim EMailFile As String
Dim M As String
Dim D As String
Dim Y As String
Dim EmailDir As String
Dim TName As String
Dim MyDir
Dim SQLCode As String

EmailDir = "\\Psls16\Compliance_groups\Products Surveillance Group\PRODUCT SURVEILLANCE (NEW)\Mutual Funds\Tom Halpin\CDSC Waiver\Emails\"

LastDate = DLookup("[Date]", "TblLastDate")

EmailDate = LastDate

SQLCode = "INSERT INTO TblEmail ( RunDate, Account, [Primary Broker], [Billing Symbol], [Security Number], [Share Amount], [Dollar Amount], [Waiver Reason]) " & _
"SELECT (SELECT Max(" & (TName) & ".Field3) AS RD FROM " & (TName) & " HAVING ((Left([Field3],1) Between '0' And '1'))) AS RD, " & (TName) & ".Field4, " & (TName) & ".Field5, " & (TName) & ".Field6, " & (TName) & ".Field9, " & (TName) & ".Field12, " & (TName) & ".Field13, " & (TName) & ".Field14 " & _
"FROM " & (TName) & " WHERE (((" & (TName) & ".Field4) Is Not Null And (" & (TName) & ".Field4) Not Like ('*ACCOUNT*')) AND ((" & (TName) & ".Field5) Is Not Null And (" & (TName) & ".Field5) Not Like ('*PRIMARY BROKER*')));"


Do While EmailDate < Now()

M = IIf(Len(Month(EmailDate)) = 2, Month(EmailDate), 0 & Month(EmailDate))
D = IIf(Len(Day(EmailDate)) = 2, Day(EmailDate), 0 & Day(EmailDate))
Y = Right(EmailDate, 2)

EMailFile = M & D & Y
TName = EMailFile
MyDir = Dir(EmailDir & EMailFile & ".txt")

If MyDir <> "" Then

DoCmd.TransferText acImportFixed, "Email Specification", TName, EmailDir & EMailFile & ".txt"
DoCmd.RunSQL SQLCode
End If

EmailDate = EmailDate + 1

Loop
End Sub

 

[Gates Is Antichrist]

Well TName is "" before you hit the Do loop. It hasn't been assigned anything yet. Right?!

 

[Norie]

Your constructing the SQL before you put a value in TName.

 

[UBSDev]

D'OH!!!! I had far too much to drink over the weekend...Thanks for your help!