Strange behavior in query with LEN Function

[boblarson]

Access 2003 SP3, Windows XP

I have a sub report which is returning two different fields depending on if a checkbox is checked. I needed to exlcude any records where both of the notes were blank and so I came up with a field to return BothNull if both had no data. If the checkbox is checked it is supposed to check one set of two fields and if it is not checked it is supposed to check the other. The result that I came up with was confusing a bit, because when I tried using Len([Field]) = 0 it wouldn't recognize it for the two fields which come from a linked SQL Server table, but it worked fine on the Access linked table. So, by using Len([Field]) < 1 it actually works. I'm trying to figure out how the length of a field can be greater than 0 but have no data, including spaces.

Anyway, this is what I wound up with:

BothNull: IIf(([Forms]![frmAdmin]![chkUseBarkleyNotes]=0) And (Len([ProfileNote] & "")=0 And Len([WatchlistNote] & "")=0) Or ([Forms]![frmAdmin]![chkUseBarkleyNotes]<>0) And (Len([BarkleyQtrNotes])<1 And Len([BarkleyWLCorrect])<1),"Both","")

 

[DCrake]

BothNull: IIf(([Forms]![frmAdmin]![chkUseBarkleyNotes]=0) And (Len(Trim([ProfileNote]) & "")=0 And Len(Trim([WatchlistNote]) & "")=0) Or ([Forms]![frmAdmin]![chkUseBarkleyNotes]<>0) And (Len(Trim([BarkleyQtrNotes]))<1 And Len(Trim([BarkleyWLCorrect]))<1),"Both","")

Have you tried wrapping Trim() around the field inside the Len() commands?

 

[boblarson]

BothNull: IIf(([Forms]![frmAdmin]![chkUseBarkleyNotes]=0) And (Len(Trim([ProfileNote]) & "")=0 And Len(Trim([WatchlistNote]) & "")=0) Or ([Forms]![frmAdmin]![chkUseBarkleyNotes]<>0) And (Len(Trim([BarkleyQtrNotes]))<1 And Len(Trim([BarkleyWLCorrect]))<1),"Both","")

Have you tried wrapping Trim() around the field inside the Len() commands?

No, I hadn't tried that. I'll see about trying that. Maybe somehow there is a padded space or spaces there.

 

[vbaInet]

I'm sure you will sort it out, but you forgot to include & "" on the other Len() comparisons. This is how I would write it:

BothNull: IIf(([Forms]![frmAdmin]![chkUseBarkleyNotes] = 0 And Len(trim$([ProfileNote] & [WatchlistNote] & "")) = 0) Or ([Forms]![frmAdmin]![chkUseBarkleyNotes] <> 0 And Len(Trim$([BarkleyQtrNotes] & [BarkleyWLCorrect] & "")) = 0),"Both","")

Just because Len() can return Null.