Access Query Using Like & Wildcard Operators

[Guest]

Does anybody know of a good way to build a query in Access (using the Like
operator and wildcards) to do the following:

Say I have a text field with the following records:

I want the query to return only the records where:
the last three characters are "MCC"
and
the characters preceding the MCC must be 0-4 numeric characters (digits).

SO if the query were working properly, the indicated records would be
returned.


Vals
----------------------
1234MCCI
123MCC <--
145MCC <--
13MCC <--
189MCCT
1MCCK
12345MCC
MCC <--
9MCC <--
ZMCC


The query criteria that comes closest to working, looks something like this:
Like "[####]MCC" but it's not right.

Thanks

 

[Nikos Yannacopoulos]

Kristie,

Try:

Like "*MCC"

HTH,
Nikos

 

[Guest]

Hi, Kristie.

Try a SQL statement that uses the VBA "IsNumeric" function for the logic on
the digits that might be found in the "Vals" field:

SELECT tblVals.Vals
FROM tblVals
WHERE (((Right ([Vals], 3) = "MCC") AND
(IsNumeric (Mid ([Vals], 1, Len ([Vals]) - 3))) AND
(Len ([Vals]) <= 7)) OR
(tblVals.Vals = "MCC"));

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question, please sign in to Microsoft's
Online Community and mark these posts, so that all may benefit by filtering

 

[Guest]

Thanks, Nikos. That was my first guess too. It returns the records with the
numeric characters that precede the "MCC" but it also returns the records
with alpha characters that precede the "MCC", like the "ZMCC" record listed
below. I need the query to distinguish between the numeric and the alpha
characters in the text field, and eliminate the records with alpha characters
that aren't "MCC".

Thanks,
Kristie

Kristie,

Try:

Like "*MCC"

HTH,
Nikos

Does anybody know of a good way to build a query in Access (using the Like
operator and wildcards) to do the following:

Say I have a text field with the following records:

I want the query to return only the records where:
the last three characters are "MCC"
and
the characters preceding the MCC must be 0-4 numeric characters (digits).

SO if the query were working properly, the indicated records would be
returned.


Vals
----------------------
1234MCCI
123MCC <--
145MCC <--
13MCC <--
189MCCT
1MCCK
12345MCC
MCC <--
9MCC <--
ZMCC


The query criteria that comes closest to working, looks something like this:
Like "[####]MCC" but it's not right.

Thanks

 

[Guest]

It works, Gunny! I was trying to make it too simple by using the Like
operator.

Thanks,
Kristie

Hi, Kristie.

Try a SQL statement that uses the VBA "IsNumeric" function for the logic on
the digits that might be found in the "Vals" field:

SELECT tblVals.Vals
FROM tblVals
WHERE (((Right ([Vals], 3) = "MCC") AND
(IsNumeric (Mid ([Vals], 1, Len ([Vals]) - 3))) AND
(Len ([Vals]) <= 7)) OR
(tblVals.Vals = "MCC"));

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question, please sign in to Microsoft's
Online Community and mark these posts, so that all may benefit by filtering

Does anybody know of a good way to build a query in Access (using the Like
operator and wildcards) to do the following:

Say I have a text field with the following records:

I want the query to return only the records where:
the last three characters are "MCC"
and
the characters preceding the MCC must be 0-4 numeric characters (digits).

SO if the query were working properly, the indicated records would be
returned.


Vals
----------------------
1234MCCI
123MCC <--
145MCC <--
13MCC <--
189MCCT
1MCCK
12345MCC
MCC <--
9MCC <--
ZMCC


The query criteria that comes closest to working, looks something like this:
Like "[####]MCC" but it's not right.

Thanks

 

[Guest]

You're welcome!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question, please sign in to Microsoft's
Online Community and mark these posts, so that all may benefit by filtering

It works, Gunny! I was trying to make it too simple by using the Like
operator.

Thanks,
Kristie

Hi, Kristie.

Try a SQL statement that uses the VBA "IsNumeric" function for the logic on
the digits that might be found in the "Vals" field:

SELECT tblVals.Vals
FROM tblVals
WHERE (((Right ([Vals], 3) = "MCC") AND
(IsNumeric (Mid ([Vals], 1, Len ([Vals]) - 3))) AND
(Len ([Vals]) <= 7)) OR
(tblVals.Vals = "MCC"));

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question, please sign in to Microsoft's
Online Community and mark these posts, so that all may benefit by filtering

Does anybody know of a good way to build a query in Access (using the Like
operator and wildcards) to do the following:

Say I have a text field with the following records:

I want the query to return only the records where:
the last three characters are "MCC"
and
the characters preceding the MCC must be 0-4 numeric characters (digits).

SO if the query were working properly, the indicated records would be
returned.


Vals
----------------------
1234MCCI
123MCC <--
145MCC <--
13MCC <--
189MCCT
1MCCK
12345MCC
MCC <--
9MCC <--
ZMCC


The query criteria that comes closest to working, looks something like this:
Like "[####]MCC" but it's not right.

Thanks

 

[John Spencer (MVP)]

= "MCC" OR
Like "#MCC" OR
Like "##MCC" OR
Like "###MCC" OR
Like "####MCC"

and it might be possible with
= "MCC" OR (Like "*MCC" AND NOT LIKE "[!0-9]*MCC")

It works, Gunny! I was trying to make it too simple by using the Like
operator.

Thanks,
Kristie

Hi, Kristie.

Try a SQL statement that uses the VBA "IsNumeric" function for the logic on
the digits that might be found in the "Vals" field:

SELECT tblVals.Vals
FROM tblVals
WHERE (((Right ([Vals], 3) = "MCC") AND
(IsNumeric (Mid ([Vals], 1, Len ([Vals]) - 3))) AND
(Len ([Vals]) <= 7)) OR
(tblVals.Vals = "MCC"));

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question, please sign in to Microsoft's
Online Community and mark these posts, so that all may benefit by filtering

Does anybody know of a good way to build a query in Access (using the Like
operator and wildcards) to do the following:

Say I have a text field with the following records:

I want the query to return only the records where:
the last three characters are "MCC"
and
the characters preceding the MCC must be 0-4 numeric characters (digits).

SO if the query were working properly, the indicated records would be
returned.


Vals
----------------------
1234MCCI
123MCC <--
145MCC <--
13MCC <--
189MCCT
1MCCK
12345MCC
MCC <--
9MCC <--
ZMCC


The query criteria that comes closest to working, looks something like this:
Like "[####]MCC" but it's not right.

Thanks