sql statement in a form

jmeek

Registered User.
Local time
Today, 02:31
Joined
Aug 16, 2005
Messages
38
I have the following code

PHP:
Dim strSQL As String

strSQL = "SELECT * " _
& "FROM tblProducts " _
& "WHERE (((tblProducts.SupplierID)=" & Combo0  & ") " _
& "AND ((tblProducts.CategoryID)=" & Combo3 & "));"

I would like to add a clause which would say

PHP:
OR
WHERE (((tblProducts.SupplierID)=" & Combo0  & ") " _
& "AND " & Combo3 IsNull);"

which is like saying that if you leave the categoryID
blank then you will get all suppliers as shown in
Combo0.

Needless to say my attempt above didn't work.
Any help greatly appreciated
 
SQL Statement

By the way the code shown above is not PHP
but VBA. I seemed to have clicked the wrong
button
 
just a shot...why don't you do an IF/THEN statement and say something like "IF ISNull (me!Combo3) then..." and have the SQL statement you want with regards to combo3 being null...then have and ELSE statement that has the SQL statement you want for Combo3 not being null...
 
You might try something like this:

Code:
    "From tblProducts " & _
     "Where((([tblProducts].[SupplierID]=[Forms]![YourFormName]![Combo0] Or [Forms]![YourFormName]![Combo0] Is Null)=True)) And " & _
     "((([tblProducts].[CategoryID]=[Forms]![YourFormName]![Combo3] Or [Forms]![YourFormName]![Combo3] Is Null)=True));"

Actually, as I recently found out, the performance will be better if you base this on a query rather than trying to use the SQL in code. Then you will not have to wrestle with the SQL syntax either.
 
Last edited:
SQL Statement

Thanks to both cyberpac9 and jrjr

I used the JRJR solution.
JRJR, what was it you recently found out about the
speed ? Also would it be better to run this query
from the local w/station rather than from the
server when using linked tables ?

Thanks
 
I was trying to sort out SQL same as you and was advised to just use a query. Its easier and faster to use a query so I was told.

There are good points and bad to running local or on the server. Placing the FE on the users machine will be the fastest for sure. I run my FE from the server though. It makes deployment and updating easier as there is only one DB to update and you can simply e-mail a link to the users. (if that is your question) The FE contains all of the forms, queries, etc. The BE should only have tables to hold your data.
 
Last edited:
SQL Statement

jrjr

I heard that queries are optimised whereas sql
optimisation is done at the last moment and on the
fly. Although it is very difficult to measure any
speed differences between the 2 techniques, they
both seem the same.

jmeek
 
I would imagine it has to do with the magnitude of the work being performed. I do not ask a lot so I see no speed differences. Crunching thousands of records may show the differences though. In any event, I plan to use queries whenever possible till I hear different. Glad things worked out for you! :)
 
Sort of half on topic / half off topic........ JRJR stated that he preferred using queries instead of code, because it was easier.

I agree.... however...... using variables is more difficult IMHO in queries... so I usually build my SQL statements in the query window.. then copy / paste to code using the SQL View in the query window.

I prefer keeping as much as possible in the code... because with multiple users / administrators, you never know when some *#@((!#&.. err, I mean colleague, will come in and start tinkering with a query, not realizing you've based 20 different reports and forms off of it, and he/she just ruined it.
 

Users who are viewing this thread

Back
Top Bottom