Replacing * in order number

  • Thread starter Thread starter KarenY
  • Start date Start date
K

KarenY

Hi,
We have customers input orders themselves through internet. Our order field
accepts anything the user input.
Some of the orders using * prefix, e.g. *-DXB12345.

How do i replace a * by using IIF or other function in a select query ?

I just want to replace the * for letters "HP" and the rest after the *
remain the same after HP, e.g. *-DXB12345 will become HP-DXB12345
OR *-DXB12345SCRAP will become HP-DXB12345SCRAP.

The order number field name is CO.

I have tried : IIF(Left([CO],1)="*","HP & Right([CO],14)="*"",[CO])

It returned as "error" and I tried some others, also not working.
Please help.
 
Replace([CO], "*", "HP")

A concern with the Replace function is that it will search the entire
string. Therefore if a * could be somewhere besides at the beginning, it
might not work best for you.
 
No, Judith, it didn't work out.
I added a "(" after the LENG because it didn''t let me go through.
Then, the result : All the other fields without * became blank and the one
with * became #Error.

I got the other 2 replies with function REPLACE, it works to replace the *.
However I just want to replace the prefix * in CO, and there are some other
customers input their order with a suffix * in the CO !

So I just want to replace the prefix *. sorry.

JudithJubilee said:
Hi KarenY,

Try this:

=IIF(LEFT([CO],1)="*","HP"&RIGHT([CO],LEN[CO]-1))
--
Hope this helps


KarenY said:
Hi,
We have customers input orders themselves through internet. Our order field
accepts anything the user input.
Some of the orders using * prefix, e.g. *-DXB12345.

How do i replace a * by using IIF or other function in a select query ?

I just want to replace the * for letters "HP" and the rest after the *
remain the same after HP, e.g. *-DXB12345 will become HP-DXB12345
OR *-DXB12345SCRAP will become HP-DXB12345SCRAP.

The order number field name is CO.

I have tried : IIF(Left([CO],1)="*","HP & Right([CO],14)="*"",[CO])

It returned as "error" and I tried some others, also not working.
Please help.
Click to expand...
Click to expand...
 
You are right, Jerry, it replaces the * in the other orders too.
Is there a way for the prefix * (1st position in the CO) ?

Anyway, I've learned this REPLACE too. thanks


Jerry Whittle said:
Replace([CO], "*", "HP")

A concern with the Replace function is that it will search the entire
string. Therefore if a * could be somewhere besides at the beginning, it
might not work best for you.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

KarenY said:
Hi,
We have customers input orders themselves through internet. Our order field
accepts anything the user input.
Some of the orders using * prefix, e.g. *-DXB12345.

How do i replace a * by using IIF or other function in a select query ?

I just want to replace the * for letters "HP" and the rest after the *
remain the same after HP, e.g. *-DXB12345 will become HP-DXB12345
OR *-DXB12345SCRAP will become HP-DXB12345SCRAP.

The order number field name is CO.

I have tried : IIF(Left([CO],1)="*","HP & Right([CO],14)="*"",[CO])

It returned as "error" and I tried some others, also not working.
Please help.
Click to expand...
Click to expand...
 
Thanks, Karl.
Yes, it works for all the * in the [CO]. Is there a way to replace just the
1st position * in the [CO] ?

KARL DEWEY said:
Replace([CO], Chr(42), "HP")

--
Build a little, test a little.


KarenY said:
Hi,
We have customers input orders themselves through internet. Our order field
accepts anything the user input.
Some of the orders using * prefix, e.g. *-DXB12345.

How do i replace a * by using IIF or other function in a select query ?

I just want to replace the * for letters "HP" and the rest after the *
remain the same after HP, e.g. *-DXB12345 will become HP-DXB12345
OR *-DXB12345SCRAP will become HP-DXB12345SCRAP.

The order number field name is CO.

I have tried : IIF(Left([CO],1)="*","HP & Right([CO],14)="*"",[CO])

It returned as "error" and I tried some others, also not working.
Please help.
Click to expand...
Click to expand...
 
UNTESTED --- Try this --
Replace(Left([CO], InStr([CO], Chr(42)), Chr(42), "HP")

--
Build a little, test a little.


KarenY said:
Thanks, Karl.
Yes, it works for all the * in the [CO]. Is there a way to replace just the
1st position * in the [CO] ?

KARL DEWEY said:
Replace([CO], Chr(42), "HP")

--
Build a little, test a little.


KarenY said:
Hi,
We have customers input orders themselves through internet. Our order field
accepts anything the user input.
Some of the orders using * prefix, e.g. *-DXB12345.

How do i replace a * by using IIF or other function in a select query ?

I just want to replace the * for letters "HP" and the rest after the *
remain the same after HP, e.g. *-DXB12345 will become HP-DXB12345
OR *-DXB12345SCRAP will become HP-DXB12345SCRAP.

The order number field name is CO.

I have tried : IIF(Left([CO],1)="*","HP & Right([CO],14)="*"",[CO])

It returned as "error" and I tried some others, also not working.
Please help.
Click to expand...
Click to expand...
Click to expand...
 
No, not worked out.
Result:....the function containg the wrong number of arguments" ?

KARL DEWEY said:
UNTESTED --- Try this --
Replace(Left([CO], InStr([CO], Chr(42)), Chr(42), "HP")

--
Build a little, test a little.


KarenY said:
Thanks, Karl.
Yes, it works for all the * in the [CO]. Is there a way to replace just the
1st position * in the [CO] ?

KARL DEWEY said:
Replace([CO], Chr(42), "HP")

--
Build a little, test a little.


:

Hi,
We have customers input orders themselves through internet. Our order field
accepts anything the user input.
Some of the orders using * prefix, e.g. *-DXB12345.

How do i replace a * by using IIF or other function in a select query ?

I just want to replace the * for letters "HP" and the rest after the *
remain the same after HP, e.g. *-DXB12345 will become HP-DXB12345
OR *-DXB12345SCRAP will become HP-DXB12345SCRAP.

The order number field name is CO.

I have tried : IIF(Left([CO],1)="*","HP & Right([CO],14)="*"",[CO])

It returned as "error" and I tried some others, also not working.
Please help.
Click to expand...
Click to expand...
Click to expand...
 
You are right, Jerry, it replaces the * in the other orders too.
Is there a way for the prefix * (1st position in the CO) ?
Click to expand...

I'd suggest an update query:

UPDATE tablename
SET CO = Mid([CO], 2)
WHERE CO LIKE "[*]*";

Putting the asterisk in brackets tells Access to treat it as a literal
character rather than a wildcard; the Mid() function extracts the rest of the
string starting at position 2.
 
Not enough closing parenthesis --
Replace(Left([CO], InStr([CO], Chr(42))), Chr(42), "HP")

--
Build a little, test a little.


KarenY said:
No, not worked out.
Result:....the function containg the wrong number of arguments" ?

KARL DEWEY said:
UNTESTED --- Try this --
Replace(Left([CO], InStr([CO], Chr(42)), Chr(42), "HP")

--
Build a little, test a little.


KarenY said:
Thanks, Karl.
Yes, it works for all the * in the [CO]. Is there a way to replace just the
1st position * in the [CO] ?

:

Replace([CO], Chr(42), "HP")

--
Build a little, test a little.


:

Hi,
We have customers input orders themselves through internet. Our order field
accepts anything the user input.
Some of the orders using * prefix, e.g. *-DXB12345.

How do i replace a * by using IIF or other function in a select query ?

I just want to replace the * for letters "HP" and the rest after the *
remain the same after HP, e.g. *-DXB12345 will become HP-DXB12345
OR *-DXB12345SCRAP will become HP-DXB12345SCRAP.

The order number field name is CO.

I have tried : IIF(Left([CO],1)="*","HP & Right([CO],14)="*"",[CO])

It returned as "error" and I tried some others, also not working.
Please help.
Click to expand...
Click to expand...
Click to expand...
 
The results are completely incorrect.

KARL DEWEY said:
Not enough closing parenthesis --
Replace(Left([CO], InStr([CO], Chr(42))), Chr(42), "HP")

--
Build a little, test a little.


KarenY said:
No, not worked out.
Result:....the function containg the wrong number of arguments" ?

KARL DEWEY said:
UNTESTED --- Try this --
Replace(Left([CO], InStr([CO], Chr(42)), Chr(42), "HP")

--
Build a little, test a little.


:

Thanks, Karl.
Yes, it works for all the * in the [CO]. Is there a way to replace just the
1st position * in the [CO] ?

:

Replace([CO], Chr(42), "HP")

--
Build a little, test a little.


:

Hi,
We have customers input orders themselves through internet. Our order field
accepts anything the user input.
Some of the orders using * prefix, e.g. *-DXB12345.

How do i replace a * by using IIF or other function in a select query ?

I just want to replace the * for letters "HP" and the rest after the *
remain the same after HP, e.g. *-DXB12345 will become HP-DXB12345
OR *-DXB12345SCRAP will become HP-DXB12345SCRAP.

The order number field name is CO.

I have tried : IIF(Left([CO],1)="*","HP & Right([CO],14)="*"",[CO])

It returned as "error" and I tried some others, also not working.
Please help.
Click to expand...
Click to expand...
Click to expand...
 
IIF([C0] like "[*]*","HP" & Mid([CO],2),[CO])

OR you could use

IIF(Left([C0],1) = "*","HP" & Mid([CO],2),[CO])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
The results are completely incorrect.

KARL DEWEY said:
Not enough closing parenthesis --
Replace(Left([CO], InStr([CO], Chr(42))), Chr(42), "HP")

--
Build a little, test a little.


KarenY said:
No, not worked out.
Result:....the function containg the wrong number of arguments" ?

:

UNTESTED --- Try this --
Replace(Left([CO], InStr([CO], Chr(42)), Chr(42), "HP")

--
Build a little, test a little.


:

Thanks, Karl.
Yes, it works for all the * in the [CO]. Is there a way to replace just the
1st position * in the [CO] ?

:

Replace([CO], Chr(42), "HP")

--
Build a little, test a little.


:

Hi,
We have customers input orders themselves through internet. Our order field
accepts anything the user input.
Some of the orders using * prefix, e.g. *-DXB12345.

How do i replace a * by using IIF or other function in a select query ?

I just want to replace the * for letters "HP" and the rest after the *
remain the same after HP, e.g. *-DXB12345 will become HP-DXB12345
OR *-DXB12345SCRAP will become HP-DXB12345SCRAP.

The order number field name is CO.

I have tried : IIF(Left([CO],1)="*","HP & Right([CO],14)="*"",[CO])

It returned as "error" and I tried some others, also not working.
Please help.
Click to expand...
Click to expand...
Click to expand...
 
If the first asterisk is allways the left most character then why not use
this --
"HP-" & Right([CO], Len([CO]) - 2))

--
Build a little, test a little.


KarenY said:
The results are completely incorrect.

KARL DEWEY said:
Not enough closing parenthesis --
Replace(Left([CO], InStr([CO], Chr(42))), Chr(42), "HP")

--
Build a little, test a little.


KarenY said:
No, not worked out.
Result:....the function containg the wrong number of arguments" ?

:

UNTESTED --- Try this --
Replace(Left([CO], InStr([CO], Chr(42)), Chr(42), "HP")

--
Build a little, test a little.


:

Thanks, Karl.
Yes, it works for all the * in the [CO]. Is there a way to replace just the
1st position * in the [CO] ?

:

Replace([CO], Chr(42), "HP")

--
Build a little, test a little.


:

Hi,
We have customers input orders themselves through internet. Our order field
accepts anything the user input.
Some of the orders using * prefix, e.g. *-DXB12345.

How do i replace a * by using IIF or other function in a select query ?

I just want to replace the * for letters "HP" and the rest after the *
remain the same after HP, e.g. *-DXB12345 will become HP-DXB12345
OR *-DXB12345SCRAP will become HP-DXB12345SCRAP.

The order number field name is CO.

I have tried : IIF(Left([CO],1)="*","HP & Right([CO],14)="*"",[CO])

It returned as "error" and I tried some others, also not working.
Please help.
Click to expand...
Click to expand...
Click to expand...
 
Perfect! thanks a million !

John Spencer said:
IIF([C0] like "[*]*","HP" & Mid([CO],2),[CO])

OR you could use

IIF(Left([C0],1) = "*","HP" & Mid([CO],2),[CO])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
The results are completely incorrect.

KARL DEWEY said:
Not enough closing parenthesis --
Replace(Left([CO], InStr([CO], Chr(42))), Chr(42), "HP")

--
Build a little, test a little.


:

No, not worked out.
Result:....the function containg the wrong number of arguments" ?

:

UNTESTED --- Try this --
Replace(Left([CO], InStr([CO], Chr(42)), Chr(42), "HP")

--
Build a little, test a little.


:

Thanks, Karl.
Yes, it works for all the * in the [CO]. Is there a way to replace just the
1st position * in the [CO] ?

:

Replace([CO], Chr(42), "HP")

--
Build a little, test a little.


:

Hi,
We have customers input orders themselves through internet. Our order field
accepts anything the user input.
Some of the orders using * prefix, e.g. *-DXB12345.

How do i replace a * by using IIF or other function in a select query ?

I just want to replace the * for letters "HP" and the rest after the *
remain the same after HP, e.g. *-DXB12345 will become HP-DXB12345
OR *-DXB12345SCRAP will become HP-DXB12345SCRAP.

The order number field name is CO.

I have tried : IIF(Left([CO],1)="*","HP & Right([CO],14)="*"",[CO])

It returned as "error" and I tried some others, also not working.
Please help.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top