Trying to Count IIF a date formated as "dddd". (1 Viewer)

Mr. Southern · Oct 4, 2019

Hello,

In my query, I have a column with a date. This date is formatted as short date (10/4/2019). I then have a column that is taking that and formats it to "dddd" (Friday).

What I am trying to do now is perform a Count IIF for a specific date in a different query. I am trying to count the Mondays for each account(Monday: Count(IIf([Day]="Monday",1))).

I am receiving the error message "Data type mismatch in criteria expression. What am I missing?

theDBguy · Oct 4, 2019

Hi. You're missing the True part of your IIf() statement and you could also try using Sum() instead of Count().

Minty · Oct 4, 2019

I'm assuming [Day] is your calculated dayname field? If so you can't refer to it in the same query in another calculation. Use the weekday() function instead;

Code:

Monday: Sum(IIf(Weekday([YourDateField])=2,1,0)

Weekday starts with 1 as Sunday by default.

By the way [Day] is a reserved word I think, so not the best choice for a field name?

plog · Oct 4, 2019

Not to be a pedantic a-hole, but a data type mismatch means you are using the wrong type of data somewhere. A string where a date is expected, a number where a string is expected, a null where a null isn't expected, etc.

There are only a few spots where this can happen--in a comparison (on the left or right side of an equal sign) or in a function (it wants a string, you pass it a date). So isolate those areas and make sure your data is what you expect it to be. My guess is you have Null dates when you convert your dates to days of the week.

Mr. Southern · Oct 4, 2019

theDBguy said:
Hi. You're missing the True part of your IIf() statement and you could also try using Sum() instead of Count().

I had a brain fart and ended up using Day: Format([Delivery Date],"dddd"). Looks like it is working.

Mr. Southern · Oct 4, 2019

plog said:
Not to be a pedantic a-hole, but a data type mismatch means you are using the wrong type of data somewhere. A string where a date is expected, a number where a string is expected, a null where a null isn't expected, etc.

There are only a few spots where this can happen--in a comparison (on the left or right side of an equal sign) or in a function (it wants a string, you pass it a date). So isolate those areas and make sure your data is what you expect it to be. My guess is you have Null dates when you convert your dates to days of the week.

It looks like it wasn't formatting the short date only showing "dddd"

theDBguy · Oct 4, 2019

Mr. Southern said:
I had a brain fart and ended up using Day: Format([Delivery Date],"dddd"). Looks like it is working.

Hi. Glad to hear you got it sorted out. Good luck with your project.

Trying to Count IIF a date formated as "dddd". (1 Viewer)

Mr. Southern

Registered User.

theDBguy

I’m here to help

Minty

AWF VIP

plog

Banishment Pending

Mr. Southern

Registered User.

Mr. Southern

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread