Results 1 to 5 of 5
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Prefix transfer to Union Query


    We choose to use the same kind of numbering wherever numbering is used. We use three letters followed by six numbers i.e. "ABC"000000. Or in our Invoice purchase there may be a number INP002345. In the table where we setup the autonumber we enter "INP"000000 to return it as we want it. My invoices table has no problem when I create a query these numbers shows complete in the query. I then create a Union query. The problem I try to solve is the Union query does not display the full number. It shows the number without the prefix. I have a short cut fix but is there a easy way I am not aware of?

  2. #2
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Click image for larger version. 

Name:	111.png 
Views:	11 
Size:	86.7 KB 
ID:	34481Click image for larger version. 

Name:	222.png 
Views:	11 
Size:	99.3 KB 
ID:	34482

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    These are number fields with formatting? Formatting does not change the data and does not pass to UNION.

    What is your fix?

    I never do formatting in tables, only on forms and reports.
    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.

  4. #4
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thanks. Mostly I use the autonumber in the table as the numbers for documents etc. Tables does not allow input mask to be used if not text or date. I just watched two tutorials but haven't learn something yet. My fix is to concatenate. It works. We are just used to our numbers ABC123456. When I concatenate CmbEnt01: "ENT" & "" & [CmbEntID] it will not show the zeros if I concatenate "ENT" with "000762". It returns ENT762 which will be OK at the end but if I can get ENT000762 I would like to learn how.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    "ENT" & Format(CmbEntID, "000000")
    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.

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

Similar Threads

  1. Replies: 4
    Last Post: 08-09-2017, 12:06 PM
  2. Convert Union Query to non-union help
    By Ekhart in forum Queries
    Replies: 2
    Last Post: 01-10-2017, 03:39 AM
  3. Replies: 3
    Last Post: 11-26-2014, 02:36 PM
  4. use update query to add prefix to a field
    By djlabreche in forum Queries
    Replies: 3
    Last Post: 06-16-2014, 12:46 PM
  5. Add prefix to AutoNumber
    By sirmilt in forum Database Design
    Replies: 3
    Last Post: 07-09-2010, 01:41 PM

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