Bob Collison
asked on
Access 2010 Query Syntax
Hi Experts,
I have the following Criteria in a query. The 'Like' part doesn't select any records when it should select all records. The rest of the query works correctly.
IIf([Forms]![M-50-000 - Honours - Awards Module Menu]![M50000_ORG_LONG_NAM E_FILTER]= "Default Organization",Like "*",[Forms]![M-50-000 - Honours - Awards Module Menu]![M50000_ORG_LONG_NAM E_FILTER])
If I replace the 'Like "*"' with "Default Organization" it works correctly so I assume that my 'Like' syntax is incorrect.
What should the syntax be?
Thanks,
Bob C.
I have the following Criteria in a query. The 'Like' part doesn't select any records when it should select all records. The rest of the query works correctly.
IIf([Forms]![M-50-000 - Honours - Awards Module Menu]![M50000_ORG_LONG_NAM
If I replace the 'Like "*"' with "Default Organization" it works correctly so I assume that my 'Like' syntax is incorrect.
What should the syntax be?
Thanks,
Bob C.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I see that you have selected an answer but I'd like to offer another option. Your criteria should not be using LIKE at all. LIKE is used in conjunction with wild cards when partial strings are entered. You appear to be using LIKE to handle the null argument case. Try something like this instead:
WHERE ([M50000_ORG_LONG_NAME_FIL TER] = [Forms]![M-50-000 - Honours - Awards Module Menu]![M50000_ORG_LONG_NAM E_FILTER] OR [Forms]![M-50-000 - Honours - Awards Module Menu]![M50000_ORG_LONG_NAM E_FILTER] = "Default Organization") AND (any other criteria)
Notice that the first expression must be enclosed in parentheses if you need to combine it with other conditions to ensure that the "OR" operator is evaluated as you intend it to be.
The reason for not using LIKE capriciously is because it is inefficient and most database engines will perform a full table scan rather than use an index to satisfy the request. So, only use LIKE when you actually need to search for a partial STRING. Examples would be:
WHERE FirstName LIKE "Jon*"
WHERE LastName LIKE "*ash*"
Where Addr1 LIKE "*Washington*"
If you have a thousand records in the table, it doesn't matter much what you do. If you have a million, every little thing matters. It isn't necessary to kill yourself to make everything as efficient as possible, just gain an understanding of efficient methods and apply them as general practice unless you need to deviate.
WHERE ([M50000_ORG_LONG_NAME_FIL
Notice that the first expression must be enclosed in parentheses if you need to combine it with other conditions to ensure that the "OR" operator is evaluated as you intend it to be.
The reason for not using LIKE capriciously is because it is inefficient and most database engines will perform a full table scan rather than use an index to satisfy the request. So, only use LIKE when you actually need to search for a partial STRING. Examples would be:
WHERE FirstName LIKE "Jon*"
WHERE LastName LIKE "*ash*"
Where Addr1 LIKE "*Washington*"
If you have a thousand records in the table, it doesn't matter much what you do. If you have a million, every little thing matters. It isn't necessary to kill yourself to make everything as efficient as possible, just gain an understanding of efficient methods and apply them as general practice unless you need to deviate.
ASKER
Hi Pat,
Thanks for the insight.
I will update my 'documentation' with the information.
Thanks,
Bob C.
Thanks for the insight.
I will update my 'documentation' with the information.
Thanks,
Bob C.
ASKER
Rey's solution is the simplest and works correctly
Mike, your suggestion results in an error message that char is not valid.
Thanks for all your assistance,
Bob C.