Results 1 to 5 of 5
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Comparing strings

    Very puzzling. I was in the midst of debugging some new code in an AfterUpdate Event when I notice strange behavior of a statement comparing two strings. It might just be because they are strings rather than variant. Thought I'd post anyway. I verified the contents running with the MsbBox displays going from one of the fields from text content to null and visa-versa. The comparison statement DOES NOT evaluate to true when one of the string values is Null. ("FldName") is the name of the variable to which the text box is bound and the OnAfterUpdate Event is coded "=ChgData("table name")", "ChgData" is the function being degugged. I.e., Public Function ChgData(FldName as string)

    Code:
    MsgBox "New: " & Me("tb" & FldName) & "What is it? " & IsNull(Me("tb" & FldName))
    
    MsgBox "Old: " & Me("tb" & FldName).OldValue & "What is it? " & IsNull(Me("tb" & FldName).OldValue)
    
        If Me("tb" & FldName) <> Me("tb" & FldName).OldValue Then
    Doesn't work properly even if I change the function to: Public Function ChgData(FldName As Variant)

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Null is not equal to anything, not even another null

    Try typing these into the immediate window and you get the output
    As you would expect
    ?1=1 gives True
    ?0=0 gives True
    ?1=0 gives False
    BUT
    ?1=Null gives Null
    ?Null = Null gives Null

    Suggest you try using the Nz function
    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

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    You mean like this?

    Code:
    If Nz(Me("tb" & FldName)) <> Nz(Me("tb" & FldName).OldValue) Then
    I guess so as it works like a charm.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    An alternative is to just concatenate empty string to field.

    If Me("tb" & FldName) & "" <> Me("tb" & FldName).OldValue & "" Then
    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.

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Never thought of that. Funny too because I often make use of that very idea in examining strings from unknown parts using something like

    If len(SomeString & "") > 0 if "SomeString" turned out to be null raising an error.

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

Similar Threads

  1. Replies: 4
    Last Post: 03-16-2017, 12:19 PM
  2. strings coloured
    By snoopy in forum Programming
    Replies: 2
    Last Post: 10-17-2012, 01:46 AM
  3. Comparing Strings
    By Paul H in forum Programming
    Replies: 1
    Last Post: 01-27-2012, 01:46 PM
  4. Cocatenation of strings
    By swetha in forum Access
    Replies: 1
    Last Post: 09-21-2011, 08:31 AM
  5. Comparing Two strings issue
    By ganeshvenkatram in forum Access
    Replies: 2
    Last Post: 06-16-2011, 05:27 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