Results 1 to 3 of 3
  1. #1
    Min Donner is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    2

    Consistent Character Length in Fields

    I am taking three fields (Time, Date, Number) and putting them together as a bar code (that I can do). When the bar code is scanned I want to split the data again. I assume using LEFT RIGHT MID would be the way to do that however, it seems you need to know exactly how many characters are in each field.



    How can I be sure that the fields are consistent length? For instance: Date of 4/2/19 needs to always be 04/02/19. Time of 7:05 needs to be 07:05, Number needs to be 002 or 128 (up to three characters). So the three fields together would be 04/02/1907:05002.

    Is there a better way to split the data out of the bar code? Without five pages of coding?

    Access 2016

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Date and Time are reserved words. Should not use reserved words as names for anything.

    Are Date and Time both date/time type fields? Why aren't these actually a single field with full date/time value?

    Use Format() function when concatenating the fields to assure consistent length.

    Format(Date + Time, "mm/dd/yyhh:nn") & Format(Number, "000")
    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
    Min Donner is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    2

    Thanks

    Quote Originally Posted by June7 View Post
    Date and Time are reserved words. Should not use reserved words as names for anything.

    Are Date and Time both date/time type fields? Why aren't these actually a single field with full date/time value?

    Use Format() function when concatenating the fields to assure consistent length.

    Format(Date + Time, "mm/dd/yyhh:nn") & Format(Number, "000")

    Yes, I know date and time are reserved, I was using them as an indicator of what type they were. Also, I need Date and Time to be separate for business process purposes.

    I will look at the Format function. Thanks for the direction. I've been on this for a few days now. Appreciate it.

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

Similar Threads

  1. Access not consistent
    By johnseito in forum Access
    Replies: 3
    Last Post: 08-11-2018, 05:51 AM
  2. Replies: 6
    Last Post: 01-13-2017, 08:54 AM
  3. Crosstab with multiple character fields
    By ecology in forum Queries
    Replies: 9
    Last Post: 07-30-2015, 11:04 AM
  4. Fixed character length of field
    By tylerg11 in forum Access
    Replies: 3
    Last Post: 09-29-2011, 11:58 AM
  5. Replies: 1
    Last Post: 03-09-2010, 10:23 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