Data Type Mismatch in Criteria Expression

[MayaMana]

I am working on a database that someone else created and the following expression is being used in a query:

TPT1Date: IIf(Mid([RECV_TPT1],3,1)="-",DateValue([RECV_TPT1]),IIf([RECV_TPT1]="null",Null,IIf(Mid([RECV_TPT1],5,1)="-",DateValue(Mid([RECV_TPT1],6,2) & "/" & Mid([RECV_TPT1],9,2) & "/" & Mid([RECV_TPT1],1,4)),[RECV_TPT1])))

When I try to run the query I get an error message that:
"Data type mismatch in criteria expression."

 

[gemma-the-husky]

try putting your expression in a function, and see what happens.

what data type IS tpt1_recv.

I suspect the iif() is not working correctly for the particular value(s) you have.

Can we have an example of the type of values you have?

 

[plog]

It has to do with a function call, and most likely from bad data. You have so many, function calls in that thing its going to be hard to debug.

What I would do is make a copy of the query, bring down RECV_TPT1 and gut TPT1Date to this:

TPT1Date: IIf(Mid([RECV_TPT1],3,1)="-", True, False)

It should run fine. However, order by RECV_TPT1 date (both ascending and descending) and look at whats pops to the top. Then make sure that the TPT1Date value is correct for that RECV_TPT1 value.

If nothing jumps out, keep readdingg code to TPT1Date, one function call at the time until you find the offending function call.

However, I'd make a function out of this in a module. That code is so dense, put it in a function and use however much space you need. This has the added benefit off being able to trap the errors easier.

 

[MayaMana]

RECV_TPT1 is set as a text field at the moment.
The data in it is a date format of DD/MON/YY.

plog - I am going to try that now and see what shows up, thank you. I am rusty at this and am trying to help out someone else with a database they inherited.
update: they all seem to work on their own. including the first two IIF's together and the last 2 IIF's. Just not when all 3 IIF's are ran, or the first and the last IIF's are ran together. When the second IIF statement is moved to the end if says "The expression you entered has a function containing the wrong number of arguments."