Crosstab Query - Column Headings by Day of the Week

[Guest_imported]

In generating a crosstab query, I am sorting by individual dates, but I want the column headings to read the actual days of the week, e.g., Monday, Tuesday, etc. The current expression is: Expr1: Format([Date],"Short Date&quot
- This is giving me specific dates, whereas I only want Days of the Week. I have tried to manually enter column headings in the column headings option in properties, but this renders the data all null values (but does give me the correct column headings). Is there an access expression, similar to "Short Date" but only for days of the week? - Thanks, John

 

[MichaelRed]

Weekdayname([YourDateField])

but -of course- this CANNOT be the actual field - else the order will be ALL Wrong!

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

Michael's point should be considered as substituting names for dates will change the sort sequence. However, to answer your questionm, you can use the following to give the names of the days of the week.

format(date(), "ddd&quot - 3 character names (Sun, Mon, etc.)
format(date(), "dddd&quot - full names (Sunday, Monday, etc.) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Ctrler]

Although crosstab queries sort column headers in ascending order by default, you can change the order of columns by using some drag and drop in the result table, simply select your monday-column and drag it to the left, etc. Remember to save before your query you switch back to design view. the order (and width, as it is) of columns remains the same until you introduce new column header values.

Regards, Gion

 

[MichaelRed]

There is NO reason you cannot have (and Use) both the WeekDay and the WeekdayName. You just need to know where and how to use them.

In a production environment, doing the drag / drop manual arrangement of fields is sure to cause "issues".

I would (probably) use the weekday for the actual crosstab, and the weekdayname for the reporting, but w/o out knowing more about the app, this is only a first "knee jerk" reaction.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over