By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,946 Members | 1,610 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,946 IT Pros & Developers. It's quick & easy.

Writting SQL including VBA variables

P: 14
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

Expand|Select|Wrap|Line Numbers
  1. Set qd = db.CreateQueryDef( qryName,
  2.  
  3. "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 
  4.  
  5. FROM " & tblName & " 
  6.  
  7. WHERE (((" & tblName.EmpNumber & ") Is Not Null) AND ((" & tblName.RegularHours & ") Is Not Null))")
Jun 23 '14 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. "SELECT " & tblName & "." & EmpNumber & " AS EmpNumb, " & tblName & "." & RegularHours ...
  2.  
  3. FROM " & tblName & "  
  4.  
  5. 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.

Share this Question
Share on Google+
3 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,487
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:

Expand|Select|Wrap|Line Numbers
  1. "SELECT " & tblName & "." & EmpNumber & " AS EmpNumb, " & tblName & "." & RegularHours ...
  2.  
  3. FROM " & tblName & "  
  4.  
  5. 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.
Jun 23 '14 #2

P: 14
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
Jun 24 '14 #3

twinnyfo
Expert Mod 2.5K+
P: 3,487
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!
Jun 24 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.