troublesome function with #Error (1 Viewer)

[LOUISBUHAGIAR54]

Hello access programmers.

I have this very useful user defined function with the following beginning;

Public Function PbHolhrs(EntryTime As Date, FinishTime As Date, Roster As String, JobTitle As String, TypeofEmployment As String, meta As Date, Btala As String, GhadaBtala As String) As Double

This function works well when both EntryTime and FinishTime are filled in with the appropriate Date and Time. Both EntryTime and FinishTime are date fields.

When there are no entries in both EntryTime and FinishTime fields then the result which I am having is #Error. This is proving very annoying. Filtering out records in which there is no EntryTime and FinishTime is not a solution because the relevant records contain other useful data.

I have tried using an if statement with If IsNull(EntryTime) or IsNull(FinishTime) then etc. etc. but we are still getting the same #Error on the form.

Anyone can help with this matter ?


Many thanks.

Louis Buhagiar

 

[Minty]

Can't you just not call the function based on the lack of those dates?
E.g. something like txtHolHours = IIF(EntryTime = Null OR FinishTime = Null, 0, pdbHolHrs(....))

 

[LOUISBUHAGIAR54]

Many thanks for your reply. It looks promising but I have never used this type of code in a User defined function.

My code starts something like this and continues as the module is quite long. Essentially the code is trying to calculate working hours depending on worker category and their roster.

I suppose the line of code that you are suggesting would have to fit somewhere at the start of the following procedure. Can you please indicate how I could make this work ?

My code runs as follows:



Public Function PbHolhrs(EntryTime As Date, FinishTime As Date, Roster As String, JobTitle As String, TypeofEmployment As String, meta As Date, Btala As String, GhadaBtala As String) As Double

Dim suppostdahal As Date
Dim suppostjohrog As Date
Dim Workhrs As Double

If Format([EntryTime], "dddd") = "Saturday" And Roster = "N" Or (GhadaBtala = "Public Holiday" And Roster = "N") Then 'working between Saturday and Sunday or a holiday
suppostdahal = CVDate(Int(meta) + 0.7916666667)
suppostjohrog = CVDate(Int(meta) + 1.2916666667)
If CDbl(EntryTime) < suppostdahal Then EntryTime = suppostdahal 'comes in earlier than 7pm
If CDbl(FinishTime) > suppostjohrog Then FinishTime = suppostjohrog 'leaves later than 7 am

PbHolhrs = ((CDbl(FinishTime) - Int(CDbl(FinishTime)))) - 0.0416666667 ' sbieh il-hadd jew btala

ElseIf (Format([EntryTime], "dddd") = "Sunday" And Roster = "N") Or (Btala = "Public Holiday" And Roster = "N") Then
suppostdahal = CVDate(Int(meta) + 0.7916666667)
suppostjohrog = CVDate(Int(meta) + 1.2916666667)
If CDbl(EntryTime) < suppostdahal Then EntryTime = suppostdahal 'comes in earlier than 7pm
If CDbl(FinishTime) > suppostjohrog Then FinishTime = suppostjohrog 'leaves later than 7 am

PbHolhrs = (Int(CDbl(FinishTime)) - CDbl(EntryTime)) - 0.0416666667 'il-hadd jew btala sa nofs il-lejl sigha break maqtugha minn hawn

etc. etc.


Louis Buhagiar

 

[Minty]

Not quite - if you call the function without all the parameters you are likely to get the error you describe.

My suggestion was to NOT call the function if certain parameters were missing.

So I'm guessing you have a control on your form with its control source set to PbHolhrs(.....)?

 

[pbaldy]

If an input parameter could be Null you have to change its data type to Variant.

 

[LOUISBUHAGIAR54]

Many thanks. I have changed the data type of the EntryTime and FinishTime to Variant as you have suggested. Now the code stops and shows Runtime error 94 'Invalid use of Null'.

Before the code used to run well and I used to get #Error only in those records where there was no EntryTime or FinishTime entries. Any more ideas how I can solve this riddle.

Many thanks.


Louis Buhagiar

 

[Minty]

I'm not sure why you would call this function if there were no times - am I missing something...

Can you show us the code that Calls this function ?
Or maybe a picture of the form / stripped down copy of database where it is being used.

 

[pbaldy]

There are two basic methods. Don't call the function unless you have all the values, or handle the Null values in the function. If you go that way, using the appropriate data type is only the first step. You need to test for the nulls inside the function and set the return value appropriately, so you avoid a line of code that would blow up on a Null.

 

[LOUISBUHAGIAR54]

I am trying to include the user defined function as part of the argument of an IIF function within a query. It is not working either and I am still getting the #Error.

No luck yet.


Louis Buhagiar

 

[pbaldy]

If an input could be Null, test for it and exit, along the lines of:

If IsNull(FinishTime) Then
  PbHolhrs = 0
  Exit Function
End If

 

[LOUISBUHAGIAR54]

Many thanks to both Minty and pBaldy. With your help I have finally managed to solve this problem as follows using the IIF function. I included the user defined function within the IIf statement as follows and it is now working fine. The following statement was included within the query that supplied the form.

Expr2: IIf(IsNull([EntryTime]) Or IsNull([FinishTime]),0,(PbHolhrs([EntryTime],[FinishTime],[Roster],[JobTitle],[TypeofEmployment],[meta],[Btala],[GhadaBtala])))

Many thanks.

Louis Buhagiar

 

[pbaldy]

No problem. That's the other way I mentioned and Minty had already recommended, not calling the function unless you have all the values. Either works fine, though I lean towards the other method so I don't have to worry about checking the values everyplace I might call the function.

 

[LOUISBUHAGIAR54]

For the sake of completeness I tried as well the method suggested by Paul. The method appeared quite neat. However it did not work. The same error continued to glare in the fields where FinishTime was null.

It appears to be that in a user defined function the first line has a prime function picking up Null values in the arguments.

In any case my UDF is now working well with the IIF function as described above. Many thanks to all.


Louis Buhagiar.

 

[pbaldy]

To use my method you would have had to change the data type to Variant, like:

Public Function PbHolhrs(EntryTime As Variant, FinishTime As Variant, Roster As String, JobTitle As String, TypeofEmployment As String, meta As Date, Btala As String, GhadaBtala As String) As Double