query filter not like

[Murata-PS]

I have a field that contains 6 digits, then an astrik, then 3 digits, then an
astrik , then possible 2 digits, then an astrik and 1 digit. I need to
filter for all but the records that contain the last astrick and 1 digit. I
have tried "Not Like "######" & "*" & "###" & "*" & "#" & "*" & "#" - but no
luck - can anyone help ?

 

[Jerry Whittle]

How about another tack. Seems that you want fields with less than 14
characters. Put something like this in the Field of the query.

TheLenght: Len([TheFieldName)

In the criteria for that field put: <14

 

[John Spencer]

Not Like "######[*]###[*]*[*]#"

That would ignore records with 6 digits followed by an asterisk plus 3
digits and an asterisk and ending in an asterisk followed by a digit.
IF you always had exactly two digits prior to the end then

Not Like "######[*]###[*]##[*]#"

IF you wanted to test for three options - two, one, or none - in the
penultimate group then

Not Like "######[*]###[*]##[*]#"
and Not Like "######[*]###[*]#[*]#"
and Not Like "######[*]###[*][*]#"

The brackets around the asterisk make the asterisk be treated a just a
character and not a wild card.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

I have a field that contains 6 digits, then an astrik, then 3 digits, then an
astrik , then possible 2 digits, then an astrik and 1 digit. I need to
filter for all but the records that contain the last astrick and 1 digit. I
have tried "Not Like "######" & "*" & "###" & "*" & "#" & "*" & "#" - but no
luck - can anyone help ?

The trouble is that an asterisk is itself a wildcard, so searching for it will
treat it as a wildcard, matching any string of characters or nothing at all.
The getaround is to put it in square brackets so that it will be treated as a
literal asterisk, not a wildcard:

NOT LIKE "######[*]###[*]#"

will find all records except those with five digits, a literal asterisk, three
digits, a literal asterisk, and one digit.