Solved code no longer working

[Eljefegeneo]

I have a query where one of the fields is [Date1]. In the query I have the following to sort [Day1] by the day of the week. That is, Sun first, Monday second, etc.

DaySort: InStr(1,"SuMoTuWeThFrSa",Left([Day1],2))

Was using Access 2010 now using 2019 and this is giving me an error. Never had a problem before. So, what am I doing wrong and how do I solve this so that I can list the items in the query sorted by the Weekday in calendar order?

 

[June7]

Is field Date1 or Day1?

Don't have an actual Date value field?

Not the first thread about failure of intrinsic functions after upgrade of Access and/or Windows.

 

[Eljefegeneo]

Date1 is a tree letter day of week abbreviation, i.e. Mon, Tue, etc.

 

[Eljefegeneo]

Sorry Three not tree. On my wife's laptop and my fingers don't fit it.

 

[Isaac]

My first though is, if you really do have a Date field, why can't you just sort by Weekday? Or is there something I'm missing.

 

[Eljefegeneo]

I want to sort by weekday. Sun first, Mon second, etc.

 

[June7]

Okay, but function uses Day1 field name. So I am still confused.

And is there not a full date value field?

Why would sorting by date not have desired result?

 

[Isaac]

If the column is Date1, then your column reference in the expression is wrong ?

Correcting for that, your basic approach does work for me (see attached sample).

 

[Eljefegeneo]

yes there is, but it is not Date1. Probably should just think of it as DayOfWeek. The actual date has no bearing on this query and might not be the actual current day of this month's week. There is a date field but it is the contract start date and it can be several years ago. I hope I am not confusing you. Date1 is a day of the week that a particular program is on the radio, i.e. Mondays @ 1 PM. So the Date1 field will show Mon.

 

[June7]

Then why did you even mention field Date1 to begin with?

As I said, not the first discussion about function failures after upgrade of Windows or Access. Usually means there is a missing reference library. Open VBE and check for missing reference.

 

[Isaac]

I hope I am not confusing you

You've definitely confused me at least.

All else being equal, a text column containing values like "Mon", "Tue", "Wed", "Fri", and an expression identical to the one you posted, correctly referring to that column, worked fine for me.

But you also haven't mentioned the error you're getting? How can we do anything but guess if you don't reveal WHAT the error is.

 

[Eljefegeneo]

Sorry fr all the confusion. The field is Day1 not Date1 and while your query will run on my computer using Access 2019, it looks like this:

Query1 Query1

Date1	DaySort
Sun	1​
Sun	1​
Mon	3​
Mon	3​
Tue	5​
Tue	5​
Wed	7​
Wed	7​
thu	9​
Thu	9​
Fri	11​
Sat	13​

 

[Isaac]

And how do you want it to look?
What error are you getting?

 

[Eljefegeneo]

I get #Error on mine.

 

[Eljefegeneo]

Also argument count mismatch

 

[Eljefegeneo]

my query which is throwing the error messages is essentially the same as yours.

SELECT tblSalesAndSchedules.Day1, InStr(1,"SuMoTuWeThFrSa",Left([Day1],2)) AS DaySort
FROM tblSalesAndSchedules
ORDER BY InStr(1,"SuMoTuWeThFrSa",Left([Day1],2));

 

[Isaac]

Maybe you can post copy of db.
You might have records which aren't fulfilling valid arguments in InStr and Left as June mentioned.

 

[Eljefegeneo]

I've thought Will take some doing to extract some data and post the DB, and I do appreciate your help.

 

[Eljefegeneo]

I will post my strip down DB as I want to know why this wouldn't work after years of working OK, but i got it to work using the following:

DaySort1: IIf([Day1]Like"Sun*",1,IIf([Day1]Like"Mon*",2,IIf([Day1]Like"Tue*",3,IIf([Day1]Like"Wed*",4,IIf([Day1]Like"Thu*",5,IIf([Day1]Like"Fri*",6,7))))))[\code]

 

[Eljefegeneo]

I was working on a laptop earlier that had Office 2019 and was getting the error messages. When I went back to my desktop computer with Offfice 2010 the original code works fine. So it isn't my data. Or is it?