Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    text value contain # sign (2002 SP1)

    Comparing 2 tables based upon a code match and not like condition on text value (description)

    Table A contains : Table B contains :
    Code Code
    Description Description

    Where codes match and description from A is not like description from B

    The problem is where the descriptions are the same but both contain a # sign, I get a record indicating that they are different.

    How, Why, most importantly, How do I fix it?

    Thanks,
    Kathy Nielsen
    DHTS-PRMO IT Div
    niels009@mc.duke.edu

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

    Re: text value contain # sign (2002 SP1)

    Do you really need Like in the criteria? When you use Like, the # character acts as a wildcard that stands for any numeric digit 0 ... 9.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: text value contain # sign (2002 SP1)

    You don't tell us how the Code and Description fields are defined in the table - that may have some bearing on the problem. If the Description fields are text fields (limited to 255 characters) then I am puzzled. If they are memo fields, which can be quite large, then I would be less surprised. The # symbol does have a special meaning when it comes to defining date fields, and it is also a wildcard character for matching a single numeric character, but neither of those should show up in a mismatch arrangement. Are you using a SQL expression to compare the records? If so, it would be useful to see the SQL expression. If you are doing it in code, then it would also be useful to see the code.
    Wendell

  4. #4
    New Lounger
    Join Date
    May 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: text value contain # sign (2002 SP1)

    Table A, is QCCDM All Fields and contains the following defined fields Short Code an 8 position text field and Tech Description a 30 position text field.
    Tabe B, is SDBASIC and contains the following defined feilds Short Code an 8 position text field and Technical Desc a 30 position text field.
    The query is designed where Short Codes match and [SDBASIC].[Tech Description] is not like [QCCDM All Fields].[Technical Desc]
    I originally tried using <>[QCCDM All Fields]![Tech Description] but that returned even more invalid lines.
    I just tried using NOT instead of NOT LIKE but it still returns the lines where a # sign occurs...
    I guess what I really need is a NOT EQUAL but I've no idea how to do that.
    I'm trying to do this simply in Query Design. I don't know any other place.

    Basically the purpose behind this is I am building a file of all values in SDBASIC in a second system and I need to modify it whenever any are changed.

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

    Re: text value contain # sign (2002 SP1)

    < > seems to work fine for me, even for values with # in them. Could you post a stripped-down version of your database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]This will enable us to see what's happening.

  6. #6
    New Lounger
    Join Date
    May 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: text value contain # sign (2002 SP1)

    Thanks. I'm just so baffled.

    Kathy
    Attached Files Attached Files

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

    Re: text value contain # sign (2002 SP1)

    You have 17 records in each table. The description fields in both tables seem to contain exactly the same values when inspected visually.

    If I change the criteria for Technical Desc in SDBASIC to [QCCDM All Fields].[Tech Desc], the query returns 17 records, confirming that the descriptions are equal.
    If I change the criteria to <>[QCCDM All Fields].[Tech Desc], the query returns no records at all, which is to be expected, since there are no records that match on Short Cd with differing descriptions.

    So I'm baffled too. I have attached the database, with the modified query. Does it return records when you open it?
    Attached Files Attached Files

  8. #8
    New Lounger
    Join Date
    May 2002
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: text value contain # sign (2002 SP1)

    No, it returns no records.
    It was returning lots but it's been a couple months since I changed from <> because it was returning too many invalid records. I guess I'll go back to it.

    Thanks for all your help. =)

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: text value contain # sign (2002 SP1)

    I looked at downloaded db & got same results reported by HansV when using equal (implied =) or not equal (<>) to compare Tech Description fields, while Like & Not Like produced apparently bogus results. Modified query with this SQL:

    SELECT [QCCDM All Fields].[Svc Cd], SDBASIC.[Technical Desc], [QCCDM All Fields].[Tech Desc], StrComp([QCCDM All Fields].[Tech Desc],[SDBASIC].[Technical Desc],0) AS StringCompBin, StrComp([QCCDM All Fields].[Tech Desc],[SDBASIC].[Technical Desc],1) AS StringCompText, [Tech Desc] Like [Technical Desc] AS [LIKE], [Tech Desc] Not Like [Technical Desc] AS [NOT LIKE]
    FROM SDBASIC INNER JOIN [QCCDM All Fields] ON SDBASIC.[Short Cd] = [QCCDM All Fields].[Short Cd]
    ORDER BY [Tech Desc] Like [Technical Desc];

    When ran query, the LIKE calculated expression returned -1 (True) for all records w/o the "#", while NOT LIKE returned True for all those with the "#" (7 and 10 records, respectively). The two calculated expressions using StrComp function to perform a binary and textual comparison returned 0 for all records, meaning the two strings being compared are equal. So the conclusion would seem to be, do not use LIKE or NOT LIKE for direct comparison, use these only when wild cards are involved.

    HTH

Posting Permissions

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