formatting in table (Office 2007)

Topic

New Reply

Have an access db with a phone field formatted (xxx) xxx-xxxx. However, when data was imported it is in the format xxx-xxx-xxxx. How to change the formatting with an ALTER TABLE command?

Reply | Quote

Replies

I don’t think you can use ALTER TABLE for that.
Do you just want to change the display format, or do the data themselves have to be converted?

Reply | Quote

Thanks for the response Hans. Yes, the data needs to be converted.

Reply | Quote

ALTER TABLE changes the structure of a table. To convert data, you can use an update query. The expression in the Update to line would look like

"(" & Left([FieldName],3) & ") " & Mid([FieldName],5)

where FieldName is the name of the relevant field.

Reply | Quote

Thanks, Hans. That did what I wanted.

Is it possible to change a date field from x /xx/xxxx to 0x/xx/xxxx. If the month is < 10 but the month is in the first spot, move the month (i.e. 1-9) to the right and replace the first spot with a 0.

Reply | Quote

If you have values of the form x /xx/xxxx, they are text values, not real date values. You could add a date/time field to the table, and populate it in an update query using the following expression in the Update to line:

DateValue([FieldName])

where FieldName is the name of the text field. If you prefer to keep on using the text field, you could update the text field to

IIf(Mid([FieldName],2,1)=" ","0" & Left([FieldName],1),Left([FieldName],2)) & Mid([FieldName],3)

Reply | Quote

Hans,

If i put the IIF statement into a query, I do not see any changes.

Reply | Quote

Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

Thanks, Hans.

Reply | Quote

The birthdate field in the table is already a date/time field, so there are no values of the form “x /xx/xxxx”

Reply | Quote

Apparently I forgot to change it back to text. Either way you will note that there is a space after the month before the / where the format should be either xx/xx/xxxx or x/xx/xxxx at least that is the format which does not cause an error in VB. Is there a way to change the records without doing it 1 by 1?

Reply | Quote

Set the format property of the birthdate field to “mm/dd/yyyy” if you want the months first, or “dd/mm/yyyy” if you want days first.

Reply | Quote

As John says, you don’t have to do anything with the records, just set the Format property of the field. Apparently your default date format is a bit strange – on my PC (with dd-mm-yyyy as default format) I didn’t see any spaces in the dates.

Reply | Quote

Thanks to all.

Reply | Quote