Solved

Using a string value in a SQL query in VBA

Posted on 2014-04-24
9
1,438 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

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.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

777 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