Public Function causes Access to crash??

[sbowman]

I'm writing a function to provide the time to close for help desk
tickets. There are 6 different departments to take into consideration,
therefore my code looks like this:

Public Function TimeToClose(Dept, LanStatus, TechStatus_1,
TechStatus_2, TechStatus_3, TechStatus_4, _
TechStatus_5 As String, LanDueDate, Tech1DueDate, Tech2DueDate,
Tech3DueDate, Tech4DueDate, _
Tech5DueDate, LanAssignedDate, LanClosedDate, TechAssignedDate_1,
TechClosedDate_1, TechAssignedDate_2, _
TechClosedDate_2, TechAssignedDate_3, TechClosedDate_3,
TechAssignedDate_4, TechClosedDate_4, _
TechAssignedDate_5, TechClosedDate_5 As Date) As Date


If Right(Dept, 1) = "0" And LanStatus = "Closed" Then
TimeToClose = DateDiff("d", LanAssignedDate, LanClosedDate) + 1
ElseIf Right(Dept, 1) = "1" And TechStatus_1 = "Closed" Then
TimeToClose = DateDiff("d", TechAssignedDate_1, TechClosedDate_1) +
1
ElseIf Right(Dept, 1) = "2" And TechStatus_2 = "Closed" Then
TimeToClose = DateDiff("d", TechAssignedDate_2, TechClosedDate_2) +
1
ElseIf Right(Dept, 1) = "3" And TechStatus_3 = "Closed" Then
TimeToClose = DateDiff("d", TechAssignedDate_3, TechClosedDate_3) +
1
ElseIf Right(Dept, 1) = "4" And TechStatus_4 = "Closed" Then
TimeToClose = DateDiff("d", TechAssignedDate_4, TechClosedDate_4) +
1
ElseIf Right(Dept, 1) = "5" And TechStatus_5 = "Closed" Then
TimeToClose = DateDiff("d", TechAssignedDate_5, TechClosedDate_5) +
1
End If


End Function

whenever i insert this function in to my query, access crashes. Is
Access unable to handle this many variables being passed to a function?

Thanks!
Shelley

 

[John Spencer]

Access will allow you to pass up to 39 arguments to a function when you are
using the function in a query.

What do you mean by Access crashes? Does it generate an error? Quit? Give
you a message and ????

Try changing the declaration of
TechStatus_5 As String
to
TechStatus_5

This will allow TechStatus to be Null as well as a string. That is what you
did with all the other tech_Status arguments you are passing in. Also, do
the same for
TechClosedDate_5 As Date

 

[sbowman]

Thanks, John...I tried all that and I still get the error message
"Microsoft Access has encountered a problem and needs to close. We are
sorry for the inconvenience." Send Error Report/Don't Send buttons.
This occurs when I try to use the function in a query field.When I
click on the function to insert it, Access gives me that error and
closes.

 

[John Spencer]

Sounds like you may have suffered some corruption in your database.

Try Allen Browne's article on Recovering from Corruption at:
http://allenbrowne.com/ser-47.html

Also you take a look at Tony Toews site
http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Guest]

For Access 97, the limit was 28, then 'query too complex".

Access 2000 in particular just sometimes crashed when it
hit an exception - the exception handler started out as broken,
advanced to faulty, then MS abandoned the customers and
released Access 2002.

Queries (like VBA) are stored both in a 'source code' form
and a 'compiled' form. Sometimes you have corrupt compiled
VBA or compiled Query.

(david)