Results 1 to 5 of 5
  1. #1
    lmcm1977 is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2018
    Posts
    9

    Data type error - leading zeros format causing problems

    Hi



    I have a field which contains a 10 digit "number". I have set the data type to "0000000000" this works fine for some numbers but I am also getting error messages for certain numbers and I cant work out why.

    I have generated an error message for these numbers
    2312774515
    2504521588
    3108115458


    but these numbers work

    2002203154
    1908771425

    am I blind and I cant see a pattern here???

    best wishes
    lynn

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    the pattern is the number is too big for the datasize - see https://docs.microsoft.com/en-us/off...long-data-type - maximum value is 2147483647

    if you have 64bit access (not windows) then you can use the longlong datatype, otherwise you will need to use double

  3. #3
    lmcm1977 is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2018
    Posts
    9
    Quote Originally Posted by Ajax View Post
    the pattern is the number is too big for the datasize - see https://docs.microsoft.com/en-us/off...long-data-type - maximum value is 2147483647

    if you have 64bit access (not windows) then you can use the longlong datatype, otherwise you will need to use double

    Hi Ajax

    thank you for your response. No I don't have 64bit access - what would you suggest I do - was there a part missing from your response after double??? I cant change the format to text as this is my primary and foreign key in my tables.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    was there a part missing from your response after double???
    No, but to add to it, other possible options are single and decimal. but all could suffer from approximation so personally I would not use them as PK or FK. I would use an autonumber (long) as your PK and have these fields as indexed, no duplicates. It is generally bad practice to use meaningful values as a PK. You'll find plenty of threads on this and other forums on the subject.

  5. #5
    lmcm1977 is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2018
    Posts
    9
    I have changed it to double and it seems to be working so far!!!!!!!!!!!!

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

Similar Threads

  1. Replies: 18
    Last Post: 01-12-2016, 11:53 AM
  2. Replies: 3
    Last Post: 07-21-2014, 09:33 AM
  3. VBA Format Append Leading Zeros
    By Jester0001 in forum Programming
    Replies: 10
    Last Post: 04-03-2012, 09:54 PM
  4. Importing data with leading zeros
    By buckychudd in forum Import/Export Data
    Replies: 7
    Last Post: 11-04-2011, 12:26 PM
  5. leading 'Zeros' in data
    By wasim_sono in forum Forms
    Replies: 3
    Last Post: 04-06-2009, 11:57 AM

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