I'm trying to work out an expression that will return the number of records
in a table that were added in the current week (Sun-Sat). I have a date
field in the table.

I have tried using a string I found on the forum (below), which was for
quarters, tweaking for weeks, but am new to more complicated expressions and
it returns an error.

=DCount("*","tblRequests","[Date]>=" &
Format(DateSerial(Year(Date()),3*(DatePart("ww",Date())-1)+1,1),"#mm\/dd\/yyyy#"))

Any help most gratefully received.

Tonia

First day of current week =
DateAdd("d",1-Weekday(Date()),Date())

Last day of current week =
DateAdd("d",7-Weekday(Date()),Date())

DCount("*","tblRequests","[Date]>=" &
Format(DateAdd("d",1-Weekday(Date()),Date()),"#mm\/dd\/yyyy#"))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Tonia King wrote:
> I'm trying to work out an expression that will return the number of records
> in a table that were added in the current week (Sun-Sat). I have a date
> field in the table.
>
> I have tried using a string I found on the forum (below), which was for
> quarters, tweaking for weeks, but am new to more complicated expressions and
> it returns an error.
>
> =DCount("*","tblRequests","[Date]>=" &
> Format(DateSerial(Year(Date()),3*(DatePart("ww",Date())-1)+1,1),"#mm\/dd\/yyyy#"))
>
> Any help most gratefully received.
>
> Tonia
>

You could do something along the line of

Function GetRecCount() As Long
On Error GoTo Error_Handler
Dim sSQL As String
Dim rs As Recordset

sSQL = "SELECT Count(tbl_Invoice_General_Info.Inv_Id) AS CountOfInv_Id "
& vbCrLf & _
"FROM tbl_Invoice_General_Info " & vbCrLf & _
"WHERE (((tbl_Invoice_General_Info.Inv_Dt) Between #5/9/2010#
And #5/15/2010#));"

Set rs = CurrentDb.OpenRecordset(sSQL)

If rs.RecordCount > 0 Then
GetRecCount = rs![CountOfInv_Id]
End If

Error_Handler_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Function

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf &
"Error Number: " & _
Err.Number & vbCrLf & "Error Source: GetRecCount" & vbCrLf & "Error
Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function

Obviously you need to switch out your Table and Field names. Notice the use
of the Between clause to only pull the count for the date range that you are
interested in.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Tonia King" wrote:

> I'm trying to work out an expression that will return the number of records
> in a table that were added in the current week (Sun-Sat). I have a date
> field in the table.
>
> I have tried using a string I found on the forum (below), which was for
> quarters, tweaking for weeks, but am new to more complicated expressions and
> it returns an error.
>
> =DCount("*","tblRequests","[Date]>=" &
> Format(DateSerial(Year(Date()),3*(DatePart("ww",Date())-1)+1,1),"#mm\/dd\/yyyy#"))
>
> Any help most gratefully received.
>
> Tonia
>

This is great. Just what I needed. Many thanks

"John Spencer" wrote:

> First day of current week =
> DateAdd("d",1-Weekday(Date()),Date())
>
> Last day of current week =
> DateAdd("d",7-Weekday(Date()),Date())
>
> DCount("*","tblRequests","[Date]>=" &
> Format(DateAdd("d",1-Weekday(Date()),Date()),"#mm\/dd\/yyyy#"))
>
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Tonia King wrote:
> > I'm trying to work out an expression that will return the number of records
> > in a table that were added in the current week (Sun-Sat). I have a date
> > field in the table.
> >
> > I have tried using a string I found on the forum (below), which was for
> > quarters, tweaking for weeks, but am new to more complicated expressions and
> > it returns an error.
> >
> > =DCount("*","tblRequests","[Date]>=" &
> > Format(DateSerial(Year(Date()),3*(DatePart("ww",Date())-1)+1,1),"#mm\/dd\/yyyy#"))
> >
> > Any help most gratefully received.
> >
> > Tonia
> >
> .
>