command if and where

[Dr. Mohamed Selima]

I need to make a button based on If ,, else condition
the form has two unbund fields Combo0 and combo2
if combo 2 = null then the form addressbook will be opened using a filter
its SQL is
SELECT AddressBook.*
FROM AddressBook
WHERE (((AddressBook.Category)=[Forms]![Form1]![Combo0]));
else if combo2 is not null it will open the same form but with this filter
SELECT AddressBook.*
FROM AddressBook
WHERE (((AddressBook.Department)=[Forms]![Form1]![Combo2]) AND
((AddressBook.Category)=[Forms]![Form1]![Combo0]));
How can I put this as VB code
I appreciate your help

 

[Wayne Morgan]

If IsNull(Forms!Form1!Combo2) Then
strSQL = "SELECT AddressBook.* FROM AddressBook" & _
" WHERE (((AddressBook.Category)=" & _
[Forms]![Form1]![Combo0] & "));"
Else
strSQL = "SELECT AddressBook.* FROM AddressBook" & _
" WHERE (((AddressBook.Department)=" & _
[Forms]![Form1]![Combo2] & ")" & _
" AND ((AddressBook.Category)=" & _
[Forms]![Form1]![Combo0] & "));"
End If

This should work if the values of the combo boxes are numeric. If string, you'll need to
add some apostrophes.

Example:
" AND ((AddressBook.Category)='" & _
[Forms]![Form1]![Combo0] & "'));"

The apostrophes have been added after the = and before the closing )).

You would then use the variable strSQL for whatever you were wanting the SQL statements
for.