Update qeury to change existing data

  • Thread starter Thread starter Darren via AccessMonster.com
  • Start date Start date
D

Darren via AccessMonster.com

Hi I have a table with a field called EWC. I originally stored the data
using an input mask to the format
00 00 00.
Now in their infinite wisdom the people i have to report to want it to appear
as
000000

I removed the input mask and this allows me to enter new data in the new way
however all the existing data still displays using the old mask. I assume i
use and update query to change the existing data but i dont really know where
to start with it. What do i need to do to change all the existing records

an example is
13 05 08
i would want as
130508

Thanks for any help
 
You're going along the right lines, if you weren't involving an InputMask.

Believe it or not, your data is actually being stored as 6 characters, the
InputMask is forcing the display and enter of data in a different manner.

I just tried this on a new table:
1 text field, with an InputMask of 00\ 00\ 00
Data can only be entered in the above format (similar to yours). Create a
quick report to see the same thing as well.
I then removed the InputMask from the text box in the report, the field in
the table, and both.

If you change the properties of the text box in the report, only that report
will be modified.
If you change the property in the table, only reports created from this
point on will be affected. So,
Change the property in the table, and the property for all field in all
relevant reports.

Let me know any anomalies.
HTH,

Piers
 
Create a query and put the field to be updated as the only column.
Change the query to an update query and in the update to row put

Replace([FieldName]," ","")
 
Darren

Make an Update query on the table, and set the Update To on field EWC to
the expression:

Replace([EWC]," ","")

which replaces all spaces in the string (" ") with zero-length strings ("").

Note: it never hurts to back up before trying something new, just in case!

HTH,
Nikos
 
Thanks everybody for the help it worked a treat


Nikos said:
Darren

Make an Update query on the table, and set the Update To on field EWC to
the expression:

Replace([EWC]," ","")

which replaces all spaces in the string (" ") with zero-length strings ("").

Note: it never hurts to back up before trying something new, just in case!

HTH,
Nikos
Click to expand...
 
Back
Top