Date Query

[vimrcds]

I have a database for tracking service calls and generating invoices. I'm trying to create a query that will tell me how many hours each tech has billed out in any given week.

It has to run on a Saturday to Friday cycle (payday being Friday), so I can't just use Date() - 7 or anything like that. I looked into DatePart(), but don't really understand what it returns or if it would solve my problem.

How can I limit the records that the query pulls to ones within a given pay-cycle week?

Fields in the Labor table (where the query is pulling from):


Tech
Date of Call
AH (abbreviated for After Hours, a on/off checkbox field)
M
T
W
Th
F
S
Su

The days of the week fields are number fields indicating how many hours were billed out on any given day for each call. Each record represents a call, so there will (usually and HOPEFULLY lol) be multiple calls per tech per day. I can't move the information to a different table every week, because it's needed in other reports and would just be impractical, so I need to figure this out.

Any help would be greatly appreciated, I'm sorry if I'm crap at explaining this.

Thanks,
Sloan

[ajetrumpet]

so you want a query that will always pull recs from the last saturday to the next friday, right?

try this:

SELECT ..... FROM ..... where ..... between  

DateAdd("d", -(Weekday(Date(), ConvertWeekdayEnum("saturday")) - 1), Date()) and 

DateAdd("d", -(Weekday(Date(), ConvertWeekdayEnum("friday")) - 1), Date())

and copy this code into a module:

Function ConvertWeekdayEnum(Optional wDay As String) As Integer

'******************************************************************************
'                                                                             *
'Author: Adam Evanovich                                                       *
'Date: 4/22/2004                                                              *
'Purpose: To return the enumeration integer value for a specified             *
'         day of the week.                                                    *
'                                                                             *
'Arguments:                                                                   *
'wDay > Any day of the week (String Value).  If omitted, Sunday will be used. *
'                                                                             *
'******************************************************************************

  Select Case wDay

    Case "Monday"
      ConvertWeekdayEnum = vbMonday
    
      Case "Tuesday"
        ConvertWeekdayEnum = vbTuesday
    
        Case "Wednesday"
          ConvertWeekdayEnum = vbWednesday
    
          Case "Thursday"
            ConvertWeekdayEnum = vbThursday
    
            Case "Friday"
              ConvertWeekdayEnum = vbFriday
    
              Case "Saturday"
                ConvertWeekdayEnum = vbSaturday
                
                Case "Sunday"
                  ConvertWeekdayEnum = vbSunday

                  Case Else
                    ConvertWeekdayEnum = vbSunday

  End Select

End Function

there are easier ways, but that's readily available from my own repertoire.

[vimrcds]

Thank you for your help. It wasn't exactly what I needed, but your code did point me in the right direction to get it figured out. Thanks alot!