Query design using wildcards

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robertofriends - 03 Jan 2005 19:27 GMT

Howdy! I'm trying to query a table using wildcards. Is this possible?

The table contains a field with alphanumeric characters... QP-100, QP-101,
QF-100, QF-101, QM-100 etc. I expected the parameters value of QP*, QP? or
QP# to get all the QP numbered records but nothing seems to work. Any ideas?

(I'm using Access 2003)

Reply to this Message

Douglas J. Steele - 03 Jan 2005 20:01 GMT

2 things come to mind. Have you replaced the equal sign with the word Like?
And, if by some chance you're using ADO recordsets, the wildcard will be %,
not *.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Howdy! I'm trying to query a table using wildcards. Is this possible?
>
[quoted text clipped - 3 lines]
>
> (I'm using Access 2003)

Reply to this Message

Robertofriends - 03 Jan 2005 20:29 GMT

My table contains a field called "record number." The data in "record
number" might be QP-100, QP-101, etc. or QF-100, QF-101 etc. or QM-100,
QM-101 etc. I'd like the query to get all the QP's. My query has all the
fields of the table with the "show" checkbox selected on all of them. In the
Criteria: row of "record number", I typed [Enter the number:] so that the
parameter dialog bog box comes up for the users to enter the record number
for which they are looking. There really isn't an ='s sign used anywhere and
I've tried entering QP%. in the "Enter the number:" dialog box but the query
comes up blank... (I know the table contain QP's) If I type a specific QP,
such as QP-424, into the dialog box (parameter value), the query retrieves
the QP-424 record. (To be honest, I'm not sure if I'm phrasing this
correctly.)

> 2 things come to mind. Have you replaced the equal sign with the word Like?
> And, if by some chance you're using ADO recordsets, the wildcard will be %,
[quoted text clipped - 9 lines]
> >
> > (I'm using Access 2003)

Reply to this Message

Douglas J. Steele - 03 Jan 2005 22:08 GMT

If you look at the SQL that Access is generating (View | SQL View on the
menu bar when the query's open in Design mode), you'll see that it's
generating WHERE [record number] = [Enter the number:].

Change your criteria to Like [Enter the number:] or, if you don't want the
users to have to key in the wildcard, Like [Enter the number:] & "*"

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> My table contains a field called "record number." The data in "record
> number" might be QP-100, QP-101, etc. or QF-100, QF-101 etc. or QM-100,
[quoted text clipped - 22 lines]
> > >
> > > (I'm using Access 2003)

Reply to this Message

Robertofriends - 03 Jan 2005 22:27 GMT

Mr. Steele,

The "like &"*" " did the trick...

I really appreciate your help, and patience.

Many Thanks,
Robert

Reply to this Message