Solved No sort possible

[mib1019]

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]

First, do you have any missing Air_Date?

 

[June7]

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]

use Expression on your grouping/sorting:

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

 

[The_Doc_Man]

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.

 

[Deleted member 147267]

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]

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.

 

[Deleted member 147267]

Well, I looked it up first at M$ before I posted that. Did you?

 

[mib1019]

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]

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.