Wildcards with Like in Microsoft Access

[Alex]

Hi all,

I'm trying a very simple Like statement in MS Access XP, but it's
acting strange. I've always used a percent (%) for wildcards in MS
SQL, and I thought this was the same in MS Access... but I'm finding I
must use an astrisk (*) instead.

Example, with the following layout for Accounts table:
Text AcctNumber (Primary Key)
Text FirstName
Text LastName
Date AdmitDate
Number AmountDue

I use the following statement:
select * from Accounts where lastname like '%th'
.... it returns nothing, but the following statement
select * from Accounts where lastname like '*th'
Does work, returning Smith and others that end in 'th'.

So, in MS Access is a wildcard * instead of % ??? Just checking.

Thanks,

Alex.

[fredg]

Re: Wildcards with Like in Microsoft Access

On 1 Apr 2004 08:48:54 -0800, Alex wrote:
[color=blue]
> Hi all,
>
> I'm trying a very simple Like statement in MS Access XP, but it's
> acting strange. I've always used a percent (%) for wildcards in MS
> SQL, and I thought this was the same in MS Access... but I'm finding I
> must use an astrisk (*) instead.
>
> Example, with the following layout for Accounts table:
> Text AcctNumber (Primary Key)
> Text FirstName
> Text LastName
> Date AdmitDate
> Number AmountDue
>
> I use the following statement:
> select * from Accounts where lastname like '%th'
> ... it returns nothing, but the following statement
> select * from Accounts where lastname like '*th'
> Does work, returning Smith and others that end in 'th'.
>
> So, in MS Access is a wildcard * instead of % ??? Just checking.
>
> Thanks,
>
> Alex.[/color]

Yup.
Like "*" & [Enter search word] & "*"
will prompt for, and find all words with the entered word any where in
the field.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Douglas J. Steele]

Re: Wildcards with Like in Microsoft Access

"fredg" <fgutkind@examp le.invalid> wrote in message
news:8ph8s3vdav 6c.4m2y4xvbe5iw .dlg@40tude.net ...[color=blue]
> On 1 Apr 2004 08:48:54 -0800, Alex wrote:[color=green]
> > So, in MS Access is a wildcard * instead of % ??? Just checking.
> >[/color]
>
> Yup.
> Like "*" & [Enter search word] & "*"
> will prompt for, and find all words with the entered word any where in
> the field.[/color]

Unless you're using ADO, in which case it's %...

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(No private e-mails, please)