If-then statements in query SQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott.McCoid@FedEx.com - 10 Mar 2007 15:25 GMT

Is there a way to add If-Then statements to the Access Query SQL? My
code below is taking way to long to run, and I would like to have
Access not search for the items which are null. For instance if
PROBLEM3, PROBLEM4 and PROBLEM5 are null, I would like the query to
only search for PROBLEM1 and PROBLEM2. Any help you can provide would
be appreciated.

SELECT trial.ID, trial.DISCREPANCY_INFO, trial.DISCREPANCY_INFO,
trial.DISCREPANCY_INFO, trial.DISCREPANCY_INFO, trial.DISCREPANCY_INFO
FROM trial
WHERE (((trial.DISCREPANCY_INFO) Like "*" & [forms]![SearchForm]!
[PROBLEM1] & "*" OR [forms]![SearchForm]![PROBLEM1] Is Null)
And ((trial.DISCREPANCY_INFO) Like "*" & [forms]![SearchForm]!
[PROBLEM2] & "*" OR [forms]![SearchForm]![PROBLEM2] Is Null)
And ((trial.DISCREPANCY_INFO) Like "*" & [forms]![SearchForm]!
[PROBLEM3] & "*" OR [forms]![SearchForm]![PROBLEM3] Is Null)
And ((trial.DISCREPANCY_INFO) Like "*" & [forms]![SearchForm]!
[PROBLEM4] & "*" OR [forms]![SearchForm]![PROBLEM4] Is Null)
And ((trial.DISCREPANCY_INFO) Like "*" & [forms]![SearchForm]!
[PROBLEM5] & "*" OR [forms]![SearchForm]![PROBLEM5] Is Null));

Reply to this Message

Stefan Hoffmann - 10 Mar 2007 16:19 GMT

hi Scott,

> Is there a way to add If-Then statements to the Access Query SQL?

You may use Iif() in your SQL statement.

> My code below is taking way to long to run, and I would like to have
> Access not search for the items which are null.

The search can't be optimized when using "*" & Pattern & "*". It would
be faster when you can omit the first asterisk and use Pattern & "*"
instead.

> SELECT trial.ID, trial.DISCREPANCY_INFO, trial.DISCREPANCY_INFO,
> trial.DISCREPANCY_INFO, trial.DISCREPANCY_INFO, trial.DISCREPANCY_INFO
[quoted text clipped - 9 lines]
> And ((trial.DISCREPANCY_INFO) Like "*" & [forms]![SearchForm]!
> [PROBLEM5] & "*" OR [forms]![SearchForm]![PROBLEM5] Is Null));

You may normalize your tables, as PROBLEM1 to PROBLEM5 indicate a
unnormalized structure. Create a PROBLEM table:

PROBLEM: ID (PK), Trial_ID (FK), ProblemNumber, Problem
with an unique index on Trial_ID and ProblemNumber.

This is the best solution when running into query performance problems.

mfG
--> stefan <--

Reply to this Message

Scott.McCoid@FedEx.com - 10 Mar 2007 16:53 GMT

Thanks Stefan, but way over my head. Can you please give me an
example of the IIF using my code? Bear in mind the Problem1-Problem5
are textboxes on a form used to search a single Discrepancy_Info
field.

> hi Scott,
>
[quoted text clipped - 30 lines]
> mfG
> --> stefan <--

Reply to this Message

Stefan Hoffmann - 10 Mar 2007 17:59 GMT

Scott.McCoid@FedEx.com schrieb:

> Thanks Stefan, but way over my head. Can you please give me an
> example of the IIF using my code?

Rereading your OP, indicates that Iif is not a solution to your problem.

> Bear in mind the Problem1-Problem5
> are textboxes on a form used to search a single Discrepancy_Info
> field.

Ah, i misinterpreted it.

>>> SELECT trial.ID, trial.DISCREPANCY_INFO, trial.DISCREPANCY_INFO,
>>> trial.DISCREPANCY_INFO, trial.DISCREPANCY_INFO, trial.DISCREPANCY_INFO
[quoted text clipped - 9 lines]
>>> And ((trial.DISCREPANCY_INFO) Like "*" & [forms]![SearchForm]!
>>> [PROBLEM5] & "*" OR [forms]![SearchForm]![PROBLEM5] Is Null));

Try the following:

Public Function GetPattern1() As String

GetPatter1 = "*" & Nz(Forms("SearchForm").Form.PROBLEM1.Value, "")
If Len(GetPattern1) > 1 then
GetPattern1 = GetPattern1 + "*"
End If

End Function

with

"... WHERE DISCREPANCY_INFO Like GetPattern1()
AND DISCREPANCY_INFO Like GetPattern2() ... "

But the query is still not optimizeable, cause of the "LIKE '*pattern*'".

mfG
--> stefan <--

Reply to this Message