'Wrong data type in criteria expression' Error in Where clause

[avenuw]

Hello

I am wondering if anyone has any ideas on what I am doing wrong. I am trying to select certian fields based on criteria that UCASE(last_name)=UCASE(last_name2), however I only have one field for name which includes first and last name. And name2 is another field with different names, also includes first and last names.So this is what I am trying to do:


SELECT name, position, status, (instr(name,',') )-1) AS [last name]
FROM rst_tbl
WHERE UCASE(left(name,( (instr(name,',') )-1) )) = UCASE( left([name2], ((instr([name2],',') )-1)) ) ;


I keep getting data type mismatch error!
Any suggestions would be greatly appreciated
Thanks,
Judy

 

[FoxProProgrammer]

Hi Judy,

Assuming the format of name and name2 are:

Last,first...

the SQL would be:

SELECT name, position, status, Left(name, InStr(1, name, ",") - 1) AS [last name]
FROM rst_tbl
WHERE ((Left(UCase(name), InStr(1, name, ",") - 1)) = (Left(UCase(name2), InStr(1, name2, ",") - 1)));


dz

 

[MajP]

in your select looks like you are missing a "left":
(instr(name,',') )-1) AS [last name]

This will return an integer which does not look like what you want, but I am not sure if this would cause the datatype error that you are getting.

 

[Remou]

By the way, Access is not case sensitive.

 

[avenuw]

Thanks for everyone's response but I tried FoxProProgrammer's suggestion and i am still getting the same error ("Data Type mismatch"). Its quite strange! I see where my mistakes were but still not sure why it doesnt like this where clause.

I tried it exactly as stated above:

SELECT name, position, status, Left(name, InStr(1, name, ",") - 1) AS [last name]
FROM rst_tbl
WHERE ((Left(UCase(name), InStr(1, name, ",") - 1)) = (Left(UCase(name2), InStr(1, name2, ",") - 1)));

Any ideas?
Thanks,
Judy

 

[PHV]

All records in rst_tbl have some value in name2 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[avenuw]

Thanks to FoxProProgrammer,MajP,Remou and PHV. You were all right. After fixing the code, I still kept getting the same error because as PHV hinted, name2 did not have values for some records. So there it is!

Thanks for your help