Empty Date Field Substitution

[dsajones]

*** SOLVED *****



Sorry, just after posting this I found a potential solution that appears to have worked. Instead of the function definition being:

Public Function Weekdays(ByRef startDate As Date, ByRef endDate As Date) As Integer

I've changed it to:

Public Function Weekdays(ByRef startDate As Variant, ByRef endDate As Variant) As Integer

This seems to be working correctly whether Quote_Received_Date is null or not. Hopefully someone may find this useful.

Cheers
David


------------------- Original Post ----------------------
Hi All,

I have a form with two dates on it. Date a quote is sent and date quote is received/accepted. I have a calculated field to show the number of working days between the two dates. This uses a function I picked up from another forum as there is no built in working days function in Access as there is in Excel. That works fine.

My problem is that the quote accepted date will be blank until, obviously, the quote gets accepted. So I want the calculated field in that scenario to be the number of working days between today and the date the quote was sent.

The overall logic is therefore:

If date quote accepted is empty then
- calculated field = number of weekdays between today and date quote sent
Else
- calculated field = number of weekdays between date quote accepted and date quote sent

I just can't find a way of getting this to actually work when the date quote accepted is blank. I've tried this in the expression for the calculated field:

=if(IsNull([Quote_Received_Date]),Weekdays[Quote_Sent_Date],(Date()),Weekdays([Quote_Sent_Date],[Quote_Received_Date]))

Weekdays is the name of the function I'm using. This just produces an error #Name? in the calculated field. It doesn't call the function at all.

If I remove the isnull test and just have the function called as =Weekdays([Quote_Sent_Date],[Quote_Received_Date]) that works fine as long as there is a date in Quote_Received_Date. But if I delete Quote_Received_Date I get a #Type! error in the calculated field with the function not being called at all.

So finally, I've also tried to modify the function to test for a null date being passed and substituting today's date if Quote_Received_Date is null. But I still get the #Type! error and the function doesn't get called.

The actual begin function line is:

Public Function Weekdays(ByRef startDate As Date, ByRef endDate As Date) As Integer

Is there something that can be put in there to test for a null endDate?

Many thanks
David

[pbaldy]

Thanks for updating the thread David. That's what I'd have done too, along with testing for Null within the function to control what gets returned.