Replacing * in order number

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

Jerry Whittle

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

KarenY

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

KarenY

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

KarenY

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

KARL DEWEY

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

KarenY

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

John W. Vinson

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

KARL DEWEY

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

KarenY

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

John Spencer

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

KARL DEWEY

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

KarenY

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIF with multiple requirement 8
WHERE -IIF ? 20
Updating Order# in an access form 0
create queries 7
How to open Microsoft Word documents from a Microsoft Access Form 8
Query criteria used twice in same query 4
Frustrated - Creating Auto Number 3
Making a wild hash of things 1

Top