If-then statements in query SQL

[Scott.McCoid]

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));

 

[Stefan Hoffmann]

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
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));

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 <--

 

[Scott.McCoid]

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,

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
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));

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 <--

 

[Stefan Hoffmann]

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
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));

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 <--