Cannot Format Data When Adding Records in Access Form (151522)

SUMMARY

When you add a new record to a list in Microsoft Excel using the Access Form option on the Data Menu, the data returned to the spreadsheet may not be formatted correctly.

MORE INFORMATION

This may happen if the information that you are typing into the form contains zip codes that have leading zeros or dates, and the worksheet cells into which the data will be returned are not formatted with either a zip code or date number format. The Access Form, unlike the Data Form in Microsoft Excel, is not aware of any formatting on the Microsoft Excel spreadsheet. As a result, the leading zero in the zip code will be truncated and the dates will be displayed as serial numbers when the information is returned to the spreadsheet.

WORKAROUND

To work around this behavior, format the columns on the spreadsheet that contain the dates or zip codes before entering new records. To format the columns, follow these steps:

Click the column header to select the entire column.
On the Format Menu, click Cells, and then click the Number tab.
To format zip codes, choose the Special Category, and then choose the Zip Code format under Type. To format dates, click the Date Category and choose the appropriate date format under Type.
Click the OK button.

REFERENCES

For more information about number formatting, click Help Topics on the Help Menu in Microsoft Excel, select the Index tab and type:

   number formatting