Solved

Using a string value in a SQL query in VBA

Posted on 2014-04-24
9
1,443 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 36

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 36

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

679 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