Monday of the week in my SQL Statement (1 Viewer)

Malcolm17

Member
Local time
Today, 07:12
Joined
Jun 11, 2018
Messages
107
Hi,

I am looking to run a report every day from data a temporary table, I am planning when a form opens it runs a few lines of code to fill the temporary table with various lines (different dates) for the report.

The report will be a weekly revenue buildup, (Weekly heading across the top of the page), foe example:
Mon Tue Wed Thu etc
5.00 3.00 2.00 20.00

The code I was planning is below, however I cannot work out what to end my code with as I need the Monday to always be the Monday of the current week, then I was planning for Tuesday would be "Monday + 1" etc kind of thing, unless anyone had any better ideas please?

DoCmd.RunSQL "INSERT INTO DayFlash (D1TheDate, D1RMActual, D1FBActual) SELECT TheDate, [Accommodation]+[Other], [Food]+[Wet] FROM Revenue WHERE REVENUE.TheDate = ?????"

Many thanks,

Malcolm
 

Ranman256

Well-known member
Local time
Today, 03:12
Joined
Apr 9, 2015
Messages
4,339
I would use a form to enter the Monday as the start date, the end date would be:
txtEndDate = DateAdd("d",6,txtStartDate)

Q1 would pull the data in that range,
Q2 would make the Crosstab of Q1 ,like your example shows.
 

plog

Banishment Pending
Local time
Today, 02:12
Joined
May 11, 2011
Messages
11,613
There a reason for the temporary table? Moving data hither and yon isn't really a good idea in a database. Generally you just run a query for the data you need. Why must it be saved to a table?

As for your issue, you can absolutely calculate the date of the most recent occuring monday without having to manually enter it. It is going to require 2 functions inherent to access:


https://www.techonthenet.com/access/functions/date/weekday.php
- this turns a date into a number which represents the day of the week (1=Sunday, 2=Monday, etc.)

https://www.techonthenet.com/access/functions/date/dateadd.php
- this can be used to calculate a date based on adding or subtacting days to it.

In combination, those 2 functiosn can help you determine the date of the most recent occuring monday.
 

Malcolm17

Member
Local time
Today, 07:12
Joined
Jun 11, 2018
Messages
107
Hi Ranman,
Thank you, I didn't think of this even although I have used this solution in other areas. I haven't had time to do all of this tonight, however I did quickly test it and it does work for what I need it to do!! Many thanks, Malcolm

Hi Plog,
The reason for the temporary table is that I will be using about 7 or 8 queries to feed one single page report (1 query for each day, plus one or two others) and I don't know how to put data from a column in to a field on a report from says rows 2 - 7. If you can help with this I'd be appreciated as it would help me with a couple other area's of concern to me. Thank you, Malcolm.
 
Last edited:

Users who are viewing this thread

Top Bottom