update query with asterisk

  • Thread starter Thread starter Klaas
  • Start date Start date
K

Klaas

I have an update query with a Replace function to remove all characters
after the first space in a field, myfield:

Replace ([myfield], " *", "")

The asteriks doesn't work in this function, and other wildcards don't work
either. Without wildcards, Replace works fine. Is this a common feature of
the Replace function? How can I solve this wildcard problem in my update
query? Any hint to a solution is appreciated greatly.

Klaas
 
Dear Klaas:

Replace doesn't recognize any wildcards. Only LIKE does (from what I
can think of right now). Anyway, Replace() isn't close to what you
need.

LEFT(myfield, IIf(INSTR(myfield, " ") = 0, LEN(myfield),
INSTR(myfield, " ") - 1)

I built in protection in case you have strings that have no space.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

Thank you very much!

Klaas



Tom Ellison wrote in microsoft.public.access.queries:
Dear Klaas:

Replace doesn't recognize any wildcards. Only LIKE does (from what I
can think of right now). Anyway, Replace() isn't close to what you
need.

LEFT(myfield, IIf(INSTR(myfield, " ") = 0, LEN(myfield),
INSTR(myfield, " ") - 1)

I built in protection in case you have strings that have no space.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have an update query with a Replace function to remove all
characters after the first space in a field, myfield:

Replace ([myfield], " *", "")

The asteriks doesn't work in this function, and other wildcards don't
work either. Without wildcards, Replace works fine. Is this a common
feature of the Replace function? How can I solve this wildcard problem
in my update query? Any hint to a solution is appreciated greatly.

Klaas
Click to expand...
Click to expand...
 
Tom Ellison wrote in microsoft.public.access.queries:
Replace doesn't recognize any wildcards. Only LIKE does (from what I
can think of right now). Anyway, Replace() isn't close to what you
need.

LEFT(myfield, IIf(INSTR(myfield, " ") = 0, LEN(myfield),
INSTR(myfield, " ") - 1)

I built in protection in case you have strings that have no space.
Click to expand...

My query design grid warns that a closing parenthesis is missing. Also,
the design grid unexpectedly adds quotation marks to myfield turning it
into a string "myfield". The following formula perfoms well in my Update
To field:

Left([myfield],IIf(InStr([myfield]," ")=0,Len([myfield]),InStr
([myfield]," ")-1))

Thanks a lot for pointing to a working solution.

Klaas
 
Dear Klaas:

Sorry about the right paren. I hope it wasn't that bad to figure out.

The brackets are necessary if you work in the design grid, but not if
you work in SQL view. As I work only in SQL view, my queries tend to
be that way. But the brackets aren't a hinderance in SQL view, so
I'll try to use them in the future when posting here.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom Ellison wrote in microsoft.public.access.queries:
Replace doesn't recognize any wildcards. Only LIKE does (from what I
can think of right now). Anyway, Replace() isn't close to what you
need.

LEFT(myfield, IIf(INSTR(myfield, " ") = 0, LEN(myfield),
INSTR(myfield, " ") - 1)

I built in protection in case you have strings that have no space.
Click to expand...

My query design grid warns that a closing parenthesis is missing. Also,
the design grid unexpectedly adds quotation marks to myfield turning it
into a string "myfield". The following formula perfoms well in my Update
To field:

Left([myfield],IIf(InStr([myfield]," ")=0,Len([myfield]),InStr
([myfield]," ")-1))

Thanks a lot for pointing to a working solution.

Klaas
Click to expand...
 
Back
Top