function args in a query

I'm getting an error when using a function in a calculated field of my query.

In the query there's a field call [DateWorked], it get's it data from a field in a table that stores short dates. There's a between statement in it's criteria.

The calculated field that generates the error is :

MissedHours: dayMissedHours([DateWorked])

When I call the function in the immediate window of the VB Builder 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. I don't think [DateWorked] is in date format by the time it get's passed to the function.

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

Any other ideas on how I can ensure that [DateWorked] is a date before it's passed to the function would be welcome.
 
If your [DateWorked] is null then it will throw the error. You need to check to see if it is null and either pass it a predetermined date of your choosing, or use the NZ function which will send it a date of December 30, 1899.
 
Tyry concatenating the # sign eg

MissedHours: dayMissedHours("#" & [DateWorked] & "#")
 
Tyry concatenating the # sign eg

MissedHours: dayMissedHours("#" & [DateWorked] & "#")

Thanks, nearly solves it. It still evaluates it as '#error' but it doesn't show an 'invalid use of null' anymore. And it doesn't debug my code any more. Now I just have to work out whats causing this new error.

Thanks again.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom