Solved

Using a string value in a SQL query in VBA

Posted on 2014-04-24
9
1,432 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
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 40020662
try

"Where " &  Morfieldname & " not like " & "'" & Gecsfieldname &  "';"
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 34

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 34

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now