Results 1 to 3 of 3
  1. #1
    DataDummy is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    1

    Custom Format not persistent or inherited


    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?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it might have something to do with the number of digits -a long has a maximum number of 4,294,967,296 i.e. 10 digits - your format requires 14 digits

    either way, use of the # character says 'maybe there is a number, maybe not' so you should be useing 0

    00-00-000-000-0000

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would change the field type to Text.
    Any field that is a number and I don't/won't perform math on it, I set the field type to Text.

    Consider the USA Social security Number. In the Employee table, the field name would be "end_ssn" and type would be "Text". I only store the digits, not the dashes. In a query, the SSN column would be SSN: Format([end_ssn],"@@@-@@-@@@@").
    (I use queries for Form/Report the record source.)

    To enter the SSN, I set the Input Mask for the text box to "000\-00\-0000;;#".


    A Zip Code field would also be a text field.


    Haven't used/programmed Excel much in the last 30+years, so I can't speak to that.....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Custom field format
    By rwahdan@gmail.com in forum Access
    Replies: 2
    Last Post: 01-23-2018, 02:25 PM
  2. Now Function Custom Format
    By DrJohn in forum Access
    Replies: 9
    Last Post: 03-16-2016, 09:51 AM
  3. Custom Currency Format
    By Ichigo in forum Access
    Replies: 3
    Last Post: 04-26-2011, 03:34 AM
  4. Custom Data input mask or format?
    By RiverAnimal in forum Database Design
    Replies: 2
    Last Post: 12-06-2010, 09:58 AM
  5. Custom format for phone#
    By SmokingMan in forum Access
    Replies: 4
    Last Post: 08-02-2010, 10:41 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums