Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL VBA string in query syntax

Posted on 2014-04-21
8
Medium Priority
?
595 Views
Last Modified: 2014-04-21
Good Day professionals,

I am running into an error with syntax using a string in a vba sql query.  The error consistently reads, syntax error in from clause.  I am suspect that the issue is the way I am setting up the use of the string in the sql statement:
Below:
Dim tableone As String
Dim tabletwo As Variant
Dim thismonth As Variant
 
tableone = Me.ListT1.Value
tabletwo = Me.ListT2.Value
thismonth = Me.ListMonths

Forms![CompareMonthsForm]![CompareMonthsSelQForm].Form.RecordSource = "SELECT T1.MOR_GROUP " & _
"      , T1.CHARTDB_BIN as T1CHARTDB_BIN " & _
"      , T2.CHARTDB_BIN as T2CHARTDB_BIN " & _
"           , T1.Feb as T1Feb " & _
"           , T2.Feb as T2Feb " & _
"FROM " & _
"  & tableone & as T1 " & _
"Left JOIN MOR_ChartDBSource_Template_xxx04142014rtt  as T2 " & _
"ON T1.MOR_GROUP = T2.MOR_GROUP " & _
"WHERE NZ(T1.CHARTDB_BIN, """") = NZ(T2.CHARTDB_BIN, """") " & _
"AND NZ(T1.Feb, """") <> NZ(T2.Feb, """");"

I have tried
1.  " '" & tableone & '" as T1"  &_
2. " """ & tableone & """ as T1 " &_

And just about every other variation.  Can you help?

Thank you.
0
Comment
Question by:UserName935
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 39

Expert Comment

by:PatHartman
ID: 40012614
This might fix it.  But, the best way to debug a string like this is to stop the code after it is built and then print it into the debug window.  If you don't see the error, copy the string from the debug window and paste it into the SQL view of the QBE where you may get better error messages.

"FROM " & _
tableone & " as T1 " & _
"Left JOIN MOR_ChartDBSource_Template_xxx04142014rtt  as T2 " & _
"ON T1.MOR_GROUP = T2.MOR_GROUP " & _
" WHERE NZ(T1.CHARTDB_BIN, """") = NZ(T2.CHARTDB_BIN, """") " & _
" AND NZ(T1.Feb, """") <> NZ(T2.Feb, """");" 

Open in new window

0
 

Author Comment

by:UserName935
ID: 40012633
Umm, interesting approach, I never saw that before.  At any rate, with that syntax the error

of:  Messagebox "Enter Parameter Value" T1.MOR_GROUP

Is there another way it could be written?  Thanks or the suggestion, I will continue to "play" with it....
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 40012683
When Access asks for Parameter Values and that is not what you intended, you have a typo.

Did you stop the code and paste the string to the debug window?  Frequently, that is all you need to do and the error jumps right out at you.
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:UserName935
ID: 40012721
the errors are either the above error message, or, syntax error in FROM clause.  I can not isolate the issue...
0
 

Author Comment

by:UserName935
ID: 40012727
I think I figured it out.  I changed the select statement and believe, perhaps, the common were not typed the way "IT" wanted.
I took out the carriage returns on the Select portion of the query.
 

Functional statement below:
Forms![CompareMonthsForm]![CompareMonthsSelQForm].Form.RecordSource = "SELECT T1.MOR_GROUP ,T1.CHARTDB_BIN as T1CHARTDB_BIN, T2.CHARTDB_BIN as T2CHARTDB_BIN, T1.Feb as T1Feb ,T2.Feb as T2Feb " & _
"FROM " & tableone & " as T1 " & _
"Left JOIN MOR_ChartDBSource_Template_xxx04142014rtt  as T2 " & _
"ON T1.MOR_GROUP = T2.MOR_GROUP " & _
"WHERE NZ(T1.CHARTDB_BIN, """") = NZ(T2.CHARTDB_BIN, """") " & _
" AND NZ(T1.Feb, """") <> NZ(T2.Feb, """");"

Pat, feel free to take them points, your error investigation suggestion seemed to have helped to clear this up.  

Thank you !
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40012746
I don't get to "take" points.  You must "give" them.

Did you ever print out the statement after it was built?  I find it easier to create a variable to hold the string and then print that.

strSQL = "......yourstring...."
Me.RecordSource = strSQL

That isolates the two steps and gives you better debugging options.
0
 

Author Comment

by:UserName935
ID: 40012778
the Semantics on this site crack me up, only here is a person corrected for giving praise.  Unreal...
At any rate, I will take the above advise under advisement and perhaps change things around.
The next step in the equation is to customize the fields that are being sought after in the select statement.  I am not to sure of the syntax for that.  The code is below:
' dim array variables
Dim tableone As String
Dim tabletwo As String
Dim thismonth As Variant
 
'Set Variable values
tableone = Me.ListT1.Value
tabletwo = Me.ListT2.Value
thismonth = Me.ListMonths

MsgBox thismonth

'Run query based on changing variables from parent form to be displayed in subform.
Forms![CompareMonthsForm]![CompareMonthsSelQForm].Form.RecordSource = "SELECT T1.MOR_GROUP ,T1.CHARTDB_BIN as T1CHARTDB_BIN, T2.CHARTDB_BIN as T2CHARTDB_BIN, T1." & thismonth & " as T1 & " & thismonth & ", T2." & thismonth & " as T2" & thismonth & " " & _
"FROM " & tableone & " as T1 " & _
"Left JOIN " & tabletwo & "  as T2 " & _
"ON T1.MOR_GROUP = T2.MOR_GROUP " & _
"WHERE NZ(T1.CHARTDB_BIN, """") = NZ(T2.CHARTDB_BIN, """") " & _
" AND NZ(T1." & thismonth & ", """") <> NZ(T2." & thismonth & ", """");"
'" AND NZ(T1.Feb, """") <> NZ(T2.Feb, """");"

'Refresh the subform post query
Forms![CompareMonthsForm]![CompareMonthsSelQForm].Form.Requery

Question:  Is it possible to concatenate a field name in a select statement in vba?
for example,

Select T1.Jan as T1Jan, T2.Jan as T2Jan

To something like this?

Select T1." & thismonth & " as T1 & " & thismonth & ", T2." & thismonth & " as T1 & " & thismonth & "
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 40012966
Since this is a new question, it would be appropriate to start a new thread.

Question:  Is it possible to concatenate a field name in a select statement in vba?
for example,
When you are building SQL in code, you can do anything you want to build the string.  When you build a querydef using the QBE, you cannot change the structure on the fly and what you are asking would do that.  Querydefs have fixed clauses.  The only thing that is a variable is the value you supply as a parameter.

Step back and think about the bigger picture.  Why are you doing this?  Is the selection of fields really necessary?  What are you going to do with the string once it is built?  You can't use a query with a variable select clause as the recordsource for a form.  The controls on the form are bound to a field from the recordsource and so that has to remain constant.

Frequently just exporting the selected data to Excel will satisfy the user requirements.  Once in Excel, the user can delete any extraneous columns and add sorting and grouping at will.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

670 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