Using a With Statement in SQL string for an Access Query 1

[kjv1611]

I know that in VBA, you can use the With statement to reduce the amount of code involved when you are using multiple parts of a whole. Is this possible with SQL strings?
For example, could you do something like this:

Dim myquery As QueryDef
Dim strQueryName as Sring
strQueryName = cmbCombo1 & "_" & cmbCombo2

With tblMyTable
  Set myquery = CurrentDb.CreateQueryDef([strQueryName], _
  "SELECT .Year, .Month, .Name, .Status, .Manager " & _
  "FROM tblMyTable " &_
  "WHERE (((.[Year]) = [Forms]![frmMyForm]![cmbYear]));")
End With

I may have an ( or " in the wrong place, or too many or too few, but it's just for an example. I was just wanting to know if anyone knew whether this is possible or not.

So, is it possibe?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[lespaul]

that's not a SQL string.

your code example is VB and you are using a WITH statement in VB (although you haven't declared tblMyTable as anything).

So, what's your question?


Leslie

 

[kjv1611]

Well, I was basically wanting to use the SQL statement (or at least I thought that was SQL code based on what a SQL view of an Access Query gives - whatever it is called) in conjunctino with the VB With Statement. I basically wanted to know if it were possible to do something like that... I guess my format should have been more like this:

With Tables!tblMyTable
  Set myquery = CurrentDb.CreateQueryDef([strQueryName], _
  "SELECT [B].[/B]Year, .Month, .Name, .Status, .Manager " & _
  "FROM tblMyTable " &_
  "WHERE (((.[Year]) = [Forms]![frmMyForm]![cmbYear]));")
End With

Anyway, I hope my idea is a little more clear this way.. is it possibe? The .Year, .Month, etc would be values in the table, tblMyTable; and tblMyTable, would be a table in an Access 2002 Database.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PHV]

Try something like this (aliasing technique):
"SELECT A.Year, A.Month, A.Name, A.Status, A.Manager " & _
"FROM tblMyTable A " &_
"WHERE A.Year=[Forms]![frmMyForm]![cmbYear];"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kjv1611]

Thanks! That works great! For example, I can now just type A. instead of tblStatusAudit


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Well, actually, I left off the . from tblStatusAudit, so I can use A. insetad of tblStatusAudit.

Anyway, thanks a bunch - learned yet one more tool or "trick"

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV