Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Odd behavior (2003)

    I have two table with a field called GrpNum in each table. They are both data types of text. I put the tables in a query and join on GrpNum and do a one to one join and I don't get anything. I know that there are matches but can't figure out why I am not getting any results. I just thought that maybe someone would recognize the behavior and have some miracle solution.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Odd behavior (2003)

    Are you sure that the matches are exact? For example, if one of the values has a leading space and the other one doesn't, they look the same but are in fact different.

    If you wish, you can attach a small sample database. See <post#=401925>post 401925</post#> for instructions.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Odd behavior (2003)

    That could be the problem. One of the files was created in Cognos8 which I have had problems with spaces being added. Is there a way in Access to eliminate spaces in a field?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Odd behavior (2003)

    You could run an update query that trims the field:
    - Create a query based on the table.
    - Add the relevant field to the query grid.
    - Select Query | Update Query.
    - Enter the following in the Update to line, substituting the actual name of the field

    Trim([FieldName])

    - Select Query | Run or click the Run button on the toolbar.

    If this doesn't help, post back. There could also be non-breaking spaces.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Odd behavior (2003)

    Thanks Hans - that worked.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Odd behavior (2003)

    I am having the same problem but the update query didn't work. When I do a search on the field for the group number 04601608 and use whole field, it doesn't find the group number but when I put any part of field, it finds it.

    The problem is that I am trying to join on the Group number field and I am not getting any matches even though there are matches. Any help you can provide would be appreciated.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Odd behavior (2003)

    You could try another update query that removes non-breaking spaces:
    - Create a query based on the table.
    - Add the relevant field to the query grid.
    - Select Query | Update Query.
    - Enter the following in the Update to line, substituting the actual name of the field
    <code>
    Replace([FieldName], Chr(160), "")
    </code>
    - Select Query | Run or click the Run button on the toolbar.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Odd behavior (2003)

    Thank you so much - that was it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •