i'm using a form to create a new record in a table. there are 3 text boxes to enter data into and depending on the info, 1 or 2 of the text boxes may be blank. so i'm using this null value code to enter my data to my table and then i string it into a SQL string:

        If IsNull(Me.TxtPlatNo) Or Me.TxtPlatNo = "" Then
            strPlatNo = ""
        Else
        strPlatNo = Me.TxtPlatNo
        End If

whats happening is, if TxtPlatNo is left empty the field in the table remains empty but when i use a query to search all the records in that field where "not is null" these blank entries are showin up in my query search even though the field is blank. can anyone explain to me why this is happening and most importantly how i can correct this???? i need to retrieve all the records in that field that contain data and exclude the rest of the records that dont.

Blank is *NOT* the same as NULL.

A blank string is still a string and is never considered to be a NULL.

You could do
     SELECT ... FROM ... WHERE IIF( field IS NULL, '', field ) <> '' ...
or, equivalently,
     SELECT ... FROM ... WHERE IIF( ISNULL(field), '', field ) <> '' ...

sorry but i need some clarification here.
1. are you saying i should use this SELECT.... FROM statement in my search query???

2. if the field is blank and that is not considered to be a null value cant i filter whatever value a blank field would be??

i'm trying to use your SELECT .... FROM statement but i cant seem to get it to work. i'm working with a text field and not a number so i'm guessing the <> wouldn't work with text.

can you shed a little more light on this. here's the SQL string i'm using on my form:

strSQL = "INSERT INTO T_Survey(WOID,RecordingNo,PlatNo,Qtr) " & " VALUES ('" & strWOID & "', '" & strRecordingNo & "', '" & strPlatNo & "', '" & strQtr & "')"

By inserting empty strings, as in: strPlatNo = "", the field is no longer Null. It is just an empty string.

In your subsequent searches, you can search for Null fields AND empty strings. That is what the old dude is suggesting. That would be AFTER you had already inserted an empty string.

If you want to prevent empty strings from being inserted, then you need to modify your code so that if the field was Null, then you didn't insert an empty string instead. You would have to modify your SQL INSERT string in order to do that. Like:

If all fields had values:
strSQL = "INSERT INTO T_Survey(WOID,RecordingNo,PlatNo,Qtr) " & " VALUES ('" & strWOID & "', '" & strRecordingNo & "', '" & strPlatNo & "', '" & strQtr & "')"

If PlatNo did not have a value:
strSQL = "INSERT INTO T_Survey(WOID,RecordingNo,Qtr) " & " VALUES ('" & strWOID & "', '" & strRecordingNo & "', '" & strQtr & "')"

Is that the sort of thing you want to do dynamically? Or do you just want to change the search parameters to include empty strings AND Nulls?




mmcdonal

Look it up at: http://wrox.books24x7.com

To answer you directly:

1. are you saying i should use this SELECT.... FROM statement in my search query???

Yes, of course.

2. if the field is blank and that is not considered to be a null value cant i filter whatever value a blank field would be??

My code will filter on *EITHER* a blank field *OR* a null value. It converts the nulls *TO* blanks. And then looks for fields that are not blank (or not-blank-after-the-null-is-converted-to-blank).

Yes, of course you can do
     <> ''
with text. You could *NOT* do that if the field was indeed numeric!!!

Just to put in my 2 cents:

I find the NZ() function to be an invaluable tool when dealing with nulls in either text or numeric fields.

-Phil-