If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Coding a WHERE clause using form options: what's best practice?
Hi All,
This is probably a real newbie thing, but what is the best practice coding for creating the WHERE clause for an SQL statement, in VBA, using options on a form. In particular, I[m curious about when none, any combination, or all of the options may be selected. For the sake of argument, let's say that if none are selected, all records should be returned, so no WHERE is needed. Here's what I currently do. I form the initial SELECT and FROM clauses into a string. At the end of the string, I tack on "WHERE 1=1". Then I go through the options, concatinating phrases like " AND yada yada yada" for each option selected. Once done with the options, I add the ORDER, if used, and close the string. The only real down side I can see is that I always have a WHERE clause being evaluated, even if there are no options. Especially if this is the most common case, having an unnecessary WHERE hurts efficiency. This isn't likely a big deal unless it's a huge installation or something, and that not an issue for me. So, what do the folks "in the know" do? What's the best way to handle this sort of coding? Thanks All, Max |
Thread Tools | |
Display Modes | |
|
|