Type Mismatch....WHY?!!?

**jgelpi16** · 03-04-2011, 01:31 PM

I have the below piece of code as criteria in a query. For some reason I keep getting a type mismatch error! I checked the table and the field is formatted to Date/Time. Can someone please lay a fresh set of eyes on this? Thank you.

Code:

Between CDate(IIf(DatePart("m",Date())=1,CDate("12/31/" & DatePart("yyyy",Date())-1),CDate(DatePart("m",Date())-1 & "/1/" & DatePart("yyyy",Date())))) And CDate(IIf(DatePart("m",Date())=1,CDate("12/31/" & DatePart("yyyy",Date())-1),CDate(DatePart("m",Date())-1 & "/31/" & DatePart("yyyy",Date()))))

**ajetrumpet** · 03-04-2011, 01:36 PM

first. sorted out....

Originally Posted by jgelpi16

Code:

Between CDate(IIf(DatePart("m",Date())=1,CDate("12/31/" & 

DatePart("yyyy",Date())-1),CDate(DatePart("m",Date())-1 & "/1/" & 

DatePart("yyyy",Date())))) And Date(IIf(DatePart("m",Date())=1,

CDate("12/31/" & DatePart("yyyy",Date())-1),CDate(DatePart("m",Date())-1 & 

"/31/" & DatePart("yyyy",Date()))))

maybe you need to wrap it in pound signs? like:

Code:

between "#" & IIF() statement & "#" AND 

"#" & IIF() statement & "#"

or maybe take the cdate() functions out of it? believe or not, that might work!

**orange** · 03-04-2011, 01:36 PM

Can you tell us in English what the criteria is meant to do?

**jgelpi16** · 03-04-2011, 01:38 PM

The criteria needs to filter for last month's date. If this month is January I have to back up to the previous year as well. Hence the portion with "12/1/" & Datepart("yyyy", Date()-1.

**jgelpi16** · 03-04-2011, 01:43 PM

maybe you need to wrap it in pound signs? like:
Code:
between "#" & IIF() statement & "#" AND

"#" & IIF() statement & "#"
or maybe take the cdate() functions out of it? believe or not, that might work!

Tried both methods....no luck.

**ajetrumpet** · 03-04-2011, 01:55 PM

maybe the problem is the fact that you can't have IIF() statements in criteria statements?

now that I think about it, I think I read that somewhere. Either that, or Paul Baldy might have mentioned it once. I would seriously think about taking those out and testing it. I'm kind of thinking that's it. Either that, or IIF() statements can't be in BETWEEN statements...

**jgelpi16** · 03-04-2011, 02:04 PM

I discovered the issue...I know the statement works because it worked last month. I was under the incorrect impression that if you just every month the 31st it would accept that as the end of the month. I was wrong. If I can the "/31/" to "/28/" (since last month only had 28 days) the code works fine. I'll have to go about another method to find the end of the month.

**jgelpi16** · 03-04-2011, 02:17 PM

And the solution....

Code:

Between CDate(IIf(DatePart("m",Date())=1,CDate("12/1/" & DatePart("yyyy",Date())-1),CDate(DatePart("m",Date())-1 & "/1/" & DatePart("yyyy",Date())))) And CDate(IIf(DatePart("m",Date())=1,CDate("12/31/" & DatePart("yyyy",Date())-1),cdate(DateSerial(Year(Date()), Month(Date()), 0)))))

**ajetrumpet** · 03-04-2011, 02:49 PM

Originally Posted by jgelpi16

I discovered the issue...I know the statement works because it worked last month. I was under the incorrect impression that if you just every month the 31st it would accept that as the end of the month. I was wrong. If I can the "/31/" to "/28/" (since last month only had 28 days) the code works fine. I'll have to go about another method to find the end of the month.

I'm laughing at this, simply because I could've probably given you this exact same answer if I would've analyzed your post completely. But I didn't, so hence you found it on your own.