Results 1 to 12 of 12
  1. #1
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86

    Concatenate with Tabs

    I would like to know if it is possible to concatenate two fields in a query and use a tab instead the normal space. It is possible? Thanks!



    [Code]& tab??? &[Description]

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    What happens when you try this?

  3. #3
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    I have not tried anything yet. I am searching for something to try? I don't want the word tab, I want a tabbed space so that my description column lines up evenly on the left. The code column is either 2 or 3 characters long, so if I just put spaces there, it does not line up. Thanks!

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If you are looking for putting the tab character between the words, a tab is Chr(9)
    Code:
    [MyCode]& Chr(9) & [MyDescription]
    Google "ASCII table" . In the table, look for Tab in the Char column...

  5. #5
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    No, it treats the Chr (9) like text it does nothing, my field get joined but not space or anything, it's like it just ignore the Chr (9). Any other suggestions.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I don't want the word tab, I want a tabbed space so that my description column lines up evenly on the left. The code column is either 2 or 3 characters long, so if I just put spaces there, it does not line up
    OK, let's back up a minute..
    You want to concatenate two fields so they line up evenly? Is this on a form, report, word doc, text file????

    Pretend you are in line at Burger King. I am behind you and I don't know anything about databases. Explain what you are trying to do.

  7. #7
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    Ok, I will try to explain better. I have this table with two fields or columns. I am creating a query for a drop down box on a form. I want the drop box to show the two fields together so when the users selects it from the list, it shows up on the form in one field. I am actually only storing the id to this record, but this is hidden from the user, they will see the actual name which has more meaning the them. Anyway, when I join the fields and you are looking at them in the drop box and after they are selected to show in the field, they look like this
    AAB Description
    AA Description
    CCC Description
    BB Description

    See how the description does not line up evenly. If I could use a tab there instead of the spaces, it would look much neater and easier to read.

    Like this
    AA Description
    BBB Description

    Hope this explanation helps.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Why can't you just show the 2 fields (no concatenation)
    Code:
    AA    Description
    BBB   Description
    They are text fields, so left justified in their own area.

    You want the rowsource of your combo to be something like

    Select id, field1, fieldDesc from Table....

    Then in the format have widths 0";1",1" which will hide the ID, and display field1 and fieldDesc ( as 2 columns)

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, I understand now. You can't use the tab character.. You will never get the columns to line up because the font is probably a proportional font.

    You would need to set the font for the combo box control to something like Courier.
    Then in the query, use

    Code:
    SELECT YourTable.id, Left([MyCode] & "     ",5) & [Description] AS Expr1 FROM YourTable;
    Formatting is:

    Bound Column 1
    Column Count 2
    Column widths 0"

    --------------------------------------
    Had to change "[Code]" to "[MyCode]". Can't use the word "code" in the post

  10. #10
    loantin868 Guest
    Windows 8 Access 2013
    upppppppppp ?ng h? ch? topic

  11. #11
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    Steve,
    Now I get a syntax error, it seems it does not like the comma. If I take the comma out it tells me I am missing an operator.
    So still not working. Thanks

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try this one (untested)

    You can make x whatever you want (I'm still not sure what the goal is.)

    SELECT YourTable.id, Trim([MyCode]) & Space(x) & Trim([Description]) AS Expr1 FROM YourTable;

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

Similar Threads

  1. Concatenate Fields
    By Njliven in forum Forms
    Replies: 9
    Last Post: 12-21-2012, 08:31 AM
  2. Replies: 5
    Last Post: 03-13-2012, 01:13 PM
  3. Tabs on Tabs?
    By NateHaze in forum Forms
    Replies: 4
    Last Post: 06-01-2011, 11:32 AM
  4. concatenate
    By stephenaa5 in forum Programming
    Replies: 2
    Last Post: 10-11-2010, 05:18 PM
  5. Concatenate Formula
    By Shingo in forum Programming
    Replies: 1
    Last Post: 07-25-2009, 09:14 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