Change all the values of a field in a table

[morgan23]

Hi,

I am very new to MS Access and to VBA. I have a field in a table in MS Access that contains data in the format: xx-xxxx.xx , where x is a character.
I need to be able to compare this field with another field in a different table with the format as: xxxxxxxx. I would like to know how to write a procedure that I could run one time, to either remove the '-' and '.' from the first field, or add the '-' and '.' to the second field. Any advice on how to begin something like this (or some good references) would be greatly appreciated.

Thanks in advance,
Morgan23.

 

[FoFa]

You don't need a procedure, you can do it with a query.
Remove current characters:
LEFT([MyField],2) & MID([MyField],4,4) & RIGHT([MyField],2)
Add to current field:
Left([OtherField],2) & "-" & MID([OtherField],3,4) & "." & RIGHT([OtherField],2)

You can use this in a query (instead of actually changing the field) or you can use an update query to modify what you have. If you go the modify route, I would create a new field. Update that. Verify. Delete current field, then rename new field to old field name (basically).

 

[Pat Hartman]

If your date is actually stored as a string in a text field, then FoFa's suggestion will work correctly. However, if your date is stored in a date/time field as it should be, then it is a number, NOT a string and string functions such as Left(), Right(), and Mid() will not work correctly on it in all cases. If the field is a date, you can reformat it as a string by using the Format() function:

Format(YourDate, "yyyymmdd") - I'm assuming that is the correct field order. Your post didn't specify.

 

[morgan23]

Thanks for your quick response. The strings in the fields were not dates so the method FoFa suggested worked great.

Thanks to both of you for your help.

Morgan23