Writting SQL including VBA variables

[Beaudry93]

Hello,

I have a valid recordset VBA code with the following string variables (EmpNumber, RegularHours, OTHours, DTHours, RBAmount, qryName, tblName) and I am trying to create a SELECT query via db.CreateQueryDef.

My issue is writing the SQL Code incorporating these string variables as the name of the fields and tables that the query has to go searching for.

Here is the line of code I have written from how I thought it should have been written but I do not have advanced knownledge in the syntax of both languages.

PS. This is all 1 line in my VBA code, I only split it for viewing purposes

Set qd = db.CreateQueryDef( qryName,
 
"SELECT" & tblName.EmpNumber & " AS EmpNumb," & tblName.RegularHours & " AS TotalRegularHours," & tblName.OTHours & " AS TotalOTHours," & tblName.DTHours & " AS TotalDTHours," & tblName.RBAmount & " AS TotalRBAmount, IIf([" & RegularHours & "] Is Null,0,Val([" & RegularHours & "])) + IIf([" & OTHours & "] Is Null,0,Val([" & OTHours & "])) + IIf([" & DTHours & "] Is Null,0,Val([" & DTHours & "])) AS ActualHours 
 
FROM " & tblName & " 
 
WHERE (((" & tblName.EmpNumber & ") Is Not Null) AND ((" & tblName.RegularHours & ") Is Not Null))")

[twinnyfo]

Beaudry93,

You mention in your question that your variables are all strings. If this is the case, your statement builder should look something like this:

"SELECT " & tblName & "." & EmpNumber & " AS EmpNumb, " & tblName & "." & RegularHours ...
 
FROM " & tblName & "  
 
WHERE " & tblName & "." & EmpNumber & " Is Not Null AND ...

However, I am not sure your planning is correct, as the variable "EmpNumber" implies that it is "an employee ID Number" and not the name of a field name.

Please tell us what is actually being stored in your variables first, so that we can be sure we are building your query correctly.

Also, it may be wise to step through building your query more slowly, since you say you don't have a lot of experience. This will make sure you are building correctly from the beginning, rather than building a huge query that "doesn't work" and then have no way to begin troubleshooting.

[Beaudry93]

Twinnyfo, glad I can always count on you.

Your alteration worked. The reason for text is that the query was reading the values from a linked excel table which had different value types in the same comlunm, forcing me to keep the EmpNumber as text.

And I had initially built the query through the query wizard, which is where I took the SQL text from the SQL view.

Thanks again, your help is VERY appreciated

Beaudry93

[twinnyfo]

Beaudry93,

Glad I could help. There are plenty of bigger brains on this forum, but I try to help where I can. Good luck on the rest of your project!