Query Date Criteria: Before the Previous Friday (1 Viewer)

M

mcinta19

New member

Local time

Today, 00:17

Joined

Jan 16, 2018

Messages

7

• Jan 30, 2018

• #1

I need to run a query that returns all the results that have a due date on or prior to the Friday of the previous week.

Any help would be appreciated.

 

jdraw

Super Moderator

Staff member

Local time

Today, 00:17

Joined

Jan 23, 2006

Messages

15,380

• Jan 30, 2018

• #2

Last/previous Friday can be found using

Code:Copy to clipboard

Format(Date-(weekday(date,vbFriday)-1), "dd-mmm-yy")

'with dd-mmm-yy format

or

Code:Copy to clipboard

Date-(weekday(date,vbFriday)-1)

'with regional setting date default

 

P

Pat Hartman

Super Moderator

Staff member

Local time

Today, 00:17

Joined

Feb 19, 2002

Messages

43,196

• Jan 30, 2018

• #3

Formatting a date using Format() turns it into a string which will make it act like a string. That means it will not compare correctly or sort correctly.

 

M

mcinta19

New member

Local time

Today, 00:17

Joined

Jan 16, 2018

Messages

7

• Jan 30, 2018

• #4

I tried that and it gave me an error message.

 

jdraw

Super Moderator

Staff member

Local time

Today, 00:17

Joined

Jan 23, 2006

Messages

15,380

• Jan 30, 2018

• #5

I tried that and it gave me an error message.

Click to expand...

Post your code and the error message.

This is how I determined that last Friday was Jan 26, 2018

Code:Copy to clipboard

?Date-(weekday(date,vbFriday)-1)
26-Jan-2018