Null gives error

D

dbl

Hi I have the following code in my Text330 field
=LiscYrs([LicHeldDate]) & " Yrs " & AgeMonths([LicHeldDate]) & " Mths"
if no date is entered I get an #Error how do I set it up so that if me
LicHeldDate Field is blank my Text330 field is blank.
It works correctly when a date is entered.

I have copied in the Module codes for LiscYrs and AgeMonths

Function AgeMonths(ByVal StartDate As String) As Integer



Dim tAge As Double
tAge = (DateDiff("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function


Function LiscYrs(varTestDate As Variant) As Variant
Dim varYrs As Variant
If IsNull(varTestDate) Then
varYrs = Null
Else
varYrs = DateDiff("yyyy", varTestDate, Date)
If Date < DateSerial(Year(Date), Month(varTestDate), _
Day(varTestDate)) Then
varYrs = varYrs - 1
End If
End If
LiscYrs = varYrs
End Function

The above code does what I need in a different part of the db and works
correctly.

Any help would be much appreciated.

Bob
 
G

Guest

You test for the IsNull condition in the LiscYrs() function but not in
AgeMonths(). Since the LicHeldDate is the argument to each of them, try
adding the null condition test to AgeMonths also.
 
B

Bill

=LiscYrsNZ(([LicHeldDate]),0) & " Yrs " & AgeMonthsNZ(([LicHeldDate]),0) & "
Mths"
THT
Bill
 
D

dbl

Sorry but I don't know how to do it.

Chaim said:
You test for the IsNull condition in the LiscYrs() function but not in
AgeMonths(). Since the LicHeldDate is the argument to each of them, try
adding the null condition test to AgeMonths also.
--
Chaim


dbl said:
Hi I have the following code in my Text330 field
=LiscYrs([LicHeldDate]) & " Yrs " & AgeMonths([LicHeldDate]) & " Mths"
if no date is entered I get an #Error how do I set it up so that if me
LicHeldDate Field is blank my Text330 field is blank.
It works correctly when a date is entered.

I have copied in the Module codes for LiscYrs and AgeMonths

Function AgeMonths(ByVal StartDate As String) As Integer



Dim tAge As Double
tAge = (DateDiff("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function


Function LiscYrs(varTestDate As Variant) As Variant
Dim varYrs As Variant
If IsNull(varTestDate) Then
varYrs = Null
Else
varYrs = DateDiff("yyyy", varTestDate, Date)
If Date < DateSerial(Year(Date), Month(varTestDate), _
Day(varTestDate)) Then
varYrs = varYrs - 1
End If
End If
LiscYrs = varYrs
End Function

The above code does what I need in a different part of the db and works
correctly.

Any help would be much appreciated.

Bob
Click to expand...
Click to expand...
 
D

dbl

Bill that produces the #Name error.

Bob
Bill said:
=LiscYrsNZ(([LicHeldDate]),0) & " Yrs " & AgeMonthsNZ(([LicHeldDate]),0) &
"
Mths"
THT
Bill
dbl said:
Hi I have the following code in my Text330 field
=LiscYrs([LicHeldDate]) & " Yrs " & AgeMonths([LicHeldDate]) & " Mths"
if no date is entered I get an #Error how do I set it up so that if me
LicHeldDate Field is blank my Text330 field is blank.
It works correctly when a date is entered.

I have copied in the Module codes for LiscYrs and AgeMonths

Function AgeMonths(ByVal StartDate As String) As Integer



Dim tAge As Double
tAge = (DateDiff("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function


Function LiscYrs(varTestDate As Variant) As Variant
Dim varYrs As Variant
If IsNull(varTestDate) Then
varYrs = Null
Else
varYrs = DateDiff("yyyy", varTestDate, Date)
If Date < DateSerial(Year(Date), Month(varTestDate), _
Day(varTestDate)) Then
varYrs = varYrs - 1
End If
End If
LiscYrs = varYrs
End Function

The above code does what I need in a different part of the db and works
correctly.

Any help would be much appreciated.

Bob
Click to expand...
Click to expand...
 
D

dbl

I have entered the following code but still get the #Error when there is no
date in the LicHeldDate field.

Function LiscMths(varTestDate As String) As Integer

Dim varMths As Double
If IsNull(varTestDate) Then
varMths = Null
Else
varMths = (DateDiff("m", varTestDate, Date))
If (DatePart("d", varTestDate) > DatePart("d", Date)) Then
varMths = varMths - 1
End If
If varMths < 0 Then
varMths = varMths + 1
End If
End If
LiscMths = CInt(varMths Mod 12)

End Function

This is now the code in the Text330 Field =LiscYrs([LicHeldDate]) & " Yrs "
& LiscMths([LicHeldDate]) & " Mths"

It still works correctly until you have no data in field Text330 which then
produces the same error on my reports.

Bob
Chaim said:
You test for the IsNull condition in the LiscYrs() function but not in
AgeMonths(). Since the LicHeldDate is the argument to each of them, try
adding the null condition test to AgeMonths also.
--
Chaim


dbl said:
Hi I have the following code in my Text330 field
=LiscYrs([LicHeldDate]) & " Yrs " & AgeMonths([LicHeldDate]) & " Mths"
if no date is entered I get an #Error how do I set it up so that if me
LicHeldDate Field is blank my Text330 field is blank.
It works correctly when a date is entered.

I have copied in the Module codes for LiscYrs and AgeMonths

Function AgeMonths(ByVal StartDate As String) As Integer



Dim tAge As Double
tAge = (DateDiff("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function


Function LiscYrs(varTestDate As Variant) As Variant
Dim varYrs As Variant
If IsNull(varTestDate) Then
varYrs = Null
Else
varYrs = DateDiff("yyyy", varTestDate, Date)
If Date < DateSerial(Year(Date), Month(varTestDate), _
Day(varTestDate)) Then
varYrs = varYrs - 1
End If
End If
LiscYrs = varYrs
End Function

The above code does what I need in a different part of the db and works
correctly.

Any help would be much appreciated.

Bob
Click to expand...
Click to expand...
 
B

Bill

Oops! Try this........
=LiscYrs(NZ([LicHeldDate]),0) & " Yrs " & AgeMonths(NZ([LicHeldDate]),0) &
Click to expand...
"Mths"

dbl said:
Bill that produces the #Name error.

Bob
Bill said:
=LiscYrsNZ(([LicHeldDate]),0) & " Yrs " & AgeMonthsNZ(([LicHeldDate]),0) &
"
Mths"
THT
Bill
dbl said:
Hi I have the following code in my Text330 field
=LiscYrs([LicHeldDate]) & " Yrs " & AgeMonths([LicHeldDate]) & " Mths"
if no date is entered I get an #Error how do I set it up so that if me
LicHeldDate Field is blank my Text330 field is blank.
It works correctly when a date is entered.

I have copied in the Module codes for LiscYrs and AgeMonths

Function AgeMonths(ByVal StartDate As String) As Integer



Dim tAge As Double
tAge = (DateDiff("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function


Function LiscYrs(varTestDate As Variant) As Variant
Dim varYrs As Variant
If IsNull(varTestDate) Then
varYrs = Null
Else
varYrs = DateDiff("yyyy", varTestDate, Date)
If Date < DateSerial(Year(Date), Month(varTestDate), _
Day(varTestDate)) Then
varYrs = varYrs - 1
End If
End If
LiscYrs = varYrs
End Function

The above code does what I need in a different part of the db and works
correctly.

Any help would be much appreciated.

Bob
Click to expand...
Click to expand...
Click to expand...
 
B

Brendan Reynolds

You've defined varTestDate as String, and a String can't be Null, only a
Variant can contain a Null value.

--
Brendan Reynolds (MVP)

dbl said:
I have entered the following code but still get the #Error when there is no
date in the LicHeldDate field.

Function LiscMths(varTestDate As String) As Integer

Dim varMths As Double
If IsNull(varTestDate) Then
varMths = Null
Else
varMths = (DateDiff("m", varTestDate, Date))
If (DatePart("d", varTestDate) > DatePart("d", Date)) Then
varMths = varMths - 1
End If
If varMths < 0 Then
varMths = varMths + 1
End If
End If
LiscMths = CInt(varMths Mod 12)

End Function

This is now the code in the Text330 Field =LiscYrs([LicHeldDate]) & " Yrs
" & LiscMths([LicHeldDate]) & " Mths"

It still works correctly until you have no data in field Text330 which
then produces the same error on my reports.

Bob
Chaim said:
You test for the IsNull condition in the LiscYrs() function but not in
AgeMonths(). Since the LicHeldDate is the argument to each of them, try
adding the null condition test to AgeMonths also.
--
Chaim


dbl said:
Hi I have the following code in my Text330 field
=LiscYrs([LicHeldDate]) & " Yrs " & AgeMonths([LicHeldDate]) & " Mths"
if no date is entered I get an #Error how do I set it up so that if me
LicHeldDate Field is blank my Text330 field is blank.
It works correctly when a date is entered.

I have copied in the Module codes for LiscYrs and AgeMonths

Function AgeMonths(ByVal StartDate As String) As Integer



Dim tAge As Double
tAge = (DateDiff("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function


Function LiscYrs(varTestDate As Variant) As Variant
Dim varYrs As Variant
If IsNull(varTestDate) Then
varYrs = Null
Else
varYrs = DateDiff("yyyy", varTestDate, Date)
If Date < DateSerial(Year(Date), Month(varTestDate), _
Day(varTestDate)) Then
varYrs = varYrs - 1
End If
End If
LiscYrs = varYrs
End Function

The above code does what I need in a different part of the db and works
correctly.

Any help would be much appreciated.

Bob
Click to expand...
Click to expand...
Click to expand...
 
D

dbl

Hi I have change the code as below but still get an #Error or an error
message "Invalid use of Null" and it highlights the following line of code
"varMths = Null

Function LiscMths(varTestDate As Variant) As Variant

Dim varMths As Double
If IsNull(varTestDate) Then
varMths = Null
Else
varMths = (DateDiff("m", varTestDate, Date))
If (DatePart("d", varTestDate) > DatePart("d", Date)) Then
varMths = varMths - 1
End If
If varMths < 0 Then
varMths = varMths + 1
End If
End If
LiscMths = CInt(varMths Mod 12)

End Function

Bob
Brendan Reynolds said:
You've defined varTestDate as String, and a String can't be Null, only a
Variant can contain a Null value.

--
Brendan Reynolds (MVP)

dbl said:
I have entered the following code but still get the #Error when there is
no date in the LicHeldDate field.

Function LiscMths(varTestDate As String) As Integer

Dim varMths As Double
If IsNull(varTestDate) Then
varMths = Null
Else
varMths = (DateDiff("m", varTestDate, Date))
If (DatePart("d", varTestDate) > DatePart("d", Date)) Then
varMths = varMths - 1
End If
If varMths < 0 Then
varMths = varMths + 1
End If
End If
LiscMths = CInt(varMths Mod 12)

End Function

This is now the code in the Text330 Field =LiscYrs([LicHeldDate]) & " Yrs
" & LiscMths([LicHeldDate]) & " Mths"

It still works correctly until you have no data in field Text330 which
then produces the same error on my reports.

Bob
Chaim said:
You test for the IsNull condition in the LiscYrs() function but not in
AgeMonths(). Since the LicHeldDate is the argument to each of them, try
adding the null condition test to AgeMonths also.
--
Chaim


:

Hi I have the following code in my Text330 field
=LiscYrs([LicHeldDate]) & " Yrs " & AgeMonths([LicHeldDate]) & " Mths"
if no date is entered I get an #Error how do I set it up so that if me
LicHeldDate Field is blank my Text330 field is blank.
It works correctly when a date is entered.

I have copied in the Module codes for LiscYrs and AgeMonths

Function AgeMonths(ByVal StartDate As String) As Integer



Dim tAge As Double
tAge = (DateDiff("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function


Function LiscYrs(varTestDate As Variant) As Variant
Dim varYrs As Variant
If IsNull(varTestDate) Then
varYrs = Null
Else
varYrs = DateDiff("yyyy", varTestDate, Date)
If Date < DateSerial(Year(Date), Month(varTestDate), _
Day(varTestDate)) Then
varYrs = varYrs - 1
End If
End If
LiscYrs = varYrs
End Function

The above code does what I need in a different part of the db and works
correctly.

Any help would be much appreciated.

Bob
Click to expand...
Click to expand...
Click to expand...
 
D

dbl

Bill this comes up with the Error "The expression you have entered has a
function containing the wrong number of arguments"

Bob
Bill said:
Oops! Try this........
=LiscYrs(NZ([LicHeldDate]),0) & " Yrs " & AgeMonths(NZ([LicHeldDate]),0)
&
Click to expand...
"Mths"

dbl said:
Bill that produces the #Name error.

Bob
Bill said:
=LiscYrsNZ(([LicHeldDate]),0) & " Yrs " &
AgeMonthsNZ(([LicHeldDate]),0) &
"
Mths"
THT
Bill
Hi I have the following code in my Text330 field
=LiscYrs([LicHeldDate]) & " Yrs " & AgeMonths([LicHeldDate]) & " Mths"
if no date is entered I get an #Error how do I set it up so that if me
LicHeldDate Field is blank my Text330 field is blank.
It works correctly when a date is entered.

I have copied in the Module codes for LiscYrs and AgeMonths

Function AgeMonths(ByVal StartDate As String) As Integer



Dim tAge As Double
tAge = (DateDiff("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function


Function LiscYrs(varTestDate As Variant) As Variant
Dim varYrs As Variant
If IsNull(varTestDate) Then
varYrs = Null
Else
varYrs = DateDiff("yyyy", varTestDate, Date)
If Date < DateSerial(Year(Date), Month(varTestDate), _
Day(varTestDate)) Then
varYrs = varYrs - 1
End If
End If
LiscYrs = varYrs
End Function

The above code does what I need in a different part of the db and
works
correctly.

Any help would be much appreciated.

Bob
Click to expand...
Click to expand...
Click to expand...
 
B

Brendan Reynolds

As I explained in my previous post, only a Variant can contain a Null value.
varMths is declared as Double. Note, though, that simply changing varMths to
Variant isn't going to make this code work either, as the final line of
code - LiscMths = CInt(varMths Mod 12) - is outside the outer If ... End If,
and will still be executed if the value is Null. This will raise an error
because a Null value is not a valid argument for the CInt() function. The
CInt() function appears to be redundant here as far as I can tell anyway, as
the result of a Mod is always either a whole number or a Null, any
fractional part of the number is truncated (see the help topic on the Mod
Operator for details).

What I would suggest would be something more like the following, which both
changes the data type of varMths to Variant, and moves the Mod operation
inside the outer If ... End If.

Function LiscMths(varTestDate As Variant) As Variant

Dim varMths As Variant

If IsNull(varTestDate) Then
LiscMths = Null
Else
varMths = (DateDiff("m", varTestDate, Date))
If (DatePart("d", varTestDate) > DatePart("d", Date)) Then
varMths = varMths - 1
End If
If varMths < 0 Then
varMths = varMths + 1
End If
LiscMths = varMths Mod 12
End If

End Function

--
Brendan Reynolds (MVP)

dbl said:
Hi I have change the code as below but still get an #Error or an error
message "Invalid use of Null" and it highlights the following line of code
"varMths = Null

Function LiscMths(varTestDate As Variant) As Variant

Dim varMths As Double
If IsNull(varTestDate) Then
varMths = Null
Else
varMths = (DateDiff("m", varTestDate, Date))
If (DatePart("d", varTestDate) > DatePart("d", Date)) Then
varMths = varMths - 1
End If
If varMths < 0 Then
varMths = varMths + 1
End If
End If
LiscMths = CInt(varMths Mod 12)

End Function

Bob
Brendan Reynolds said:
You've defined varTestDate as String, and a String can't be Null, only a
Variant can contain a Null value.

--
Brendan Reynolds (MVP)

dbl said:
I have entered the following code but still get the #Error when there is
no date in the LicHeldDate field.

Function LiscMths(varTestDate As String) As Integer

Dim varMths As Double
If IsNull(varTestDate) Then
varMths = Null
Else
varMths = (DateDiff("m", varTestDate, Date))
If (DatePart("d", varTestDate) > DatePart("d", Date)) Then
varMths = varMths - 1
End If
If varMths < 0 Then
varMths = varMths + 1
End If
End If
LiscMths = CInt(varMths Mod 12)

End Function

This is now the code in the Text330 Field =LiscYrs([LicHeldDate]) & "
Yrs " & LiscMths([LicHeldDate]) & " Mths"

It still works correctly until you have no data in field Text330 which
then produces the same error on my reports.

Bob
You test for the IsNull condition in the LiscYrs() function but not in
AgeMonths(). Since the LicHeldDate is the argument to each of them, try
adding the null condition test to AgeMonths also.
--
Chaim


:

Hi I have the following code in my Text330 field
=LiscYrs([LicHeldDate]) & " Yrs " & AgeMonths([LicHeldDate]) & " Mths"
if no date is entered I get an #Error how do I set it up so that if me
LicHeldDate Field is blank my Text330 field is blank.
It works correctly when a date is entered.

I have copied in the Module codes for LiscYrs and AgeMonths

Function AgeMonths(ByVal StartDate As String) As Integer



Dim tAge As Double
tAge = (DateDiff("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function


Function LiscYrs(varTestDate As Variant) As Variant
Dim varYrs As Variant
If IsNull(varTestDate) Then
varYrs = Null
Else
varYrs = DateDiff("yyyy", varTestDate, Date)
If Date < DateSerial(Year(Date), Month(varTestDate), _
Day(varTestDate)) Then
varYrs = varYrs - 1
End If
End If
LiscYrs = varYrs
End Function

The above code does what I need in a different part of the db and
works
correctly.

Any help would be much appreciated.

Bob
Click to expand...
Click to expand...
Click to expand...
 
D

dbl

Brendan, thanks for help that's sorted out the problem.

Thanks Bob
Brendan Reynolds said:
As I explained in my previous post, only a Variant can contain a Null
value. varMths is declared as Double. Note, though, that simply changing
varMths to Variant isn't going to make this code work either, as the final
line of code - LiscMths = CInt(varMths Mod 12) - is outside the outer If
... End If, and will still be executed if the value is Null. This will
raise an error because a Null value is not a valid argument for the CInt()
function. The CInt() function appears to be redundant here as far as I can
tell anyway, as the result of a Mod is always either a whole number or a
Null, any fractional part of the number is truncated (see the help topic
on the Mod Operator for details).

What I would suggest would be something more like the following, which
both changes the data type of varMths to Variant, and moves the Mod
operation inside the outer If ... End If.

Function LiscMths(varTestDate As Variant) As Variant

Dim varMths As Variant

If IsNull(varTestDate) Then
LiscMths = Null
Else
varMths = (DateDiff("m", varTestDate, Date))
If (DatePart("d", varTestDate) > DatePart("d", Date)) Then
varMths = varMths - 1
End If
If varMths < 0 Then
varMths = varMths + 1
End If
LiscMths = varMths Mod 12
End If

End Function

--
Brendan Reynolds (MVP)

dbl said:
Hi I have change the code as below but still get an #Error or an error
message "Invalid use of Null" and it highlights the following line of
code "varMths = Null

Function LiscMths(varTestDate As Variant) As Variant

Dim varMths As Double
If IsNull(varTestDate) Then
varMths = Null
Else
varMths = (DateDiff("m", varTestDate, Date))
If (DatePart("d", varTestDate) > DatePart("d", Date)) Then
varMths = varMths - 1
End If
If varMths < 0 Then
varMths = varMths + 1
End If
End If
LiscMths = CInt(varMths Mod 12)

End Function

Bob
Brendan Reynolds said:
You've defined varTestDate as String, and a String can't be Null, only a
Variant can contain a Null value.

--
Brendan Reynolds (MVP)

I have entered the following code but still get the #Error when there is
no date in the LicHeldDate field.

Function LiscMths(varTestDate As String) As Integer

Dim varMths As Double
If IsNull(varTestDate) Then
varMths = Null
Else
varMths = (DateDiff("m", varTestDate, Date))
If (DatePart("d", varTestDate) > DatePart("d", Date)) Then
varMths = varMths - 1
End If
If varMths < 0 Then
varMths = varMths + 1
End If
End If
LiscMths = CInt(varMths Mod 12)

End Function

This is now the code in the Text330 Field =LiscYrs([LicHeldDate]) & "
Yrs " & LiscMths([LicHeldDate]) & " Mths"

It still works correctly until you have no data in field Text330 which
then produces the same error on my reports.

Bob
You test for the IsNull condition in the LiscYrs() function but not in
AgeMonths(). Since the LicHeldDate is the argument to each of them,
try
adding the null condition test to AgeMonths also.
--
Chaim


:

Hi I have the following code in my Text330 field
=LiscYrs([LicHeldDate]) & " Yrs " & AgeMonths([LicHeldDate]) & "
Mths"
if no date is entered I get an #Error how do I set it up so that if
me
LicHeldDate Field is blank my Text330 field is blank.
It works correctly when a date is entered.

I have copied in the Module codes for LiscYrs and AgeMonths

Function AgeMonths(ByVal StartDate As String) As Integer



Dim tAge As Double
tAge = (DateDiff("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If

AgeMonths = CInt(tAge Mod 12)

End Function


Function LiscYrs(varTestDate As Variant) As Variant
Dim varYrs As Variant
If IsNull(varTestDate) Then
varYrs = Null
Else
varYrs = DateDiff("yyyy", varTestDate, Date)
If Date < DateSerial(Year(Date), Month(varTestDate), _
Day(varTestDate)) Then
varYrs = varYrs - 1
End If
End If
LiscYrs = varYrs
End Function

The above code does what I need in a different part of the db and
works
correctly.

Any help would be much appreciated.

Bob
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error 3021 No current record 5
Age Calculation 3
basage modular 4
Blocking Error 3021 No current record 1
Calc Date Years Month Days from DOB to DOD 2
Age from Dob 12
Invalid use of Null error 2
Checking Date for Null 14

Top