Date query Criteria

[Dave]

Access 2003

In a query with a date date field I need to limit to dates since the most
recent Sunday

date()-7 does not work as that limits to the last seven days

and way for the query to know when the last Sunday was?

Thanks
Dave

 

[vanderghast]

DatePart("w", someDate) returns the day of the week ( by default, 1 for
Sunday),



1 + SomeDate- DatePart("w", someDate)



should return the latest Sunday <= someDate. Haven't tried with non default
setting, though.




Vanderghast, Access MVP

 

[KARL DEWEY]

Try this as criteria --

Date()-Format(Date(),"w")+1

 

[Dave]

Exactly what I needed.

Thanks

Dave

Try this as criteria --

 

[Piet Linden]

Access 2003

In a query with a date date field I need to limit to dates since the most
recent Sunday


and way for the query to know when the last Sunday was?

Thanks
Dave

This function returns the previous Sunday (or input date if it falls
on a Sunday).

Public Function PrevSunday(ByVal dtAny As Date) As Date
If Weekday(dtAny) = vbSunday Then
PrevSunday = CDate(dtAny)
Else
PrevSunday = CDate(dtAny - (Weekday(dtAny) - 1))
End If
End Function

So,...

IIF(Weekday([dtAny])=7, [dtAny], CDate([dtAny] - (Weekday([dtAny]) -
1))

should work...