Help with Dates

[asif79]

Hi All
I am trying to design a query which counts the number of records. I want to count all the records in a week.
Which Date function do I use to count the records in a week.

Any help in this regards will be highly appreciated.

Regards
Asif

 

[GoodOmens]

Perhaps you could use the DatePart("ww", [date]) function. It returns the week of the year.

 

[asif79]

Hi Goodomens
Thanks for the help. It works now. I would appreciate it if you could help me with one more thing. Is there any way, I could use this same function to group by days. I tried using DatePart('d', [Date]) and it works but it displays the day number. Is there any way I could display the name of the day, rather than the number.

Once again thanks for the earlier help.

Regards
Asif

 

[VBslammer]

The WeekDay() function will give the numeric day of the week. You can use a simple function to translate:

strDay = DayOfWeek(Date)

'@------------------------------------------------------@

Function DayOfWeek(ByVal datDate As Date) As String
  Dim strDay As String
  
  Select Case Weekday(datDate)
    Case 1
      strDay = "Sunday"
    Case 2
      strDay = "Monday"
    Case 3
      strDay = "Tuesday"
    Case 4
      strDay = "Wednesday"
    Case 5
      strDay = "Thursday"
    Case 6
      strDay = "Friday"
    Case 7
      strDay = "Saturday"
  End Select
  
  DayOfWeek = strDay

End Function

VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

Although the simpler method is:

strDay = Format(Date, "dddd")

VBSlammer

Unemployed in Houston, Texas

 

[asif79]

Hi VBslammer
Thanks for the help but can I do this at that table level.

Asif

 

[VBslammer]

Here's a sample SQL statement:

"SELECT Format([Invoices].[DatePaid], 'dddd') As WeekDay,  [Customers].[CustName] FROM Customers INNER JOIN Invoices ON [Customers].[CustID]=[Invoices].[CustID] GROUP BY Format([Invoices].[DatePaid], 'dddd'), [Customers].[CustName];"

VBSlammer

Unemployed in Houston, Texas