Wild card in query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Akrt48 - 30 Nov 2005 15:58 GMT

I have a table I am trying to query I want to return all rows with H* and
convert them to other text in my column, here is the query, can't figure out
what I am doing wrong.
iif([field]="h*","handy",iif([field]="t*","tell",null)) I have tried it with
h* no asteriks but it doesn't work. Is this possible to do?

Reply to this Message

Allen Browne - 30 Nov 2005 16:15 GMT

Use the Like operator with wildcards:
IIf([Field] Like "h*", ...

If you are trying to replace the leading h with a leading t, you would need
to use the Criteria of:
Like "h*"
and in the Update row of the query:
"t" & Mid([Field], 2)

The Replace() function might also be useful.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a table I am trying to query I want to return all rows with H* and
> convert them to other text in my column, here is the query, can't figure
[quoted text clipped - 3 lines]
> with
> h* no asteriks but it doesn't work. Is this possible to do?

Reply to this Message

John Spencer - 30 Nov 2005 16:53 GMT

Allen I misread your reply, so if you will allow me to expand on your first
answer.

IIF([field]Like "h*","handy",IIF ([field] LIKE "t*","tell",null))

> Use the Like operator with wildcards:
> IIf([Field] Like "h*", ...
[quoted text clipped - 14 lines]
>> with
>> h* no asteriks but it doesn't work. Is this possible to do?

Reply to this Message

Akrt48 - 30 Nov 2005 19:19 GMT

Thank you Allen worked perfectly!

> Use the Like operator with wildcards:
> IIf([Field] Like "h*", ...
[quoted text clipped - 14 lines]
> > with
> > h* no asteriks but it doesn't work. Is this possible to do?

Reply to this Message