Dealing with the # character

[frankivalli]

Thanks in advance.



I have a database that was populated using many excel sheets and one of the fields [partDescription] uses the # character over and over to represent "lbs". I realize that this is a wild card for digits in VB. Is there a quick fix for finding and replacing this character?

[rpeare]

it's not a wildcard it notates the beginning/end of a date field.

what do you want your result to be?

Two separate fields one for the quantity, one for the measure

i.e.

ItemWeight  ItemUnit
1           Pounds
2           Ounces
10          Grams

if you don't care about the unit measure or just want to replace it with a string (which I don't recommend)

you could run an update query on the current field that and in the UPDATE TO row of the query put something like Replace([Fieldname], "#", "lbs")

if you want to split the units and the actual value that would also be a update query but you'd have to split the field using something like

left([fieldname], instr([fieldname], "#") -1)
and
right([fieldname], len([fieldname]) - instr([fieldname],"#"))

[booismycat]

Once you have imported to Access, you can run an Update Query to remove the # from the field(s) in question.
Easiest way to remove unwanted characters (in mass) that I know of.

I would suggest importing to a temp table first until you are comfortable with your process.