I am working with data that is stored in the table as a long number but is most useful when shown to the user with a
custom format ##-##-###-###-####.
The help files say that if I apply this format to the table field, the format will be inherited everywhere the data is used and shown. This is sadly not true.
If I run a query against the table the resulting data set will either show an un-formatted number or one that looks like #########-#-#-#-#.
If I set the number format to Custom and retype the format pattern in the query properties and rerun it, I will get results with the proper format once.
Despite having saved the query after setting the format, re-running the query from scratch will produce the messed up format not the one I entered.
This is true in Excel as well. You can set the format and view it, but reopening the spreadsheet will produce the data with an incorrect format.
Exporting a correctly formatted data set from Access to Excel with formatting will produce an Excel spreadsheet with bad formatting.
So I have several questions:
1. How do I make MY format persistent and stick to the data wherever it is used?
2. How can I program a new default format so I can just select my format pattern from the drop-down list under format? I'd like to do this for Access and Excel since I use the data across both platforms. I shouldn't have to keep retyping ##-##-###-###-#### every time.
3. Why does Access and Excel (my assumption) pervert ##-##-###-###-#### to #########-#-#-#-#? By observation, that seems to be what is going on since the number of hyphens is the same, but the number holders (#) get changed.
4. Why is this formatting a one time use thing? It works once correctly and then no longer?