Solved

Using a string value in a SQL query in VBA

Posted on 2014-04-24
9
1,440 Views
Last Modified: 2014-04-28
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
0
Comment
Question by:UserName935
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 40020539
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
 
LVL 33

Expert Comment

by:Norie
ID: 40020546
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
 

Author Comment

by:UserName935
ID: 40020627
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 40020662
try

"Where " &  Morfieldname & " not like " & "'" & Gecsfieldname &  "';"
0
 

Author Comment

by:UserName935
ID: 40020692
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
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40020720
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
 

Author Closing Comment

by:UserName935
ID: 40027825
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
 
LVL 35

Expert Comment

by:PatHartman
ID: 40027915
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
 

Author Comment

by:UserName935
ID: 40027950
I hear you, however this was a 'one off' for comparative purposes.  It is not going to be developed. Thanks again !
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question