Solved

SQL VBA string in query syntax

Posted on 2014-04-21
8
582 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
  • 4
  • 4
8 Comments
 
LVL 35

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 35

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
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.

 

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 35

Accepted Solution

by:
PatHartman earned 500 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 35

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

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