Wild card in query

[Guest]

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?

 

[Allen Browne]

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.

 

[John Spencer]

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*", ...

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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
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?

 

[Guest]

Thank you Allen worked perfectly!

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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
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?