M
MIG
I was being driven mad by getting data mismatch errors in queries, and
it looks like it may be down to inconsistent ways of dealing with
Nulls in Access.
I had been attempting to match postcodes while disregarding spaces.
As it happens
Left([postcode 1],3)
and
Replace([postcode 1]," ","")
are both valid expressions, even if [postcode 1] is Null.
the comparison
Left([postcode 1], 3) = Left([postcode 2], 3)
is also perfectly valid, even if either or both fields contain a Null.
But the comparison
Replace([postcode 1]," ","") = Replace([postcode 2]," ","")
causes a data mismatch error if either field contains a Null.
So why does Replace cause a problem with Null when Left doesn't? And
why is the error message so bluddy useless?
It seems that one can get round the error by using nz([postcode 1],"")
or whatever, but it wasn't obvious and wasted a couple of hours.
it looks like it may be down to inconsistent ways of dealing with
Nulls in Access.
I had been attempting to match postcodes while disregarding spaces.
As it happens
Left([postcode 1],3)
and
Replace([postcode 1]," ","")
are both valid expressions, even if [postcode 1] is Null.
the comparison
Left([postcode 1], 3) = Left([postcode 2], 3)
is also perfectly valid, even if either or both fields contain a Null.
But the comparison
Replace([postcode 1]," ","") = Replace([postcode 2]," ","")
causes a data mismatch error if either field contains a Null.
So why does Replace cause a problem with Null when Left doesn't? And
why is the error message so bluddy useless?
It seems that one can get round the error by using nz([postcode 1],"")
or whatever, but it wasn't obvious and wasted a couple of hours.