Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 596
  • Last Modified:

SQL VBA string in query syntax

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
UserName935
Asked:
UserName935
  • 4
  • 4
1 Solution
 
PatHartmanCommented:
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
 
UserName935Author Commented:
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
 
PatHartmanCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
UserName935Author Commented:
the errors are either the above error message, or, syntax error in FROM clause.  I can not isolate the issue...
0
 
UserName935Author Commented:
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
 
PatHartmanCommented:
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
 
UserName935Author Commented:
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
 
PatHartmanCommented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now