Link to home
Start Free TrialLog in
Avatar of Bob Collison
Bob CollisonFlag for Canada

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_NAME_FILTER]="Default Organization",Like "*",[Forms]![M-50-000 - Honours - Awards Module Menu]![M50000_ORG_LONG_NAME_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.
SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bob Collison

ASKER

Hi Experts,
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.
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_FILTER] = [Forms]![M-50-000 - Honours - Awards Module Menu]![M50000_ORG_LONG_NAME_FILTER] OR [Forms]![M-50-000 - Honours - Awards Module Menu]![M50000_ORG_LONG_NAME_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.
Hi Pat,

Thanks for the insight.

I will update my 'documentation' with the information.

Thanks,
Bob C.