Wild card in query

A

Akrt48

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

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.
 
J

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

Allen Browne said:
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.

Akrt48 said:
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?
Click to expand...
Click to expand...
 
A

Akrt48

Thank you Allen worked perfectly!

Allen Browne said:
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.

Akrt48 said:
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?
Click to expand...
Click to expand...
 
Top