"Data type mismatch in criteria expression." 2

[newyorkny]

Hi, guys:

Runnig the following query in A2k gets "Data type mismatch in criteria expression." I am unable to find anything very helpful in terms of documentation to suggest what's going wrong. The query cranks along for a few progress bars then throws the error:

SELECT tblXYZWorkLoad.fldInputClerk, tblXYZWorkLoad.fldDateRecieved, tblXYZWorkLoad.fldSSN, tblXYZWorkLoad.fldLastName, tblXYZWorkLoad.fldFirstName, tblXYZWorkLoad.fldTypeAction, tblXYZWorkLoad.fldEffectiveDate, tblXYZWorkLoad.fldDueDate, tblXYZWorkLoad.fldPendingAction, tblXYZWorkLoad.fldPendedDate, tblXYZWorkLoad.flrRemarks, tblXYZWorkLoad.fldCompletedDate, tblXYZWorkLoad.fldOutputClerk, tblXYZWorkLoad.fldPayGrade, Right$([fldssn],2) AS TD
FROM tblXYZWorkLoad
WHERE (((tblXYZWorkLoad.fldCompletedDate) Is Null));

 

[r937]

what datatype is fldssn?

rudy

http://r937.com/

SQL Consulting

 

[newyorkny]

Rudy:

It is "Text." Thank you.

NY

 

[r937]

okay, i'm out of ideas

rudy

http://r937.com/

SQL Consulting

 

[newyorkny]

Rudy:

But you made me think. I wonder if the whole query chokes if fldssn is blank?

I wish the error message were a little more specific. Hmmm.

Let me see if I can test my little theory.

Thanks. N

 

[PHV]

Try to replace this:
Right$([fldssn],2) AS TD
By this:
Right(Nz([fldssn],""),2) AS TD

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[newyorkny]

PHV,

You're a genius!!

It works. Now I'll have to figure out WHY it works so I can learn from your kind assistance!

Thanks,

NYNY

 

[lespaul]

Because you have a fldssn that is null. In your previous query when you tried to perform the RIGHT() function on a null value, it choked. PHV's solution says, If fldssn is null replace it with a blank string "", then take the right 2. That way the query doesn't choke since it knows what to do with the null field.

HTH

Leslie

 

[newyorkny]

Thanks, Les, "It's Been a Long, Long Time" (haha)

That's very helpful. Exactly what I needed to learn from this.

Give a man a fish and he eats today; teach him to fish etc.

Thanks again. NY

 

[lespaul]

glad you learned to fish today!!!

had to look up the Long Time reference (I guessed it was a Les Paul thing) to make sure. It's actually Leslie & Paul, no guitar reference intended!!

les