Solved

SQL VBA string in query syntax

Posted on 2014-04-21
8
576 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 34

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 34

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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 34

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 34

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now