Solved No sort possible (1 Viewer)

mib1019

Member
Local time
Today, 05:34
Joined
Jun 19, 2020
Messages
88
I've got a query that contains a field Air_Dates, and a calculated field to find the Week Start Date of the Air_Date (week begins on Monday). The query field/calculation is:

Week: DateAdd("d",-Weekday([Air_Date],2)+1,[Air_Date]).

The query won't sort on that field, gives a data type mismatch error. The report I'm trying to develop groups on that calculated field Week and it also produces that error and won't run.

What can I do to reconcile this?

MIB1019
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:34
Joined
Oct 29, 2018
Messages
21,358
First, do you have any missing Air_Date?
 

June7

AWF VIP
Local time
Today, 03:34
Joined
Mar 9, 2014
Messages
5,425
If Air_Date is Null Weekday() returns Null, if Air_Date is empty string, Weekday() will return that error message. DateAdd will also return that error on empty string. DateAdd will return "invalid use of Null" on Null.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:34
Joined
May 7, 2009
Messages
19,175
use Expression on your grouping/sorting:

Expression: DateAdd("d",-Weekday([Air_Date],2)+1,[Air_Date] )
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:34
Joined
Feb 28, 2001
Messages
27,001
Because Access has both "Invalid use of Null" and "Type Mismatch" errors, and isn't known to use the wrong error for most cases, I'm inclined to believe that nulls aren't your problem. Further, nulls won't stop a sort, though they might result in unexpected ordering.

If you get a type mismatch then you have provided data of the wrong type in some context. This is a stab in the dark, but check the arguments for your functions against the descriptions of them.
 

Micron

AWF VIP
Local time
Today, 07:34
Joined
Oct 20, 2018
Messages
3,476
My money is on Nulls. While you can sort a field containing nulls, I doubt you can sort on null, which is what some functions will return without complaining, as June7 indicates.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:34
Joined
Feb 28, 2001
Messages
27,001
Micron, I don't disagree with you that having excessive nulls and trying to do the wrong things with them will invariably cause issues. However, the "type mismatch" issue is NOT among them. I think it is safe to say that at least TWO different issues are rearing their ugly heads here. You are calling out nulls. But if we are do to proper service, we should not discount that the explicit error message first named was a type mismatch.
 

Micron

AWF VIP
Local time
Today, 07:34
Joined
Oct 20, 2018
Messages
3,476
Well, I looked it up first at M$ before I posted that. Did you?
 

mib1019

Member
Local time
Today, 05:34
Joined
Jun 19, 2020
Messages
88
The table had a record with no Air_Date in it. When I deleted the line, the formula worked correctly.

Thanks for the guidance!
MIB1019
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:34
Joined
Oct 29, 2018
Messages
21,358
The table had a record with no Air_Date in it. When I deleted the line, the formula worked correctly.

Thanks for the guidance!
MIB1019
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom