confusing datatype error issue

[TheShabz]

Hi all.


I am getting a datatype mismatch error in my criteria section yet cant find out why. everything is a text datatype. What am I missing? Some fresh eyes may catch what I've missed.

WHERE
tblIIDorig.[Last name] = Right([tblIIDwork].[*Customer Name],Len([tblIIDorig].[Last name]))
AND
tblIIDorig.[First name] = Left([tblIIDwork].[*Customer Name],Len([tblIIDorig].[First name]))

and yes I know the field names are horrible, not much I can do about it. Thanks.

[pbaldy]

Are there any Null values in either field?

[TheShabz]

i believe so. however, dont nulls have a len(0)? would that be a problem?

[pbaldy]

I think it will return Null, which is why a common test is

Len(Value & "")

which will make it return 0. I'd probably wrap each of them in the Nz() function.

[TheShabz]

Nz around the Len worked well. ty sir.

[pbaldy]

No problemo.