Delete characters and add * in front and last characters in ACCESS QUERY

[LALTIZER]

BEFORE AFTER
PO00699873 *699873*
PO00700521 *700521*
PO00698336 *698336*

I have a column called Vendor PO Number in my access query and I would like to be able to delete the first four characters and then add a wild card symbol * in front and the last characters (see above examples)

I figure out how to delete the first four digits by building an expression
Vendor PO: Mid([Vendor PO Number],5)

BUT

I have not figure (or if possible) to add * in front and behind the last characters. Finish product should look like *699873* from the original format of PO006998723.


I would appreicate anyone help.

[alansidman]

Try an update query. Your expression would be "*" & Mid([Vendor PO Number],5) & "*"

Look at this video on manipulating data with a query

http://www.datapigtechnologies.com/f...anipulate.html

[LALTIZER]

Thank you so much, you saved some of my brain cells

[LALTIZER]

alansidman,

Question, I have rows which does not have any chararacter and i would like to show nothing (blank) in these rows instead of **, (I would still use this expression "*" & Mid([Vendor PO Number],5) & "*" for row which as characters.

Thanks again

row 1 *699873*
row 2 ** ( I would like blank and not **)
row 3 *698336*
row 4 ** ( I would like blank and not **)

[alansidman]

I am guessing you will need to run a second update query after the first one that places a null in the fields holding the values **.

I never asked but did you want to update the table or did you just wish that the PO be displayed in a query with the asterisks. I had assumed you wished to update the table and that may not have been your wishes.

Alan

[Norie]

Try this for the expression.

Iif(IsNull([Vendor PO Number]), "", "*" & Mid([Vendor PO Number],5) & "*")

By the way, just curious really but why are you doing this?

[LALTIZER]

Norie, I have a database which everyone will just copy and paste this string (*699873*) into another program and the data will be retrieve. The expression is also entering ** in the rows which has no data, but my group is getting confused with the ** in the cell.

[Norie]

Did you try the expression I posted?

[LALTIZER]

Alan,

This work perfectly, my database is updated daily from Oracle report and this expression helps by automatically delete the first four digits and add wild card before/after the numbers. thanks again

[LALTIZER]

Yes and work great and this will make my group happy. Thank you for your help and have a great day.