Sort Day of Week field Sun-Sat

[vapid2323]

OK, I really did search for this but I only go information on how to sort a Date (1/1/1900) to Sun-Mon.

What I need is a text field that allreay has the day of week sorted.

Below is what i have now:

Site
Monday
Sunday
Saturday
Tuesday
Wednesday
Friday
Thursday

This is what I want it to look like on the report

Site
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday

Any Ideas?

 

[vbaInet]

Sort by Weekday([Field])

 

[vapid2323]

Sort by Weekday([Field])

Works but I have a problem,

I need to Group On "DayOfWeek" and also sort Sun-Sat I have set it up a few differant ways but I always get an error.

ORDER BY clause (Weekday([dayOfWeek]) conflicts with GROUP BY clause

 

[vbaInet]

Perform the Grouping and Sorting in your report.

Just create an alias field that will return the Weekday like this:

AliasField: Weekday([dayofweek])

 

[vapid2323]

Perform the Grouping and Sorting in your report.

Just create an alias field that will return the Weekday like this:

AliasField: Weekday([dayofweek])

Ok, I am really trying to get this

I try to add DOWSORT: Weekday([dayofweek]) into the qry and I get #Error in all the cells.

I have no idea how to add that into a report if thats what you ment for me to do.

Lastly I just want to make sure that I understand this Function:

If a Cell in Field [dayOfWeek] = Sunday the Function will return a 1?

So then my report can sort small to large with no issue.

 

[vbaInet]

Correct, sunday is 1 and saturday is 7.

Did you remove the Group By in query level? You can remove it by right clicking and uncheking TOTAL

 

[vapid2323]

Correct, sunday is 1 and saturday is 7.

Did you remove the Group By in query level? You can remove it by right clicking and uncheking TOTAL

Sure did

Dont know if it will help but this is my SQL I might have made a mistake?

SELECT tblBedSlot.aphCenter, tblBedSlot.beginTime, tblBedSlot.endTime, tblBedSlot.dayOfWeek, Weekday([dayOfWeek]) AS DoWSort, tblBedSlot.clinical, tblAphCenter.chairsQualifiedByQA, tblAphCenter.chairsQualifiedByANM
FROM tblAphCenter INNER JOIN tblBedSlot ON tblAphCenter.centerName = tblBedSlot.aphCenter;

 

[vbaInet]

Does it show the error in the report or in the query? Try both.

 

[vapid2323]

Does it show the error in the report or in the query? Try both.

I get #Error in all cells when looking at the query and also the report if I add in the field.

 

[vbaInet]

Create a new query from scratch (obviously using the wizard). I think something has gone wrong with that query.

 

[vapid2323]

Just did that, same thing.

Is it possible that my Weekdays would be formatted incorrectly?

Jeez I thought this was going to be simple lol.

 

[vbaInet]

Let me see your db.

 

[vapid2323]

Let me see your db.

Everything in in SQL so thats hard, I can give you a export of the needed tables and report.

Will be back in about an hour

 

[vbaInet]

I thought you said all the fields were throwing an error :O lol. Ok, I also forgot that that function requires a Date not just a day string. See attached.

 

[vapid2323]

I thought you said all the fields were throwing an error :O lol. Ok, I also forgot that that function requires a Date not just a day string. See attached.

Hah, sorry about that I guess when I said all cells I meant all cells in that field

Sorry about that

But thank you for getting this to work!!

 

[namliam]

I would probably have created a related table to sort on:
1 Sunday
2 Monday
3 Tuesday
Etc...

Simple join, done... However somehow I doubt that the source of this would only contain "Monday" instead of coping with real dates

 

[vbaInet]

I would probably have created a related table to sort on:
1 Sunday
2 Monday
3 Tuesday
Etc...

Simple join, done... However somehow I doubt that the source of this would only contain "Monday" instead of coping with real dates

Actually, you're right there. That's even what I did in one of my dbs. Certainly the way forward.