Recordset null value

[LEXCERM]

Hi all,

Before saving a record, I am testing 12 form fields for duplicates against all records in a table.

However, I have an issue whereby if the table field is null and the text field on the form is null, it doesn't recognise this as being equal. This is the line in question:-

rstDupe.Fields("FIELD1") = Me.txtFld1

So, if 11 of the 12 form fields are populated with data and they match the data in the table, it will not treat this as a duplicate record because of the null issue. If I take the above line out, the code traps the duplicate record.

Therefore, how can I tackle this null problem?

Many thanks and regards.

 

[gemma-the-husky]

it isn't equal.
null is special. you cannot test equality with nulls. you can even have a unique index that gets "duplicated" when the field value is null.

if you don't want nulls, you have to force them to put some value in, or just set a default.

 

[arnelgp]

rstDupe.Fields("FIELD1") & "" = Me.txtFld1 & ""

 

[LEXCERM]

Which means that I need to insert a bunch of IF statements to test both fields for null.

Thanks for the explanation gemma-the-husky.

 

[LEXCERM]

Thanks for replying arnelgp.

Have modified my code slightly as per your suggestion and it seems to work. Need to test further, but thanks for the suggestion. The speech marks could be the way forward:- "" = Me.txtFld1 & ""