date=last monday (1 Viewer)

JEPEDEWE

Registered User.
Local time
Today, 23:30
Joined
Nov 11, 2010
Messages
34
Hi,

I have a table with a date field
I want a query that displays all records from last monday till now
So I wrote:

>Date - Weekday(Date; vbMonday) + 1

this returns an errormassage saying:
"The expression you entered has an invalid .(dot) or ! operator or invalid paramaters"
Cursor stood on the first bracked (

So I changed it to
>Date()-Weekday(Date();vbMonday)+1

This returns an errormessage saying:
Data type mismatch in criteria expression

Access changed the syntax above into
>Date()-Weekday(Date();"vbMonday")+1

placing vbMonday between "

What is wrong
Thanks
:banghead:
 

Mr. B

"Doctor Access"
Local time
Today, 17:30
Joined
May 20, 2009
Messages
1,932
Try using:

> Format(DateAdd("d", vbSunday - Weekday(Date), Date) + 1, "yyyy-mm-dd")

as your critieria.
 

Brianwarnock

Retired
Local time
Today, 23:30
Joined
Jun 2, 2003
Messages
12,701
As this is in a query I think that you have to use the numeric number not vbMonday.

Try
Date()-Weekday(Date()-3)

the 3 is Tuesday, why start Monday and +1 ?

Brian
 

JEPEDEWE

Registered User.
Local time
Today, 23:30
Joined
Nov 11, 2010
Messages
34
Perfect.... thansk a lot
Tried the Brian-solution... no errormessage and result just like I wanted

But
Thank you all!!!
JP, Belgium
 

Tom_2020

New member
Local time
Tomorrow, 00:30
Joined
May 18, 2020
Messages
1
Hello ... I know this post is a little old, but I also had this problem and I saw it today. To make it clear:

Brianwarnrock's solution works fine as long as it's not Monday today. Then he would go back 1 week. James' solution is therefore more precise if you need "today" on a Monday and otherwise the last Monday.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom