Full Version: Format A Field Value In A Table Through Query
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
kat123
Hi,

Is there anyway that field values can be formatted through a query?

I have a table called vehicles which has many fields. The table already has more than 10000 records. One of the field is NumberPlate and the values written in this field are in the format xx-abcd-xx

(xx are letters then hyphen, abcd are digits, then again hyphen and then letters again). I want to remove those hyphens and possible spaces between letters and digits so that it looks like xxabcdxx. I can't do it one by one coz it has more than 10000 records. Can I write some query to change these values in the table? If so how the query should be?

I appreciate any help.

Thanks
Jeff B.
Take a look in Access HELP re: Left(), Mid(), Right() and InStr() functions... You can use those in a query.
JimBurke
You asked 'Can I write some query to change these values in the table'. Don't do that unless you are sure your application can handle it!! I would think that since that is the way the field has been set up, there may be code that expects those dashes to be there. If you are absolutely certain that making that change will not cause problems, then do as Jeff said. It all depends on how this field's values exist in your database. Are there always two characters, then a dash, four more chars, another dash, then 2 chars? If so you could set the field to 'left(fieldName,2) & mid(fieldname,4,4) & right(fieldName,2)', where 'fieldName' is the name of that field. If the field isn't always formatted exactly the same, you'd have to get a little more complex with it, using IIf statements and InStr to check for dashes, etc. Actually, if you want to simpy get rid of any dashes, and don't need to worry about anything else, you could just use Replace(fieldName,"-",""). But as I said, be VERY careful before making a change like that!!!
Jeff B.
<Jim>

Just to clarify, I wasn't suggesting creating an Update query. I was suggesting using those functions to "format" the existing values, per the initial statement.
Jeff B.
<kat123>

If the existing data is actually three pieces of data, then your table structure would probably benefit from further normalization. A basic 'rule' of database design is "one fact, one field".

It would be possible to separate out those three parts and put each into its own field ... but as <Jim> points out, this is fraught with danger! Make sure you have a backup copy of the database... or two ... or three!
kat123
Thanks,

I just wanted to replace those characters. How you do it if you want to replace more than one characters? In my case I want to remove spaces and hyphens.
vtd
1. Firstly, check the InputMask and the Format Property of the Field in the Table Design View to make sure you don't have anything special in these Properties. The hyphen can be created, either as part of the value actually stored or as part of the display of the values but the actual value stored may not have the hyphens, by settings in these Properties.

2. If the above Properties are not applicable, then you actually have the hephens stored as part of the value. In this case, do you actually want the stored value changed in the Table or do you actually just want the display of the value to be shown without the hypens?

If the above Properties in Q1 are not applicable, you can use the Replace() function in an UPDATE Query (to changed the stored value) or a SELECT Query (to diplay the stored value differently without changing the stored value) to do what you need.
kat123
yes the properties are not applicable. I want to update the stored value. My question is how to apply two changes, I mean I want to replace the hyphens and the spaces between the letters and the digits. I use Replace(NumberPlate, "-","") as I was suggested earlier. That replace the hyphens only. How do I add the spaces replacement in the same query?

I am very poor on queries,

Thanks again for your help
Larry Larsen
Hi
Try using a multiple statement:

eg:
? Replace(Replace("123 - a bc d -3 45","-","")," ","")

Result:
123abcd345

HTH's
thumbup.gif
vtd
To use Larry's expression in an UPDATE Query, you would have the SQL String of the Query something like:

CODE
UPDATE [YourTable]
SET [YourField] = Replace(Replace([YourField],"-","")," ","")
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.