Exporting separator mask? (A2K, SP3)

Topic

New Reply

I have a bound textbox which has an input mask “000000-00-0000”. The data appeared okay when viewed in Access. However, when I exported the data to Excel, the separator masks “-” were gone, and the data appeared as, say, 1234567890, instead of 123456-78-90. How would I export my bound values as 123456-78-90 instead of 1234567890, i.e, how do I export the separator mask “-” too?

Reply | Quote

Replies

You’ll have to create a query that reconstructs the input.

You can specify whether the separators in a mask are stored in the field or not, default is not to store them. So in your case, the data have been stored without the separators.

In the query, create a calculated field:

FieldWithSeparators:Left([MyField],6)&”-“&Mid([MyField],7,2)&”-“&Right([MyField],4)

Substitute the appropriate field name for MyField.

For future reference: if you want to store separators in a field, add “;0” after the mask. A mask “00-00” will only store 4 digits (1234), whereas a mask “00-00;0” will store 2 digits, followed by a hyphen and 2 more digits (12-34).

Reply | Quote

Thanks Hans for reminding me some Access basis which I innocently ignored. I didn’t wait for the “future” but created a few lines of codes to add in the separator for all the same field in my database.

Reply | Quote