Using a string value in a SQL query in VBA

Good Day,

I have a query that is setting the recordset of a subform from a main form in access 2010.  There are issues with using a variable to change the WHERE clause at runtime.

Does anyone know how to get a string variable to function within a SQL string in VBA?

Code below:
' dim array variables
Dim thismonth As String
Dim Gecsfieldname As String
Dim Morfieldname As String
Dim BinList As String

 
'Set Variable values
thismonth = Me.Monthlist
BinList = Me.BinList
'Gecsfieldname = "[Fused_MOR].[GECS_MOR_ChartDBSource_Template" & thismonth & "]"
'Morfieldname = "[Fused_MOR].[MOR_ChartDBSource_Template" & thismonth & "]"
             
Gecsfieldname = "[Fused_MOR].[GECS_MOR_ChartDBSource_TemplateMar]"
Morfieldname = "[Fused_MOR].[GECS_MOR_ChartDBSource_TemplateMar]"

MsgBox thismonth
MsgBox Gecsfieldname
MsgBox Morfieldname
MsgBox BinList

Forms![CompareMonthsForm]![Fused_MORsubform].Form.RecordSource = "SELECT Fused_MOR.GECS_MOR_ChartDBSource_TemplateMOR_GROUP, Fused_MOR.MOR_ChartDBSource_TemplateMOR_GROUP," & _
"Fused_MOR.GECS_MOR_ChartDBSource_TemplateCHARTDB_BIN , Fused_MOR.MOR_ChartDBSource_TemplateCHARTDB_BIN, Fused_MOR.GECS_MOR_ChartDBSource_TemplateORG_ABBR, " & _
"Fused_MOR.MOR_ChartDBSource_TemplateORG_ABBR, Fused_MOR.GECS_MOR_ChartDBSource_TemplateYEAR, Fused_MOR.MOR_ChartDBSource_TemplateYEAR, " & _
"Fused_MOR.GECS_MOR_ChartDBSource_TemplateJan, Fused_MOR.MOR_ChartDBSource_TemplateJan, Fused_MOR.GECS_MOR_ChartDBSource_TemplateFeb, " & _
"Fused_MOR.MOR_ChartDBSource_TemplateFeb, Fused_MOR.GECS_MOR_ChartDBSource_TemplateMar, Fused_MOR.MOR_ChartDBSource_TemplateMar, " & _
"Fused_MOR.GECS_MOR_ChartDBSource_TemplateApr, Fused_MOR.MOR_ChartDBSource_TemplateApr, Fused_MOR.GECS_MOR_ChartDBSource_TemplateMay, " & _
"Fused_MOR.MOR_ChartDBSource_TemplateMay, Fused_MOR.GECS_MOR_ChartDBSource_TemplateJun, Fused_MOR.MOR_ChartDBSource_TemplateJun, " & _
"Fused_MOR.GECS_MOR_ChartDBSource_TemplateJul, Fused_MOR.MOR_ChartDBSource_TemplateJul, Fused_MOR.GECS_MOR_ChartDBSource_TemplateAug, " & _
"Fused_MOR.MOR_ChartDBSource_TemplateAug, Fused_MOR.GECS_MOR_ChartDBSource_TemplateSep, Fused_MOR.MOR_ChartDBSource_TemplateSep, " & _
"Fused_MOR.GECS_MOR_ChartDBSource_TemplateOct, Fused_MOR.MOR_ChartDBSource_TemplateOct, Fused_MOR.GECS_MOR_ChartDBSource_TemplateNov, " & _
"Fused_MOR.MOR_ChartDBSource_TemplateNov, Fused_MOR.GECS_MOR_ChartDBSource_TemplateDec, Fused_MOR.MOR_ChartDBSource_TemplateDec " & _
"FROM Fused_MOR " & _
"WHERE  (((Fused_MOR.MOR_ChartDBSource_TemplateMar) Not Like [Fused_MOR].[GECS_MOR_ChartDBSource_TemplateMar]));"


'Refresh the subform post query
Forms![CompareMonthsForm]![Fused_MORsubform].Form.Requery
UserName935Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PatHartmanConnect With a Mentor Commented:
It is NOT possible to have variable Table OR column names in a querydef.  See this thread for a more complete explanation of why.

http://www.experts-exchange.com/Database/MS_Access/Q_28418634.html#a40018629

To accomplish your goal, you will need to build the entire query in VBA and then replace the RowSource of the combo with the code-built SQL string.

PS - your table is not normalized and that is what is causing your problem.  If the table were properly normalized, each month would be a separate row and that would allow you to use a WHERE clause to select the specific row you wanted.  There is simply no way to change the column to select on the fly.
0
 
Anthony BerenguelCommented:
if you want to use string variables in SQL statments you need to wrap the variable name with apostrophes.  Try this...
Forms![CompareMonthsForm]![Fused_MORsubform].Form.RecordSource = "SELECT Fused_MOR.GECS_MOR_ChartDBSource_TemplateMOR_GROUP, Fused_MOR.MOR_ChartDBSource_TemplateMOR_GROUP," & _
"Fused_MOR.GECS_MOR_ChartDBSource_TemplateCHARTDB_BIN , Fused_MOR.MOR_ChartDBSource_TemplateCHARTDB_BIN, Fused_MOR.GECS_MOR_ChartDBSource_TemplateORG_ABBR, " & _
"Fused_MOR.MOR_ChartDBSource_TemplateORG_ABBR, Fused_MOR.GECS_MOR_ChartDBSource_TemplateYEAR, Fused_MOR.MOR_ChartDBSource_TemplateYEAR, " & _
"Fused_MOR.GECS_MOR_ChartDBSource_TemplateJan, Fused_MOR.MOR_ChartDBSource_TemplateJan, Fused_MOR.GECS_MOR_ChartDBSource_TemplateFeb, " & _
"Fused_MOR.MOR_ChartDBSource_TemplateFeb, Fused_MOR.GECS_MOR_ChartDBSource_TemplateMar, Fused_MOR.MOR_ChartDBSource_TemplateMar, " & _
"Fused_MOR.GECS_MOR_ChartDBSource_TemplateApr, Fused_MOR.MOR_ChartDBSource_TemplateApr, Fused_MOR.GECS_MOR_ChartDBSource_TemplateMay, " & _
"Fused_MOR.MOR_ChartDBSource_TemplateMay, Fused_MOR.GECS_MOR_ChartDBSource_TemplateJun, Fused_MOR.MOR_ChartDBSource_TemplateJun, " & _
"Fused_MOR.GECS_MOR_ChartDBSource_TemplateJul, Fused_MOR.MOR_ChartDBSource_TemplateJul, Fused_MOR.GECS_MOR_ChartDBSource_TemplateAug, " & _
"Fused_MOR.MOR_ChartDBSource_TemplateAug, Fused_MOR.GECS_MOR_ChartDBSource_TemplateSep, Fused_MOR.MOR_ChartDBSource_TemplateSep, " & _
"Fused_MOR.GECS_MOR_ChartDBSource_TemplateOct, Fused_MOR.MOR_ChartDBSource_TemplateOct, Fused_MOR.GECS_MOR_ChartDBSource_TemplateNov, " & _
"Fused_MOR.MOR_ChartDBSource_TemplateNov, Fused_MOR.GECS_MOR_ChartDBSource_TemplateDec, Fused_MOR.MOR_ChartDBSource_TemplateDec " & _
"FROM Fused_MOR " & _
"WHERE  (((Fused_MOR.MOR_ChartDBSource_TemplateMar) Not Like "'" & [Fused_MOR].[GECS_MOR_ChartDBSource_TemplateMar] & "'" ));"

Open in new window

0
 
NorieVBA ExpertCommented:
What string variable do you want in the SQL?

In general this is how you would add a string variable to your criteria.
"WHERE  (((Fused_MOR.MOR_ChartDBSource_TemplateMar) Not Like '"& YourStringVariable "'"

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
UserName935Author Commented:
Yes, I agree, however, I can not get it to function.
Basically, it is set up like this.
the Main form has a combo box that is defined in the code
thismonth = me.monthlist

Then that value would be placed into the query by concatenating the two WHERE variables like this:
 Gecsfieldname = "[Fused_MOR].[GECS_MOR_ChartDBSource_Template" & thismonth & "]"
so, depending upon the month value from the main form, the string could be something this
[Fused_MOR].[GECS_MOR_ChartDBSource_TemplateJan
or
[Fused_MOR].[GECS_MOR_ChartDBSource_TemplateFeb
or
[Fused_MOR].[GECS_MOR_ChartDBSource_TemplateMar

I have tried
Where "" '" & Morfieldname & "'""  not like "" '" & Gecsfieldname & "'"" ;"
Where " '  & Morfieldname & '" not like  " '  & Gecsfieldname & '" ;"
Where Chr$(34) &  Morfieldname & Chr$(34) not like Chr$(34) & Gecsfieldname & Chr$(34)

If I 'hard code' it, it functions, if the variable is used, it fails.

Thank you for the input !!
0
 
Anthony BerenguelCommented:
try

"Where " &  Morfieldname & " not like " & "'" & Gecsfieldname &  "';"
0
 
UserName935Author Commented:
Umm,  How would one set up the variables?

Like this
Gecsfieldname = "[Fused_MOR].[GECS_MOR_ChartDBSource_Template" & thismonth & "]"
Morfieldname = "[Fused_MOR].[MOR_ChartDBSource_Template" & thismonth & "]"

or maybe like this

Gecsfieldname = "Fused_MOR.GECS_MOR_ChartDBSource_Template" & thismonth & "
Morfieldname = "Fused_MOR.MOR_ChartDBSource_Template" & thismonth & "

How would one set these values up?
0
 
UserName935Author Commented:
This information was very useful, and, ultimately lead me to the solution.  Since the number of queries is limited to the months in a year (12), I simpley hard coded the different circumstances since each query only had a variance of 6 characters.  these were quickly created using the replace function.
Thanks for the education.
0
 
PatHartmanCommented:
You're welcome.

Yes, there are only 12 months in a year but normalizing the schema is still better and ultimately will save you work.
0
 
UserName935Author Commented:
I hear you, however this was a 'one off' for comparative purposes.  It is not going to be developed. Thanks again !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.