469,286 Members | 2,522 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,286 developers. It's quick & easy.

Data Type Mismatch in an IIf function

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:

Expand|Select|Wrap|Line Numbers
  1. Exp1: IIf([Tot_Hours]>=24,[Wgt_Time],"")
  2.  
  3. Exp2: IIf(([On_TG]<=[To_TG]) And ([On_TG]<=12 And [To_TG]>=12),[Wgt_Time],"")
Here's what they look like when combined:

Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 28 '11 #1
12 6759
pod
298 100+
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Apr 28 '11 #2
NeoPa
32,173 Expert Mod 16PB
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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 29 '11 #3
TheSmileyCoder
2,321 Expert Mod 2GB
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?
Apr 29 '11 #4
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?
Apr 29 '11 #5
TheSmileyCoder
2,321 Expert Mod 2GB
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?
Apr 29 '11 #6
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.
Apr 29 '11 #7
TheSmileyCoder
2,321 Expert Mod 2GB
Sorry, but Im am simply all out of ideas/suggestions.
Apr 29 '11 #8
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.
Apr 29 '11 #9
TheSmileyCoder
2,321 Expert Mod 2GB
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]?
Apr 29 '11 #10
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.
Apr 29 '11 #11
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.
May 4 '11 #12
pod
298 100+
... residual code or character syndrome, I call it :)
May 4 '11 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Martin Lacoste | last post: by
4 posts views Thread by N J | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.