If statement not working when field was null and now isn't

[Rat1sully]

for some reason this if statement never returns true and builds the sql statement and i can't for the life of me work out why

background to this is importing a load of updated data using transferspeadsheet replacing current table which has been renamed

it then should fire up a load comparisons which all work apart from in the case of a description being added where there wasn't one before. neither of these statements casue an entry in that situation any ideas why?

        'if matching records are found check for same quantity fitted if not build sql as appropriate
        If tblA.Fields(field2) <> tblB.Fields(field2) Then
            strSQL = "INSERT INTO " & tblout & " (EquipmentTag, Change, Description) VALUES (" & Chr(34) & tblA.Fields(field1) & Chr(34) & ", 'Description changed', '" & tblB.Fields(field2) & "');"
        End If
        If tblA.Fields(field2) = "" And tblB.Fields(field2) <> "" Then
            strSQL = "INSERT INTO " & tblout & " (EquipmentTag, Change, Description) VALUES (" & Chr(34) & tblA.Fields(field1) & Chr(34) & ", 'Description added', '" & tblB.Fields(field2) & "');"
        End If

 

[jdraw]

Please post the full code, not just a snippet.

 

[Rat1sully]

solved it just used
isnull(tblA.Fields(field2)) = true
still don't really know why a null text field doesn't trip the = ""

honestly i don't think the rest of the code wouldn't have made a difference more if statments and loading the recordsets is all that's there

 

[jdraw]

You should investigate the difference between

Zero length string "" and NULL

see http://allenbrowne.com/casu-11.html

You can avoid zero length string in a field in a table by setting the property
Allow Zero Length String to No

 

[Rat1sully]

ah I see it's a dimensional issue null represenst a lack of data rather than blank data, fair enough you'd think I'd have picked up on that by now

 

[jdraw]

Not necessarily. In responding to you, I looked at a couple of sites. I did notice that one popular reference says a zero length string is a Blank which I think is absolutely false.
see https://599cd.com/tips/access/null-zls/

Another shows how to check for Null or zero length string (see)
http://blogs.office.com/b/microsoft...length-strings-a-power-tip-from-the-past.aspx

 

[Rat1sully]

I can see that null vs zls is potentially a big issue but I also see why I've only just encountered it as this if the first time I've looked for empty fields in this database.

From this i'm gathering that transferespreadsheet acImport moves blank cells over as nulls which is at least consistent