First day of a week date-how find

[gg80]

I use Access 2004 and 2007. I have a program that includes a table that contains the value of sold items and the date sold I would like to create a graph that displays the total$ of sold items by week and uses the first day of the week (07/19/21 format) for the X axis With a totals query, I can get the week numbers by grouping date sold with the phrase: DatePart("ww",[Date],2).



I also get the minimum sold date and week total$ in the same query. (The first sale is not always on the first day of the week.)

Is there a way to get the first day of the week from the week number? Any help much appreciated

[CJ_London]

easier to work it from the date rather than the week. Assuming your weeks start on a Monday

?date()-weekday(date(),vbmonday)+1

note that date is a reserved word (it means today per my example) and should not be used as a field name

and if you wanted to do monthly, 1st of month would be

?date()-day(date())+1

[gg80]

I need a little more if possible.. As shown, only shows today's date. Also, Access 2007 refuses vbMonday and had to remove the question mark (should I enter something where the question mark is?) How do I show first date of the week referring to a date in the week or the week number?

[Welshgasman]

I need a little more if possible.. As shown, only shows today's date. Also, Access 2007 refuses vbMonday and had to remove the question mark (should I enter something where the question mark is?) How do I show first date of the week referring to a date in the week or the week number?

Well that is a load of BS?
I have 2007, and that is al I have.?
In the immediate window ?

?date()-weekday(date(),vbmonday)+1
19/07/2021

Try adding a number to date, still works?

?date()+5-weekday(date()+5,vbmonday)+1
19/07/2021 
19/07/2021

BTW the ? is printing the result in the immediate window.?
If you want to set a variable it would be replacing ? with MyVar =

[gg80]

Appreciate your reply. Still doesn't work. It may be that I wasn't clear that I am using a query, not VBA? Also not clear how link to a date during the week. Another complication, I just was told that my user wants the first day of the week to be a Monday.

[moke123]

Appreciate your reply. Still doesn't work. It may be that I wasn't clear that I am using a query, not VBA? Also not clear how link to a date during the week. Another complication, I just was told that my user wants the first day of the week to be a Monday.

"Still doesn't work" still leaves us guessing.

Create a function and use that in your query

Function FDOW(dteIN As Date) As Date
FDOW = dteIN - Weekday(dteIN, vbMonday) + 1
End Function

[Welshgasman]

Yes, not clear at all.
Do you replace Date() with your actual date?
Yes the query will not recognise vb constants, so use the value 2 ?, or whatever you want your week to start as?

SELECT TestTransactions.ID, TestTransactions.Account, TestTransactions.TransactionDate, [Transactiondate]-Weekday([transactiondate],1)+1 AS MyMonday
FROM TestTransactions;

Results

[gg80]

"Still doesn't work" still leaves us guessing.

Create a function and use that in your query

Function FDOW(dteIN As Date) As Date
FDOW = dteIN - Weekday(dteIN, vbMonday) + 1
End Function

I thought that I had replied, but don't see it anywhere. Also can't find how to mark this as solved. I will look some more.
Thanks so much for solving my problem, much appreciated. I don't understand it, but very glad to get it.

[CJ_London]

don't see why you need to use a function, in the context of how you are using it, it will be slow compared with using SQL.

if you don't provide the names of the fields you are using, how are we supposed to know?

but just supposing the field in your table is called myDate then in a query you would use

myMonday:myDate()-weekday(myDate(),2)+1

[gg80]

The field is called INSP_DATE and is a field in the query. In the query I used myMonday: INSP_DATE()-Weekday(INSP_DATE(),2)+1
When I open the query, I get a message "Undefined function "Insp_Date" in function".

[moke123]

get rid of the ()
try:

myMonday: INSP_DATE-Weekday(INSP_DATE,2)+1

[CJ_London]

My bad - left the brackets on from the date function

[gg80]

This also works. Thanks much.

[gg80]

See Moke123 below. Getting rid of the () made it work. Thanks for solution.