What's wrong with this expression...

[DurhamCons]

DateDiffW(DateSerial(DatePart("yyyy",Date()),1,1),(Now()))

I'm trying to reference a DateDiffW module to calculate the number of business days from the first of the year to the current date.

 

[ssoltic]

I think DateDiffW is functions that cannot run from expression. You must crete function in VBA and call that function from code.

Try with this link:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;207795

 

[DurhamCons]

I think DateDiffW is functions that cannot run from expression. You must crete function in VBA and call that function from code.

Try with this link:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;207795

Thanks. I already have that in a module. I'm just not clear on the syntax for "the 1st of the year."

Maybe I'm not understanding something?

Thanks of the reply.

 

[ssoltic]

I put this formula to calculate "1st day of year":

1stDay=DateSerial(DatePart("yyyy", Now()), 1, 1)

For calculating workdays between two days I have functions but I dont now if realy works:

Public Function DiffWeekdays(datDay1 As Date, datDay2 As Date) As Long

' Comments : Returns the number of weekdays between two dates

' The days are rounded down (it takes 24 hours to make a day)

' Parameters: datDay1 - first (earlier) date/time

' (subtracted from datDay2)

' datDay2 - second (later) date/time

' Returns : Number of whole business days between two dates

' (Returns negative days if datDay1 is after datDay2)

' Source : Total Visual SourceBook 2000

'

Dim lngDays As Long

Dim lngWeeks As Long

Dim datFirstDate As Date

Dim datLastDate As Date

Dim datNewDate As Date

Dim intDirection As Integer

On Error GoTo PROC_ERR

If datDay1 < datDay2 Then

datFirstDate = datDay1

datLastDate = datDay2

intDirection = 1

Else

datFirstDate = datDay2

datLastDate = datDay1

intDirection = -1

End If

' Subtract weekends

lngWeeks = Fix(Fix(datLastDate - datFirstDate) / 7)

lngDays = lngWeeks * 5

datNewDate = CDate(datFirstDate) + lngWeeks * 7

While datNewDate < datLastDate

datNewDate = datNewDate + 1

If datNewDate <= datLastDate Then

' Don't count days when new day is Sunday or Monday.

' (When new day is Saturday, you are actually counting Friday)

If WeekDay(datNewDate) <> 1 And WeekDay(datNewDate) <> 2 Then

lngDays = lngDays + 1

End If

End If

Wend

DiffWeekdays = intDirection * lngDays

PROC_EXIT:

Exit Function

PROC_ERR:

MsgBox "Error: " & Err.Number & ". " & Err.Description, , _

"DiffWeekdays"

Resume PROC_EXIT

End Function

I hope this help!

 

[ssoltic]

I forgot, for query formula is:

1Day: DateSerial(DatePart("yyyy";Now());1;1)

 

[DurhamCons]

Thank you sir! I appreciate your responses. My roommate figured out that my query was confused (had two fields name the same thing in two different tables). Once he fixed that, my query worked.

Here is what I used for future people who might find this searching....

Function DateDiffW(BegDate As Variant, EndDate As Variant) As Integer
Const Sunday = 1
Const Saturday = 7
Dim NumWeeks As Integer

    BegDate = DateValue(BegDate)
    EndDate = DateValue(EndDate)
    
    If BegDate > EndDate Then
       DateDiffW = 0
    Else
       Select Case Weekday(BegDate)
          Case Sunday
             BegDate = BegDate + 1
          Case Saturday
             BegDate = BegDate + 2
       End Select
       Select Case Weekday(EndDate)
          Case Sunday
             EndDate = EndDate - 2
          Case Saturday
             EndDate = EndDate - 1
       End Select
       NumWeeks = DateDiff("ww", BegDate, EndDate)
       DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
    End If
End Function

Here is the expression I used in my query...

WorkDays: DateDiffW(DateSerial(Year(Date()),1,1),Date())

Here are search words for the future...

Business Days Work Days YTD Year-to-Date DateDiffW() DateDiff()