Change first day of wek

need to change firstdayofweek in an access report
Thomas DawleyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
How is the report built?  What is determining the current setup?  Are you using a query, some VBA function?
Fabrice LambertConsultingCommented:
Please, elaborate.
Thomas DawleyAuthor Commented:
The report was built with the report generator in Access. No query. I want to see weekly totals beginning with Monday as the first day of the week.  The default "firstdayofweek" is Sunday.
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
Any chance you can post the db for us to look over.
Thomas DawleyAuthor Commented:
I would be happy to share the DB with you for review.  Just not sure where/how to do that.
As for the article you suggested, I have read it before but cannot figure out how to apply the instructions to my Access report. Changing the firstdayofweek setting was not in the report generator options.  Any suggestions?
John TsioumprisSoftware & Systems EngineerCommented:
Do you mean something like
weekday(date,vbMonday)

Open in new window

Thomas DawleyAuthor Commented:
From what I have read, that is probably the correct instruction for what I want to accomplish. But, how and where do I put it in the report code to make it function?  Thanks.
Mark EdwardsChief Technology OfficerCommented:
There is a difference between setting your system-wide "firstdayofweek" setting, and just doing it for your database.

To help you out, check-out the link below.  Go the section on Date functions and select the one you want and read-up on it.  It explains how to use the Access date functions and add the "firstdayofweek" switch to them so you can set any day of the week as the first day of the week for a particular function.  I don't believe there is a way to do it globally for just the database file.

https://www.w3schools.com/sql/sql_ref_msaccess.asp
Mark EdwardsChief Technology OfficerCommented:
Here's another link to the same discussion for changing the 1st day of the week in an Access report.  (You aren't the first one to ask this question).   I just simply googled "microsoft access default first day of week for a file" and got all kinds of discussions on the subject.  Not knowing how and where you want to do this, this is the closest I can get to any specifics.

https://www.tek-tips.com/viewthread.cfm?qid=1593337
Thomas DawleyAuthor Commented:
Thanks, Mark.
John TsioumprisSoftware & Systems EngineerCommented:
weekday(date,vbMonday) will give you a number representing the order of days in a week
So monday =1, Tuesday =2 and so on..
SO in your report you must change your query to calculate the sums based on that
Thomas DawleyAuthor Commented:
Thanks, John.
Thomas DawleyAuthor Commented:
Still lost on the exact syntax and where to place it in the query.  Any help would be appreciated.  Thanks.
Mark EdwardsChief Technology OfficerCommented:
What query, where.....????
PatHartmanCommented:
Thomas,
As you can see, we are struggling.  We have no idea how you are using the day of the week to order your data.  Try posting your query since that might help us.  Open the query in SQL view and copy and paste the SQL string since pictures of the QBE don't show all the data and can be hard to read when they end up very small.
Thomas DawleyAuthor Commented:
Sorry for the slow response.  I have been out working all day.

Here is the SQL text but I don't see any code that looks like a grouping instruction:

SELECT [Daily Trip Data].Date, [Daily Trip Data].[Uber Fares], [Daily Trip Data].[Uber Trips], [Daily Trip Data].[Uber Avg Trip], [Daily Trip Data].[Uber Tips], [Daily Trip Data].[Total Uber], [Daily Trip Data].[Lyft Fares], [Daily Trip Data].[Lyft Trips], [Daily Trip Data].[Lyft Avg Trip], [Daily Trip Data].[Total Lyft], [Daily Trip Data].[Day Total], [Daily Trip Data].[Total Trips], [Daily Trip Data].[Combined Avg Trip], [Daily Trip Data].[Lyft Tips]
FROM [Daily Trip Data]
WHERE ((([Daily Trip Data].Date)>#12/31/2018# And ([Daily Trip Data].Date)<#1/1/2020#))
ORDER BY [Daily Trip Data].Date;

This is the sort code from the Report Design View Detail section, I think.
=Format$([Date],"ww",0,0)

This is the date instruction from the Report Design View Date Footer section.
="Summary for " & "'Date' = " & " " & [Date] & " (" & Count(*) & " " & IIf(Count(*)=1,"detail record","detail records") & ")"
Thanks for whatever advice you may have.
PatHartmanCommented:
Change the Format() function to:

=weekday([date],vbMonday)
Thomas DawleyAuthor Commented:
i tried the change you suggested.  The software changed the code to =Weekday([date],[vbMonday]) and gave me a parameter request for vbMonday. I tried it with and without the Format$.  Thanks.
PatHartmanCommented:
The square brackets around vbMonday make it a variable.  Please remove them.
Thomas DawleyAuthor Commented:
I tried that but the software puts them back in.
Mark EdwardsChief Technology OfficerCommented:
In my test, the Access query grid (querydef object) puts square brackets around vbMonday, but not in VBA code.  It works in VBA code.
You may need to put the use of the Weekday() function with vbMonday in a user-defined function and call in from your querygrid
Mark EdwardsChief Technology OfficerCommented:
On 2nd thought, here's how you use it in a querygrid - substitute the numeric value for vbMonday.

https://www.techonthenet.com/access/functions/date/weekday.php

"If you use the Weekday function in a query, you'll have to use the numeric value (ie: 0 to 7) for the firstdayofweek parameter. You can only use the constant equivalent (ie: vbSunday to vbSaturday) in VBA code."
PatHartmanCommented:
Based on Thomas' syntax, he isn't using the function in a query.  Also, as long as the query is running in an Access FE, VBA is available.  But, there may be some other issue so trying the numeric value may solve this immediate problem.

Thomas,
Please post a picture of the form or report control in design view where this is happening.  Also, open any code module and go to tools/references and tell is if anything is marked as MISSING>
Mark EdwardsChief Technology OfficerCommented:
Test have shown that if you use the Weekday() function in a querydef grid, it will automatically put the square brackets around vbMonday, and if you put it in code, it doesn't put square brackets around vbMonday and the function works as advertised.  If you use the numeric equivalent in the query grid it works!

The link in my prior post wasn't lying - that's exactly the way it works.  Can we spend a few moments testing instead of several days guessing?
PatHartmanCommented:
This is the sort code from the Report Design View Detail section, I think.
=Format$([Date],"ww",0,0)
This is NOT in a query.  It is in a report.  However, I tested it and it does not seem to work in the current version of Access.  This is definitely a bug.  Using the numeric value will provide a work around.
Mark EdwardsChief Technology OfficerCommented:
ah, ok.  So the use of the Weekday() function that's causing such an issue is in a control in the report, and not the report recordsource?

I think the same rules that apply to a query grid field apply to a controlsource - they work the same, so it doesn't surprise me that it puts the square brackets around vbMonday.

By the way has ANYONE out there been able to use the Weekday() function with the firstdayoftheweek in a query grid or control source WITHOUT it putting square brackets around the firstdayoftheweek parameter?  I can't.  Someone show me with a screenshot and proof.  Come-on all you older-than-dirt Access sages.  Show us what you're really made of (or stuffed with - whatever the case may be.... ;-)
Thomas DawleyAuthor Commented:
Pat Hartman, I would be happy to test it but I am not sure where to put it in the numeric value in the report code.  Help please.  I am a real novice at all this. Thanks.
PatHartmanCommented:
Look at my most recent comment.  Do you see the quote from YOU?  Wherever you found

=Format$([Date],"ww",0,0)

replace it with

=weekday([date],?)  --> replace the ? with whichever day of the week YOU want to be first.  The default is 1 for Sunday.  If you want Monday to be the first day of the week, replace the ? with a 2.
Thomas DawleyAuthor Commented:
Thanks, Pat.  I will try it.
Thomas DawleyAuthor Commented:
Pat,
Sorry about the long delay.  Had some other issues to work out before I could try what you suggested.  I did try it and did not get the results I had hoped for.  The only change that it made was displaying the day number in the report.  

I have attached screen shots of the Query SQL code, the Design View of the report in question and a page of the report as it generates now.  To save you some reading, I am looking for a way to change the starting day of the week to Monday from the default of Sunday.  

Thanks,
Tom
Screenshot-Query-Daily-Trip-Data-201.png
Screenshot-Weekly-Report-Design-View.png
Screenshot-Weekly-Report.png
John TsioumprisSoftware & Systems EngineerCommented:
Well it would be good to have some sample data...to do some checking but from the looks ..you probably need to order by week of year ...and then by weekday...
So you get week like this
Format(date(),"ww",vbMonday)

Open in new window

e.g today is the 30th week ...and then your order by weekday...1 -->monday, 2 -->Tuesday..e.tc
EDIT: there is one Parameter in the Format...it essentially takes calculates last days of previous to the current year if they are in the same week
Format(#31/12/2018#,"ww",vbMonday,vbFirstFourDays) -->1
Intellisense has a few more options to tweak to your liking.
PatHartmanCommented:
We need to see the date grouping for the form.  You may not be grouping correctly.  If you can post a stripped down version of the database, that would help.
Thomas DawleyAuthor Commented:
John Tsioumpris,
I tried the string you suggested and it caused Access to prompt me for a parameter for "vbMonday,vbFirstFourDays".  So I did some tinkering with that string and pared it down to "=Format$([Date],"ww",[vbMonday])".  Access still prompted me for a parameter for vbMonday at which point I entered as the first Monday in January 2018 (01/01/2018) and Access returned the report exactly the way I want it.  

My question to you: Is it possible to add that date to the code string so that Access does not request it every time I open the Report View?

Thanks,
Tom
John TsioumprisSoftware & Systems EngineerCommented:
Can you give some more info... maybe some screenshots...I think we are getting close
PatHartmanCommented:
Someone mentioned quite a while ago that you probably CANNOT use literals in this expression and that you will need to use the NUMERIC value for each day
Sunday = 1
Monday = 2
etc.
Thomas DawleyAuthor Commented:
PatHartman,
You are correct about the literals.  And, I misspoke in my last entry.  The change did NOT yield the desired result.  Sorry, John Tsioumpris.  I fear we are back to square one unless you can tell me where in the code to add the numeric value for the day of the week.

Also, regarding your previous entry, where do I find the date grouping code?  Please explain what you would consider a "stripped down" version of the database

Thanks,
Tom
John TsioumprisSoftware & Systems EngineerCommented:
Unless some sample database is posted with some sample data and probably with an example of what exactly you want we a going in circles...strange that my solution didn't work ...i am pretty sure that something is missing from the "equation"
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.