Invalid Use of Null

[JEA]

Edit: 6th post (5th reply) is simpler version of the problem




This function works in the immediate window of the VB builder. When I use it in a query it throws up an Invalid use of null error for each reccord.

Public Function dayMissedHours(WorkedDay As Date)

Dim s1 As Integer


If DLookup("[NoOfStaffRequired]", "hours", "[DateWorked] = #" & WorkedDay & "#") >= 1 And Not (IsNull(DLookup("[Staff1PIN]", "hours", "[DateWorked] = #" & WorkedDay & "#"))) Then  'If staff is req AND a staff member is entered then
    s1 = StaffMissedHours(DLookup("[Staff1PIN]", "hours", "[DateWorked] = #" & WorkedDay & "#"), WorkedDay)     'Work out missed hours for that staff member
Else
    If DLookup("[NoOfStaffRequired]", "hours", "[DateWorked] = #" & WorkedDay & "#") < 1 Then       'Staff not required, missed hours = 0
        s1 = 0
    Else                                                                                            'Els must be staff required but not entered

    [COLOR="Red"]s1 = DLookup("[ShiftLength]", "WorkedShiftTypeLookup", "[ShiftType] =""" & _
            (DLookup("[s1Type]", "hours", "[DateWorked] = #" & WorkedDay & "#")) & """" _
        )                          [/COLOR]                                                                 'missed hours = shift length
    End If
End If

dayMissedHours = s1

The Red code is where the debugger says the error is.
In the query that generates the error there's a field called DateWorked, it's that table's index so it can't be Null.
I've created the field "MissedHours: dayMissedHours([DateWorked])". This is what calls the function and generates the error.

Non of the fields it's looking for are null, I've checked. It works in the immediate window. Why is it not working in the query??!!

 

[JEA]

Looked in to it a bit further:

Public Function dayMissedHours(WorkedDay As Date)

Dim s1 As Integer


If [COLOR="Red"]DLookup("[NoOfStaffRequired]", "hours", "[DateWorked] = #" & WorkedDay & "#")[/COLOR] >= 1 And Not (IsNull(DLookup("[Staff1PIN]", "hours", "[DateWorked] = #" & WorkedDay & "#"))) Then  'If staff is req AND a staff member is entered then
    s1 = StaffMissedHours(DLookup("[Staff1PIN]", "hours", "[DateWorked] = #" & WorkedDay & "#"), WorkedDay)     'Work out missed hours for that staff member
Else
    If DLookup("[NoOfStaffRequired]", "hours", "[DateWorked] = #" & WorkedDay & "#") < 1 Then       'Staff not required, missed hours = 0
        s1 = 0
    Else                                                                                            'Els must be staff required but not entered

    s1 = DLookup("[ShiftLength]", "WorkedShiftTypeLookup", "[ShiftType] =""" & _
            (DLookup("[s1Type]", "hours", "[DateWorked] = #" & WorkedDay & "#")) & """" _
        )                                                                                           'missed hours = shift length
    End If
End If

dayMissedHours = s1

The error is stemming from the fact that the red text is Null.
In the immediate window (where it works) I type "?dayMissedHours(#01/11/07#)" , without the hash ("#") marks I get the same error. This implies I'm passing the arguments to the function incorrectly in the query.

I'm typing "MissedHours: dayMissedHours([DateWorked])" in the query field that throws the error. [DateWorked] is another field in the query and is a short date in the table that I'm querying. The function expects a date as it's argument.

The only thing I can think is if the query turns [DateWorked] in to a string when it get's it from the table. If so, how do I turn it back in to a date before I pass it to the function? Any other ideas?

 

[boblarson]

Use the NZ Function or you can use an IIF statement to check to see if the field is null and if it is you can substitute a value or bypass the thing completely.

 

[The_Doc_Man]

Very often, DLookup returns NULL if you ask for something using the wrong data type, because the selection criteria won't be met and the result is a NULL set.

CDate(date-string) and CStr( date-type) allow you to interconvert dates and text. You can also use the Format function to convert a date-type into a string of a specific format other than the system's default.

 

[JEA]

boblarson: Use the NZ Function or you can use an IIF statement to check to see if the field is null and if it is you can substitute a value or bypass the thing completely.

Thanks but I don't want it bypassed. I want it to execute.

Very often, DLookup returns NULL if you ask for something using the wrong data type, because the selection criteria won't be met and the result is a NULL set.

I think that's what's happening. The argument passed to the function is used in the criteria for the DLookUp.
When I call the function in the immediate window like:

dayMissedHours(#01/11/07#) it works

dayMissedHours(01/11/07) doesn't work, it throws the same error as the query.

This tells me that the problem lies with how the arguments are passed from the query. As before, the query uses a field called [DateWorked]. It get's the data for this field from a table where short dates are stored in this field. There's a between statement in it's criteria.
I've crated a calculated field:

MissedHours: dayMissedHours([DateWorked]) doesn't work as I don't believe [DateWorked] is still a date by the time it get's to the function.

MissedHours: dayMissedHours(#[DateWorked]#) doesn't work
MissedHours: dayMissedHours(CDate([DateWorked])) doesn't work

This is veeeerrrrrrryyyyyyy frustrating!

 

[JEA]

Still can't get this to work. I've narrowed down the problem further and here's a simplified example of the problem.

Public Function Test(workedDate As Date)

Test = DLookup("[NoOfStaffRequired]", "Hours", "[DateWorked] = #" & workedDate & "#")


End Function

This function works in the immediate window of the VB builder as:

?Test(#01/11/07#)

I want to use it in a text field of a form. The argument will eventually be another field in the form, but I can't even get it to work if I give it the date manually. (The result of the look up is NOT null, it contains the int '5', which is what is returned in the immediate window)

The way's I've tried are:

=Test("#" & 1/11/2007 & "#") 'This evaluates as #Error

=Test("#" & "1/11/2007" & "#") 'This evaluates as Null

=Test(01/11/07) 'Evaluates As Null

=Test(#01/11/07#) 'Evaluates as null

=Test(CDate(01/11/07)) 'Evaluates as #Error

It works in the VB Editor's Immediate window!!! Why is it not working here?!!! I'm sure the problem lies in the fact the DLookup isn't recognising the argument as a date. But then why am I not getting a data type error as the function should only accept a Date, and the DLookUp uses what the function accepts.

Tearing my hair out on this one!!

 

[JEA]

Solved it if anyone's interested (quite a common problem by all accounts).

In my example it was because VBA will only work with dates in the USA format. Regardless of system settings.

I had to re-format the date from UK to USA format (mm/dd/yyyy) at the beginning of the function. When the function has finished, it is automatically turned it back to sys settings.

You'd have thought VBA would automatically convert it to USA format to begin with... It does, but not very well. If both the month and date value are below 12, it can't tell which one's which. If one is greater than 12 it assumes that's the day value and the other must be the month so it can then automatically convert it to mm/dd/yyyy, do the calculations, and then convert it back to sys settings.

In order to prevent the weird stuff happening with days before the 12th of the month, you have to format it to mm/dd/yyyy before you ask VB to do anything with it.