I have a simple query to search records using three fields. Users can do this via a form that has a text box for each of those three fields.
Condition 1: if textbox1 entry is an exact match to field A
Condition 2: OR if textbox2 entry is like a value in field B
Condition 3: OR if textbox 3 entry is like a value in field C
User can search by any of those criteria.
I’m running into two scenarios:
- it will either match ONLY by condition 1 and even if it doesn’t have a match, it won’t produce a result through conditions 2 and 3.
- it will produce results in either conditions 2 and 3, correctly using the like statements and when I try condition 1, even though it should be a clear cut result, it pulls every record
I’ve altered the SQL to make sure the conditions are running in the proper order, but I literally don’t see anything wrong with my code. I’ve tried separating them, i.e., isolated condition 1 and it works. Then isolating conditions 2 and 3, it works.
The code is as follows:
SELECT Index.[Rec No], Index.[Type of Plan], Index.[Index Code], Index.Project, Index.Year, Index.Consultant, Index.[Needs Reassigned?], Index.[Label Needed], Index.[Scan Status], Index.Notes
FROM [Index]
WHERE
(Index.[Rec No])=[Forms]![Edit Record]![Rec No])
)
OR (
(Index.[Index Code]) Like ("*" & [Forms]![Edit Record]![Index Code] & "*")
)
AND
(Index.Project) Like ("*" & [Forms]![Edit Record]![Project] & "*")
)
ORDER BY Index.[Rec No], Index.[Index Code];
Do you see something wrong that would make the first condition not work??
UPDATE: workaround achieved by having two separate queries. One for the exact match and one for the similar matches. On the user form, the command button will run the exact match query if anything is entered, else it will run the similar match query. Since I couldn’t actually figure out what I was doing wrong
StormwaterGuru is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
6
The first thing we want to do is use white space to expand the WHERE clause in a readable way. No one could decipher that mess as written:
WHERE
(
( (Index.[Rec No])=[Forms]![Edit Record]![Rec No] )
)
OR
(
( (Index.[Index Code]) Like ("*" & [Forms]![Edit Record]![Index Code] & "*") )
AND ( (Index.Project) Like ("*" & [Forms]![Edit Record]![Project] & "*") )
)
Then we can clean out some of the excessive parentheses:
WHERE
(
Index.[Rec No] = [Forms]![Edit Record]![Rec No]
)
OR
(
Index.[Index Code] Like ("*" & [Forms]![Edit Record]![Index Code] & "*")
AND Index.Project Like ("*" & [Forms]![Edit Record]![Project] & "*")
)
And now the logic should be MUCH easier to follow… possibly such that the error is clear.
4
As to your specification (which doesn’t match your code) it should be:
WHERE
Index.[Rec No] = [Forms]![Edit Record]![Rec No] OR
Index.[Index Code] Like "*" & [Forms]![Edit Record]![Index Code] & "*" OR
Index.Project Like "*" & [Forms]![Edit Record]![Project] & "*"
2
Try this.
- All conditions work together
where condition1 and condition2 and condition3
- Condition is always true when textbox is empty.
Example (Updated)
SELECT Index.[Rec No], Index.[Type of Plan], Index.[Index Code], Index.Project, Index.Year, Index.Consultant, Index.[Needs Reassigned?], Index.[Label Needed], Index.[Scan Status], Index.Notes
FROM [Index]
WHERE (Index.[Rec No]=[Forms]![Edit Record]![Rec No]
or [Forms]![Edit Record]![Rec No] Is Null )
and (Index.[Index Code] Like ("*" & [Forms]![Edit Record]![Index Code] & "*")
or ([Forms]![Edit Record]![Index Code] Is Null )
AND (Index.Project Like ("*" & [Forms]![Edit Record]![Project] & "*")
or ([Forms]![Edit Record]![Project] Is Null) )
ORDER BY Index.[Rec No], Index.[Index Code];
There len([Forms]![Edit Record]![Project])=0
[Forms]![Edit Record]![Project] Is Null – check if condition is empty (not specified).
This check may be data type specific.
3