Data Type Mismatch in an IIf function

[Jon Farley]

Hello, and thanks in advance for any advice.

I can run the following IIf statements without issue independently , but when I combine them together I get a "Data Type Mismatch" error. Here are the separate statements:

Exp1: IIf([Tot_Hours]>=24,[Wgt_Time],"")
 
Exp2: IIf(([On_TG]<=[To_TG]) And ([On_TG]<=12 And [To_TG]>=12),[Wgt_Time],"")

Here's what they look like when combined:

CombExp: IIf([Tot_Hours]>=24,[Wgt_Time],IIf(([On_TG]<=[To_TG]) And ([On_TG]<=12 And [To_TG]>=12),[Wgt_Time],""))

Descriptions of the values in the expressions are as follows:
Tot_Hours is a DateDiff("h") on two dates, and formatted as a general number
On_TG and To_TG are both DatePart("h) of two different dates, and formatted as a general number
Wgt_Time: 1/([Tot_Hours]+1)

Again, appreciate the assistance.

[pod]

I have had similar problems sometimes, but they were due to some residual code I could not see in the Control Source field. Make sure you clean out that field completely, then start fresh, if you have a line break character in there it might buck.

I could not try your code but if your two seperate functions are working, it looks good.

I suggest start with the first one, make it work again, then add the second one but making the variables constant values:

IIf([Tot_Hours]>=24,[Wgt_Time],IIf((12<=12) And (12<=12 And 12>=12),[Wgt_Time],""),""))

if it works, change one variable at a time and assess from there

[NeoPa]

Jon, interesting question and well laid out. I've looked at your three expressions very carefully and I have to say I don't see any divergence or reason for the third one to fail. I would suggest that each of the comparison expressions is contained in parentheses (as below), but this is common between expressions two and three so I hold little hope it will resolve your issue.

CombExp: IIf([Tot_Hours]>=24,[Wgt_Time],IIf(([On_TG]<=[To_TG]) And ([On_TG]<=12) And ([To_TG]>=12),[Wgt_Time],""))

Generally, in situations like this, I find the answer lies in something different somewhere else that you don't comment on simply because you're totally unaware of what it is. This can only really be found by looking at the whole database.

[TheSmileyCoder]

Tot_Hours is a DateDiff("h") on two dates, and formatted as a general number
On_TG and To_TG are both DatePart("h) of two different dates, and formatted as a general number
Wgt_Time: 1/([Tot_Hours]+1)

Just to be clear, are they also STORED as a number, or merely formated as one?

[Jon Farley]

Good question re: stored vs. formatted. Probably just formatted.

Those values are derived from date fields, and I used the properties to format to general number.

How do I store them as numbers instead of only formatting them?

[TheSmileyCoder]

Well DateDiff returns a long, and DatePart returns a Integer, so unless you have since used a FORMAT function (which returns a string!) then I don't think that should give trouble.


Are the fields in question fields in a query or in a table? If in a table, what is the type of the field? Text, numeric, or date/Time?

[Jon Farley]

The fields are in the query, but are derived from fields in the same query that come from a ODBC connection to an oracle DB table and those are date/time.

[TheSmileyCoder]

Sorry, but Im am simply all out of ideas/suggestions.

[Jon Farley]

No problem, thanks for the brain power though. At least this is validating...since I was going crazy trying to correct it thinking that there was something obvious I wasn't looking at.

Good to know that at least it isn't a newb issue, as I'm relatively new to working in Access.

[TheSmileyCoder]

Can you test whether you get the error specifically when the second IIF is true (returning [Wgt_Time]) or when it is false (returning "")?

Do all rows have a non-Null value of [Wgt_Time]?

[Jon Farley]

There are no null values, but I did notice that some have negative values.

I figured it wouldn't be the issue though since it runs successfully when it isn't being combined with the first IIF expression.

[Jon Farley]

Just a quick follow up to close this out...

Instead of trying to edit my existing query, I started over with a new one and the expressions work.

[pod]

... residual code or character syndrome, I call it :)