Number of Mondays

[williamr]

I'm trying to count the number of Mondays, Tuesdays, Wednesdays..... from a
column named "daterelease" in a table named "Docs". Here is the code that I
cannot get to work at all, it gives me a number 3036. If anyone can help...

=DCount("daterelease","Docs","DateDiff('d', daterelease, Now)")

Thanks In advance

 

[John Smith]

DateDiff('d', daterelease, Now) will tell you how many days ago the release
date was, nothing to do with the day of the week, hence the 3036.

If you want figures for each weekday then I would suggest that you want an
aggregate query to count them:

SELECT Count(Iif(Format(daterelease, 'w')=1, 1, Null)) As sun,
Count(Iif(Format(daterelease, 'w')=2, 1, Null)) As mon,
Count(Iif(Format(daterelease, 'w')=3, 1, Null)) As tue,
Count(Iif(Format(daterelease, 'w')=4, 1, Null)) As wed,
Count(Iif(Format(daterelease, 'w')=5, 1, Null)) As thu,
Count(Iif(Format(daterelease, 'w')=6, 1, Null)) As fri,
Count(Iif(Format(daterelease, 'w')=7, 1, Null)) As sat
FROM docs ;

Open this in a recordset and you will then have all of your values available.

HTH
John
##################################
Don't Print - Save trees

 

[Ron2006]

DateDiff('d', daterelease, Now) will tell you how many days ago the release
date was, nothing to do with the day of the week, hence the 3036.

If you want figures for each weekday then I would suggest that you want an
aggregate query to count them:

SELECT Count(Iif(Format(daterelease, 'w')=1, 1, Null)) As sun,
Count(Iif(Format(daterelease, 'w')=2, 1, Null)) As mon,
Count(Iif(Format(daterelease, 'w')=3, 1, Null)) As tue,
Count(Iif(Format(daterelease, 'w')=4, 1, Null)) As wed,
Count(Iif(Format(daterelease, 'w')=5, 1, Null)) As thu,
Count(Iif(Format(daterelease, 'w')=6, 1, Null)) As fri,
Count(Iif(Format(daterelease, 'w')=7, 1, Null)) As sat
FROM docs ;

Open this in a recordset and you will then have all of your values available.

HTH
John
##################################
Don't Print - Save trees




- Show quoted text -

Also:

=DCount("daterelease","Docs","DateDiff('d', daterelease, Now)")

does NOT specify a condition for the dcount.
If you just wanted a single count (not more than 1 or 2) you could use

=DCount("daterelease","Docs","DatePart("w", daterelease) = 2")

Ron

 

[williamr]

Hi John, thank you. I entered your code but I get a data type mismatch. The
field daterelease is a date field and the format is mm/dd/yyyy. Not only
don't I understand the hard stuff I don't understand the simple stuff!!

 

[John Smith]

Sorry, I was a bit vague because I don't know what you want to do with these
values once you have them. Here is an outline of retrieving them so that you
do what you will with them inside it:

Dim ThisSet As Recordset, SQL As String
SQL = "SELECT Count(Iif(Format(daterelease, 'w')=1, 1, Null)) As sun," _
& " Count(Iif(Format(daterelease, 'w')=2, 1, Null)) As mon," _
& " Count(Iif(Format(daterelease, 'w')=3, 1, Null)) As tue," _
& " Count(Iif(Format(daterelease, 'w')=4, 1, Null)) As wed," _
& " Count(Iif(Format(daterelease, 'w')=5, 1, Null)) As thu," _
& " Count(Iif(Format(daterelease, 'w')=6, 1, Null)) As fri," _
& " Count(Iif(Format(daterelease, 'w')=7, 1, Null)) As sat" _
& " FROM docs"
Set ThisSet = CurrentDb.OpenRecordset(SQL, DB_OPEN_SNAPSHOT)
With ThisSet
.MoveFirst

' Examples:
If !sun > 0 Then
MsgBox "Never on a Sunday", vbCritical, "Data Error"
End If
Me.SomeControl = !mon
' End Examples

.Close
End With
Set ThisSet = Nothing

HTH
John
##################################
Don't Print - Save trees