Solved

SQL VBA string in query syntax

Posted on 2014-04-21
8
580 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
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