Query - date field

[k.roberts]

I have created a query which I want to use as the basis for running a
report. I want the query to pull through all records which are dated
within 2 weeks of todays date (i.e. the current date, not literally
todays date 5th Jan!). Therefore when I run a report based on this
query, it will show all records that were created 2 weeks prior.

I hope I've explained myself properly and that someone can help me with
what I put in the criteria field of the query.

Many thanks.

 

[Allen Browne]

Try Criteria in your query under this date field like this:

Between DateAdd("d", -14, Date()) And DateAdd("d", 14, Date())

 

[k.roberts]

That's great, works fine. Thanks very much for your help!

 

[k.roberts]

Just another quick question, is this going to show records from the
previous 14 days, because I really need the previous 2 calendar weeks?

 

[Guest]

Define "previous 2 calendar weeks"

 

[k.roberts]

If I run a report on Wednesday, then I want it to show all records
entered from Mon-Weds of that week, PLUS all records from Mon-Weds of
the 2 preceding weeks.

Or I need some help in creating a criteria expression and query so I
can enter a date and have all records that have been entered after that
date. In fact that is probably easier!

 

[Guest]

Here is a function that you can use in your query's critera to determine if
the date is to be included. It looks for Monday through the current day of
the week beginning on the Monday of the two previous weeks. For example, If
you used today (1/5/2006), it would include dates from 12/19/2005 through
today if the dates are Monday through Thursday:

Function ReportDateRange(dtmCheckDate As Date) As Boolean
Dim dtmBeginDate As Date
Dim blnInclude As Boolean

'set the include variable to No
blnInclude = False
'Find the date two weeks ago
dtmBeginDate = DateAdd("d", -14, date)
'Find the monday for that week
Do Until Weekday(dtmBeginDate, vbMonday) = 1
dtmBeginDate = DateAdd("d", -1, dtmBeginDate)
Loop
'See if it is in the date range to includ
If dtmCheckDate >= dtmBeginDate And dtmCheckDate <= date Then
'See if it is between Monday and the current day of the week
If Weekday(dtmCheckDate, vbMonday) <= Weekday(date, vbMonday) Then
blnInclude = True
End If
End If
ReportDateRange = blnInclude
End Function

 

[Allen Browne]

Yes, the expression will work on 14 days before and after today.

If you want to adjust for the the working week, use the Weekday() function
to subtract the day of the week from todays date. Then subtract 13 to go
back 13 further days.