Functions return #ERROR, null values

[Lifeseeker]

Hi,

I have following code:

Option Compare Database
Option Explicit
Public Function AssignWeeklyDisch(DischargeDate As Date) As Integer
'starts assigning weeklys
If DischargeDate.Value = "" Then
AssignWeeklyDisch = 100
End If
If DischargeDate >= #4/1/2012# And DischargeDate < #4/8/2012# Then
AssignWeeklyDisch = 1
End If

The code is placed in a standard module, and I am invoking it from a query. like this:

test: AssignWeeklyDisch([DischargeDate])

so i'm getting an erorr saying Visual Basic editor has a syntax error. The discharge Date in the table contains empty values. Maybe this is why?

How should I fix this problem? I also tried this:

If IsNull(DischargeDate) = "" Then
AssignWeeklyDisch = 100
End If

But it's not working.

 

[pbaldy]

You'd also have to change

Public Function AssignWeeklyDisch(DischargeDate As Date) As Integer

to

Public Function AssignWeeklyDisch(DischargeDate As Variant) As Integer

as a Date variable can't take a Null.

 

[JHB]

Or you can check it before you call the function.

test: Iif(Not IsNull([DischargeDate]); AssignWeeklyDisch([DischargeDate]))

 

[Lifeseeker]

Or you can check it before you call the function.

test: Iif(Not IsNull([DischargeDate]); AssignWeeklyDisch([DischargeDate]))

Thank you and I got it working!