Date calc without weekends

[Stacey]

I am trying to set up a field in a query that calculates the date between the DateReceived and DateCompleted, not including weekends. I have searched the forum and found some suggestions, but I can't get any of them to work. I tried the DateDiffW and the NETWORKDAYS, but I get an 'Undefined Function 'DateDiffW'. I am on Access 97, would that be why this is not working? If so, can anyone guide me on how to do what I need in Access 97?

Thanks so much.

Stacey

 

[Mile-O]

Here you go...

 

[Stacey]

Thanks, but....

I am a novice at using code. I can do basic things, mostly in Reports. How do I get this code to interact with my query. Or do I have to go at it from a different angle? I have the query results displaying in a form. I'm just not sure how to use the information you've given me.

On a side note, if I am trying to learn VB Code on my own, what suggestions would you have (as far as books that are "For Dummies', or websites that may have tutorials? My company sends us to classes, but the most advanced one is Advanced Access, which doesn't address code. I am a business analyst, not a programmer, but I like Access a lot and so have learned quite a bit on my own, but would like to learn more.

Thanks!
Stacey

 

[Stacey]

I figured out how to get the module to work in my query. I couldn't get the code you gave me a link to to work though. I did find different code, and it is working, EXCEPT, I am getting an 'Invalid use of Null" error when it hits a record that has a blank DateCompleted field. I don't know how to fix this in the code. Can you help me? This is the code I found that works otherwise.

Function DateDiffW(DateReceived, DateCompleted)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If DateReceived > DateCompleted Then
DateDiffW = 0
Else
Select Case WeekDay(DateReceived)
Case SUNDAY: DateReceived = DateReceived + 1
Case SATURDAY: DateReceived = DateReceived + 2
End Select
Select Case WeekDay(DateCompleted)
Case SUNDAY: DateCompleted = DateCompleted - 2
Case SATURDAY: DateCompleted = DateCompleted - 1
End Select
NumWeeks = DateDiff("ww", DateReceived, DateCompleted)
DateDiffW = NumWeeks * 5 + WeekDay(DateCompleted) - WeekDay(DateReceived)
End If
End Function

Thank You!!

 

[Mile-O]

You can use the Nz() function to replace the Null value with a new date.

The example below will, if the DateCompleted is a null value, replace it with the current date.

i.e.

Select Case WeekDay(Nz(DateCompleted, Date))

 

[Stacey]

I'm probably putting it in the wrong place, because I'm still getting the Invalid use of Null error. Where in the code should I insert it?

I really appreciate your patience with me.

 

[Mile-O]

Rather than bother changing the function, go to the source. Where are you calling the function from? A form? A query?

Use the Nz() function when calling the DateDiffW function.