SQL in VBA

W

Wendy Parry

Hi,
I know I should be creating a stored procedure for this, but I simply
haven't got the time to do the learning...I'll do that when I've
finished this project. But a question is how do I use the following in a
VBA strSQL string:

SELECT [CountOfPU_SEQ]-[Started] AS [Not Started], test2.SET_CODE,
test2.SET_SUBJECT, test2.Started, test2.CountOfPU_SEQ, test2.SET_SEQ,
test2.SET_DEF_TEACH, test2.SET_YEAR
FROM test2
GROUP BY [CountOfPU_SEQ]-[Started], test2.SET_CODE, test2.SET_SUBJECT,
test2.Started, test2.CountOfPU_SEQ, test2.SET_SEQ, test2.SET_DEF_TEACH,
test2.SET_YEAR
HAVING (((test2.SET_SUBJECT)="it") AND ((test2.SET_DEF_TEACH)="wmp"));


The parameters in the HAVING come from combobox values on the current form.


The length of SQL string seems to create one problem I've tried breaking
it into sections and concatenating back but it returns a FALSE and then
I get another problem when trying to pick up the ComboBox values:

strSQL = strSQL & "(((test2.SET_SUBJECT)" = "'" & strSubject & "') AND
((test.2.SET_DEF_TEACH)" = "'" & strTeach & "'))"


Be most grateful for help. You guys are stars!

Wendy
 
J

John Spencer

Wendy,
You talk about parameters and comboboxes on a form, but I see no references in
your posting to the comboboxes. Since you talk about a VBA string, I assume you
want something like the following.

StrSQL = "SELECT [CountOfPU_SEQ]-[Started] AS [Not Started]" & _
", test2.SET_CODE, test2.SET_SUBJECT, test2.Started" & _
", test2.CountOfPU_SEQ, test2.SET_SEQ" & _
", test2.SET_DEF_TEACH, test2.SET_YEAR" & _
" FROM test2 "
" GROUP BY [CountOfPU_SEQ]-[Started]" & _
", test2.SET_CODE, test2.SET_SUBJECT, test2.Started" & _
", test2.CountOfPU_SEQ, test2.SET_SEQ" & _
", test2.SET_DEF_TEACH, test2.SET_YEAR" & _
" HAVING test2.SET_SUBJECT=" & Chr(34) & Forms!YourFormname!ComboboxName &
Chr(34) & _
" AND test2.SET_DEF_TEACH = " & Chr(34) &
Forms!YourFormname!AnotherComboboxName & Chr(34)

Another problem with the above is why group? If you are grouping on all the
values you can use the DISTINCT clause and get the same results more efficiently.

StrSQL = "SELECT DISTINCT [CountOfPU_SEQ]-[Started] AS [Not Started]" & _
", test2.SET_CODE, test2.SET_SUBJECT, test2.Started" & _
", test2.CountOfPU_SEQ, test2.SET_SEQ" & _
", test2.SET_DEF_TEACH, test2.SET_YEAR" & _
" FROM test2 "
" WHERE test2.SET_SUBJECT=" & Chr(34) & Forms!YourFormname!ComboboxName &
Chr(34) & _
" AND test2.SET_DEF_TEACH = " & Chr(34) &
Forms!YourFormname!AnotherComboboxName & Chr(34)

I've used the Chr(34) to add the quote mark delimiters to the query as they make
it easier for me to see that I am dealing with a string. You can use the
apostrophe, but you need to modify the string you are building. You have too
many quote marks. If I've got this right, you want the following.

strSQL & "(((test2.SET_SUBJECT)= '" & strSubject & "') AND
((test2.SET_DEF_TEACH) = '" & strTeach & "'))"


Wendy said:
Hi,
I know I should be creating a stored procedure for this, but I simply
haven't got the time to do the learning...I'll do that when I've
finished this project. But a question is how do I use the following in a
VBA strSQL string:

SELECT [CountOfPU_SEQ]-[Started] AS [Not Started], test2.SET_CODE,
test2.SET_SUBJECT, test2.Started, test2.CountOfPU_SEQ, test2.SET_SEQ,
test2.SET_DEF_TEACH, test2.SET_YEAR
FROM test2
GROUP BY [CountOfPU_SEQ]-[Started], test2.SET_CODE, test2.SET_SUBJECT,
test2.Started, test2.CountOfPU_SEQ, test2.SET_SEQ, test2.SET_DEF_TEACH,
test2.SET_YEAR
HAVING (((test2.SET_SUBJECT)="it") AND ((test2.SET_DEF_TEACH)="wmp"));

The parameters in the HAVING come from combobox values on the current form.

The length of SQL string seems to create one problem I've tried breaking
it into sections and concatenating back but it returns a FALSE and then
I get another problem when trying to pick up the ComboBox values:

strSQL = strSQL & "(((test2.SET_SUBJECT)" = "'" & strSubject & "') AND
((test.2.SET_DEF_TEACH)" = "'" & strTeach & "'))"

Be most grateful for help. You guys are stars!

Wendy
Click to expand...
 
Top