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

x
?
Solved

Variables in Select portion of SQL statement within VBA

Posted on 2014-04-21
2
Medium Priority
?
725 Views
Last Modified: 2014-04-21
Good Afternoon Professionals,

 I need to customize the fields that are being sought after in the below 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
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
2 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 40012964
Well, with a normalized database, you wouldn't really have a field called T1.JAN or T1.Feb, you would have T1.MonthNum and T1.MonthValue,  which would allow you to have as many months as you want, all easily queryable.

But yes, you should be able to do that, although I would probably write it as:
strSQL = "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, """");"

Open in new window

0
 

Author Comment

by:UserName935
ID: 40013042
Yes, I agree, we are just beginning.  Prior to seeing the above comment, I was able to get this function with the code 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

'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 T1Feb ,T2." & thismonth & " as T2Feb " & _
"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 & ", """");"



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

MsgBox "Done "

There is still some clean up to do and the field name referenced in the Select portion of the statement will be updated to also use the month variable.
I will try and use the suggested syntax.
without going out of the boundaries to much.  I wonder, would it be possible to create an additional piece of functionality to this by adding a feature in which a user can decide what percentage of variance a person wishes to display?
For example, the above query simply shows a comparative difference between like fields of two tables with the exact same structure.  would it be possible to add a text box on the main form in which a user would put in a number, such as, 25, which would signify 25% so that the query would only show those differing records that have a variance of 25% or more?
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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