asked on

Access 2003 - Query Criteria

I have a query with a mix of blanks; 4 digit codes and 8 digit codes under my Pr/Br field with the 4 digits being the pr's and the 8 digits being the Brs. I only want the blanks and Pr which have 4 digit codes to appear in my query. What could I type in the criteria under Pr/Br to only get the blanks and the pr's 4 digits to appear?

Thank you.

aikimark

I don't think you can do it in the query wizard criteria box. However, if you switch your view to SQL, you can add this condition to the Select statement:

WHERE Len(Trim([Pr/Br]))=0 or [Pr/Br] Like "####"

Open in new window

als315

If you have only 4 or 8 digits in your Pr/Br field, you can check length of string:
len([Pr/Br]) and criteria will be 4
If you can have characters, use criteria:
like "*####*"

aikimark

@als315

Your pattern would be more efficient if you dropped the leading and trailing wildcards: like "####" rather than like "*####*"

>>What could I type in the criteria under Pr/Br
Given this problem criterion, you should probably explicitly state that the length check part of your solution is not located under [Pr/Br].

als315

@aikimark:
I've added wildcards for possible case:
abc1234def
Cases 1234 and 56789012 can be selected by len().
There is one else possible case - field is numeric:
criteria will be < 10000
Until we have no examples we can only suppose something

aikimark

@als315

Since the question specified "digits" or "spaces", I don't think we would encounter "abc1234def".

I've a hunch that this field would be better as a nullable long integer field, both from a performance and data entry (validation) perspective.

SOLUTION

als315

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

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial