Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2022
    Posts
    11

    Zeros in Access Field

    Hi All,



    I have a 'Last four social' field and it displays the leading zeros in the table, and on the form, but I have an import button to take the data from the 'Last four social' field and insert it into another field in another form. When I use that, it will not import the zeros.

    I am sure this is fairly simple as to why, but any help would be appreciated.

    Thank you!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Are fields text or number type? Text type can retain leading zeros, number cannot. However, can format number field to display with leading zeros.

    Why are you duplicating data between tables?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Nov 2022
    Posts
    11
    I assume that is most likely the issue. The one field is formatted as a number and the other short text. I assume there is no real workaround to this? I've even tried messing with the 'format' property of the textbox I am importing to but it still will not display the leading zeros.

    I do an import because I am creating a brand new record in a separate table.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Format property can simply be set to 0000.

    Still doesn't explain why data is duplicated. Why not just save record ID?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Wasser2021 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jun 2021
    Posts
    10
    Hello, friends of VBA Access programming, VBA isn't exactly known to be user-friendly, sometimes you simply fail with NUL or Empty maybe someone can give me a tip here? How to deal with this? I want to recalculate the Zz field but I can't because the field is null or empty I first defined it as a number field or in the coding as an integer or later as a variant for testing purposes My problem is, I have deleted records in the table but the "Sentence" field is an autonnum field. Now the sequence is no longer correct I read somewhere that you can tell Access to accept Num odde Empty.??? Basically, I just want to regenerate the sentences to close gaps.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Use the NZ() function if you want to handle Nulls.

    Don't jump in and hijack someone else's thread. Start your own with the respective problem.

    And no, VBA is user friendly, so just start learning it, like everyone else has to.

    You wouldn't expect to speak Chinese at the drop of a hat, same goes for any computer language. Called a language for a reason.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    My problem is, I have deleted records in the table but the "Sentence" field is an autonnum field. Now the sequence is no longer correct
    That's expected behavior. What's the relevance of the field? Is it a primary key? 99.9% of the time it is and should be irrelevant other than uniquely identifying a record.
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. Replies: 3
    Last Post: 07-08-2015, 07:18 PM
  2. zeros in front of field are dropped
    By migaccess in forum Access
    Replies: 3
    Last Post: 06-19-2015, 02:53 PM
  3. Issues with leading zeros in a form field
    By apetriella in forum Forms
    Replies: 2
    Last Post: 02-10-2015, 11:27 AM
  4. Replies: 2
    Last Post: 01-26-2015, 03:53 AM
  5. Pad Text Field With Zeros in VBA
    By jhrBanker in forum Programming
    Replies: 9
    Last Post: 11-17-2013, 11:35 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