Results 1 to 12 of 12
  1. #1
    bbxrider is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2010
    Posts
    69

    same text is not equal

    an access table is linked to a sql server table. the table is populated from excel content imported to make a local table. after the local table is created (via import) vba code runs adds changes and deletes to the linked table from the imported table.
    there are 4 long text fields in the linked table, those corresponding fields in the imported table default to short text.
    when running an update, I check if the update table text is not equal to the linked table text and only update if they are different. however, I test for not equal, ie if update<> linked-table, then do update..... however it always returns not equal
    I also tried strComp(update, linked-table, vb....) I tried all the compare options and always returns not equal
    I also manually went into the update table and changed the 4 fields to long text from short text to match the linked table, and that did not make a difference, still always not equal
    I also created the linked table recs originally from the same table that is now used (a second time) for the update. so the content in these fields HAS TO BE THE SAME... RIGHT?
    the content is similar to this, folks entering comments etc so there is cr and linefeeds
    "ANALYST for NOTE 34602 & 37722. HELPER INVOICE BILLED Erroneously. RAD 9944, 0076 (EC 3626)."

    any ideas why the not equal?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If I remember correctly, long text cannot be used in a query, or compares.
    this may have changed.

  3. #3
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    this may have changed.
    I think it has as I just tested 2 memo fields in a query with a compare which seemed to work.

    What are the lengths of the text? A short text field is only 255. Could the text be getting truncated?
    Any non printable characters? Rich Text formatting?

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Prior to version 2003 or so, there were concerns about memo fields AKA long text causing issues in queries.
    However those issues have largely been resolved many years ago and i can confirm what Moke wrote above.
    Searching memo fields may be relatively slow but it works as do comparisons.
    The only real issue is truncation of memo fields if your query has certain features. See http://allenbrowne.com/ser-63.html
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    bbxrider is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2010
    Posts
    69
    thanks for replies, good ideas, there could be non printables, right now don't have a hex editor at work and cannot get one here, so I will do that at home later, will try moving each field to a local string variables and use that for test, I never tried to specify the field type in the import, so I wonder if access would automatically create a long text if the .xls field was > 255

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No it wouldn't do so automatically.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    This may help to check for non-printable characters.
    Code:
    Public Sub sChkNonPrint(strCheck)
        Dim lngLoop As Long
    
    
        For lngLoop = 1 To Len(strCheck)
            Debug.Print MID(strCheck, lngLoop, 1) & ": " & Asc(MID(strCheck, lngLoop, 1))
        Next lngLoop
    
    
    End Sub

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just a comment --not a solution.
    When you have fields that are "free form", manually input via user, you'll get spelling, typo, punctuation, spacing....variations. Even "spaces" could be "Tabs", multiple spaces etc.

    Long text/memo fields are used by some to store "quasi-records"--issues, comments, notes, conditions etc. This often masks the real purpose of such fields. In many cases it is preferred to use a table(s) to identify consistently these "records".
    Often a standard, authoritative reference table using a code and a description/definition will remove the "ad hoc" input and use a selection(s) from a drop down list. This removes the spelling, grammar, .... issues and provides consistency.

    I don't know the details of how your initial data is captured, vetted, ... or what the subsequent use of the data is, but just offering a few comments for consideration.

    Good luck with your project.

  9. #9
    bbxrider is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2010
    Posts
    69
    thanks again for the ongoing support, I agree if a text type comparison is to be performed, it would be much better to have some control over that content but don't have that here and not possible to change the input. however, the input is a weekly extract, and if a record or that particular field has not been changed at all from the previous week, it should just be the same as the current db content??

    I ran the decimal converter and didn't get any non printables but found something else

    from a debug.print direct of the db field ie. debug.print ![fieldname] and also variable that I moved the db content into, ie, strVar=![fieldname], I get the same result for both,
    I get different characters printed than what I get if I just copy and paste the field from the table in datasheet view or print the characters from the the update rec.

    direct copy and paste from db (quotes are mine) and debug.print output from the field in the update rec, both the same and note when the rec was originally created it would have had the same content as here
    " County: Opened by: smithfield on 16 October 2018 13:40:42"


    output from debug.print
    " County: Opened by: smithfield on 16 October 2018 13:40:42"

    all the added characters &, #, 5, 8 etc, have valid decimal values, but note the original colons are gone, and by coincidence? 58 is the decimal for ":"(colon)
    so it seems in vba, the colon in the db rec, is changed to or recognized as ":" and in a literal character by character compare those characters of course are not equal to ":"

  10. #10
    bbxrider is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2010
    Posts
    69
    oops correction, sorry about the confusion, the output from debug.print is:
    " County: Opened by: smithfield on 16 October 2018 13:40:42"

    so same comments as original post:
    all the added characters &, #, 5, 8 etc, have valid decimal values, but note the original colons are gone, and by coincidence? 58 is the decimal for ":"(colon)
    so it seems in vba, the colon in the db rec, is changed to or recognized as ":" and in a literal character by character compare those characters of course are not equal to ":"


  11. #11
    bbxrider is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2010
    Posts
    69
    ouch, 2nd correction, I see now, when I copy and paste the debugger output here in the post, the apparently changed ":" does not paste out the same as it is in the debugger. but it does paste out in notepad see image
    Click image for larger version. 

Name:	colon.from.debug.print.PNG 
Views:	11 
Size:	2.2 KB 
ID:	36462

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just for future reference, use the code tags and your text should copy exactly as is to the reply window including colons (or semicolons) and all other special characters
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 7
    Last Post: 04-20-2018, 03:29 PM
  2. having trouble making a text box equal to a query result
    By NightWalker in forum Programming
    Replies: 16
    Last Post: 02-14-2017, 05:33 PM
  3. Zero doesn't equal Zero
    By DOSRoss in forum Access
    Replies: 8
    Last Post: 04-27-2015, 11:00 AM
  4. Replies: 1
    Last Post: 11-28-2014, 02:42 PM
  5. Not equal
    By slimjen in forum Queries
    Replies: 3
    Last Post: 04-21-2014, 12:44 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