query specific days

[abbers_01]

I want to be able to query records from weekends. I have a query to filter between two dates, but I'd like to filter all saturday's and sunday's between two dates. The idea is to query weekend hours of employees.

It would also be nice to be able to query say all monday's or tuesdays or say all monday's and friday, something like that.

Anyone done this or know a link to help me find my way.

 

[raskew]

Hi -

Give this a try:

Public Function GetWeekDays(pmoyr As String, pDays As String) As Integer
'************************************************
'Purpose:   Compute number of weekdays
'           in the specified mm/yyyy
'           where 1 = Sunday thru 7 = Saturday
'Coded by:  raskew
'Inputs:    1) ? GetWeekdays("02/2008", "2")   'count Mondays
'           2) ? GetWeekdays("07/2008", "246") 'count Mondays, Wednesdays, Fridays

'Output:    1) 4
'           2) 13
'************************************************

Dim dteStart As Date
Dim dteEnd   As Date
Dim intEnd   As Integer
Dim intStart As Integer
Dim i        As Integer

   dteStart = DateValue(pmoyr)
   dteEnd = DateAdd("m", 1, dteStart) - 1

   For i = 1 To Len(pDays)
      intStart = intStart + IIf(WeekDay(dteStart) <= Int(Mid(pDays, i, 1)), 1, 0)
   Next i

   For i = 1 To Len(pDays)
      intEnd = intEnd + IIf(Int(Mid(pDays, i, 1)) <= WeekDay(dteEnd), 1, 0)
   Next i

   GetWeekDays = intStart + Len(pDays) * (DateDiff("ww", dteStart, dteEnd) - 1) + intEnd

End Function

HTH - Bob

 

[abbers_01]

Is it possible to delete the entries from a select query where job date does not fall on a weekend. From what I gather its not possible, if that is the case does populating a table from the query and deleting entries based on the dates sound like the way to go with this?

Before I attempt this just want to know if it sounds correct.

 

[raskew]

Hi -

Not sure if you really mean 'delete' or just don't want to show weekend dates.

In either case, add a calculated field: Weekday([yourDate]).

To 'not show' those dates, as criteria:
Not In (1,7)

To delete, use criteria:
In (1,7)
...then convert the Select query to a Delete query.

HTH - Bob