Changing Query's SQL using VBA

[nxqviet]

Hi all,

I'm trying to create queries using VBA. The user will work through a
form in order to create a custom query. I've been spending a whole day
on this code trying to figure out what is wrong with it, but I
couldn't. Since there is an error in this stSQL, the SQL of the query
can not be replaced. Please help and Thank You very much for your
time. The code is as follow...

--------------------------------------------------------------------------------------------------------------------------------------

Dim Alias As Variant
Dim Product1 As Variant
Dim BeginDate As Variant
Dim EndDate As Variant
Dim stSQL As String

Alias = AliasCb.Value
Product1 = FieldA.value
BeginDate = DateBeginField.Value
EndDate = DateEndField.Value

stSQL = "SELECT TABLE_CUSTOMER.ALIAS, TABLE_PACK.NAME,
Count(TABLE_RECORDS.PACK_ID) AS CountOfPACK_ID" & _
"FROM TABLE_PACK INNER JOIN ((TABLE_CUSTOMER INNER JOIN TABLE_RECORDS
ON TABLE_CUSTOMER.CUSTOMER_ID = TABLE_RECORDS.CUSTOMER_ID)" & _
"INNER JOIN TABLE_PACK_DETAIL ON TABLE_RECORDS.PACK_ID =
TABLE_PACK_DETAIL.PACK_ID) ON TABLE_PACK.PACK_ID =
TABLE_RECORDS.PACK_ID" & _
"WHERE (((TABLE_CUSTOMER.ALIAS)= " & Alias & ") AND
((TABLE_RECORDS.EXPORT_DATE) Between " & BeginDate & " And " & EndDate
& ") AND" & _
"((TABLE_PACK_DETAIL.VERIFICATION_SUBJECT_ID)= " & Product1 & ") AND
((TABLE_RECORDS.PACK_ID) Is Not Null))" & _
"GROUP BY TABLE_CUSTOMER.ALIAS, TABLE_PACK.NAME;"

CurrentDb.QueryDefs("qryTemplate").SQL = stSQL
CurrentDb.QueryDefs.Refresh

--------------------------------------------------------------------------------------------------------------------------------------

THANK YOU!


V_

 

[John W. Vinson]

Hi all,

I'm trying to create queries using VBA. The user will work through a
form in order to create a custom query. I've been spending a whole day
on this code trying to figure out what is wrong with it, but I
couldn't. Since there is an error in this stSQL, the SQL of the query
can not be replaced. Please help and Thank You very much for your
time. The code is as follow...

I strongly suspect it has to do with delimiters. In WHERE clauses, any text
criterion must be delimited with quote marks (either ' or "); any Date/Time
criterion must be delimited with #; number fields get no delimiter.

You also need some spaces. By concatenating your strings you're getting things
like

AS CountOfPackIDFROM TABLE_PACK

with the FROM not set off as a separate word.

Try

stSQL = "SELECT TABLE_CUSTOMER.ALIAS, TABLE_PACK.NAME,
Count(TABLE_RECORDS.PACK_ID) AS CountOfPACK_ID" & _
" FROM TABLE_PACK INNER JOIN ((TABLE_CUSTOMER INNER JOIN TABLE_RECORDS
ON TABLE_CUSTOMER.CUSTOMER_ID = TABLE_RECORDS.CUSTOMER_ID)" & _
" INNER JOIN TABLE_PACK_DETAIL ON TABLE_RECORDS.PACK_ID =
TABLE_PACK_DETAIL.PACK_ID) ON TABLE_PACK.PACK_ID =
TABLE_RECORDS.PACK_ID" & _
" WHERE (((TABLE_CUSTOMER.ALIAS)= '" & Alias & "') AND
((TABLE_RECORDS.EXPORT_DATE) Between #" & Format(BeginDate, "mm/dd/yyyy") & "#
And #" & Format(EndDate, "mm/dd/yyyy")
& "#) AND" & _
" ((TABLE_PACK_DETAIL.VERIFICATION_SUBJECT_ID)= " & Product1 & ") AND
((TABLE_RECORDS.PACK_ID) Is Not Null))" & _
" GROUP BY TABLE_CUSTOMER.ALIAS, TABLE_PACK.NAME;"

This assumes that ALIAS is a text field which will not contain apostrophes
(e.g. no O'NEIL names or the like); and that BeginDate and EndDate are Date
variables.

Watch for word wrap of course.

John W. Vinson [MVP]