Invalid use of null on report

F

fsck!

I am getting an 'Invalid use of Null' when launching a report on my
database. I have reading about the use of "NZ" while looking up
similar issues in this group, I am just not sure as to how to make use
of it and where.


I have been inspecting the report (as best as I could with my limited
access kowledge) and I see there is a text box with the following
control source:

=IIf([InitialRecurrent]=1,"I","R")

I found the source in one of the tables and noticed some of them where
empty so I added values to the 'initialrecurrent' field in the table
but that did not seem to make a difference. Can I make use of NZ
anywhere to make this error go away?

Also, the report code is as follows:

----------------------REPORT---------------------------------------

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'GSI
If DatePart("m", Date) = DatePart("M", Me.GSI) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.GSI) Then
Me.GSI.Visible = True
Else
Me.GSI.Visible = False
End If

'SEC
If DatePart("m", Date) = DatePart("M", Me.SEC) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.SEC) Then
Me.SEC.Visible = True
Else
Me.SEC.Visible = False
End If

'SYS
If DatePart("m", Date) = DatePart("M", Me.SYS) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.SYS) Then
Me.SYS.Visible = True
Else
Me.SYS.Visible = False
End If

'SOPS
If DatePart("m", Date) = DatePart("M", Me.SOPS) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.SOPS) Then
Me.SOPS.Visible = True
Else
Me.SOPS.Visible = False
End If

'SPRO
If DatePart("m", Date) = DatePart("M", Me.SPRO) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.SPRO) Then
Me.SPRO.Visible = True
Else
Me.SPRO.Visible = False
End If

'SOBS
If DatePart("m", Date) = DatePart("M", Me.SOBS) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.SOBS) Then
Me.SOBS.Visible = True
Else
Me.SOBS.Visible = False
End If

'SSPC
If DatePart("m", Date) = DatePart("M", Me.SSPC) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.SSPC) Then
Me.SSPC.Visible = True
Else
Me.SSPC.Visible = False
End If

'WLKT
If DatePart("m", Date) = DatePart("M", Me.WLKT) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.WLKT) Then
Me.WLKT.Visible = True
Else
Me.WLKT.Visible = False
End If

'WLKC
If DatePart("m", Date) = DatePart("M", Me.WLKC) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.WLKC) Then
Me.WLKC.Visible = True
Else
Me.WLKC.Visible = False
End If

'DIF1
If DatePart("m", Date) = DatePart("M", Me.DIF1) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.DIF1) Then
Me.DIF1.Visible = True
Else
Me.DIF1.Visible = False
End If

'DIF2
If DatePart("m", Date) = DatePart("M", Me.DIF2) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.DIF2) Then
Me.DIF2.Visible = True
Else
Me.DIF2.Visible = False
End If

'TCE
If DatePart("m", Date) = DatePart("M", Me.TCE) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.TCE) Then
Me.TCE.Visible = True
Else
Me.TCE.Visible = False
End If

'TCEF
If DatePart("m", Date) = DatePart("M", Me.TCEF) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.TCEF) Then
Me.TCEF.Visible = True
Else
Me.TCEF.Visible = False
End If

'TCED
If DatePart("m", Date) = DatePart("M", Me.TCED) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.TCED) Then
Me.TCED.Visible = True
Else
Me.TCED.Visible = False
End If

'TCED
If DatePart("m", Date) = DatePart("M", Me.TCED) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.TCED) Then
Me.TCED.Visible = True
Else
Me.TCED.Visible = False
End If

'APRO
If DatePart("m", Date) = DatePart("M", Me.APRO) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.APRO) Then
Me.APRO.Visible = True
Else
Me.APRO.Visible = False
End If

'ASPC
If DatePart("m", Date) = DatePart("M", Me.ASPC) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.ASPC) Then
Me.ASPC.Visible = True
Else
Me.ASPC.Visible = False
End If

-----------------------------------------END
REPORT----------------------------------------------
 
D

Duane Hookom

I expect the issue is with your code and one or more the dates being Null.
You could probably remove all the code and change control sources to
something like:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

=IIf(Format(Date(),"yyyymm") = Format(Nz(GSI,#1/1/1900#),"yyyymm"),
[GIS],Null)

--
Duane Hookom
Microsoft Access MVP


fsck! said:
I am getting an 'Invalid use of Null' when launching a report on my
database. I have reading about the use of "NZ" while looking up
similar issues in this group, I am just not sure as to how to make use
of it and where.


I have been inspecting the report (as best as I could with my limited
access kowledge) and I see there is a text box with the following
control source:

=IIf([InitialRecurrent]=1,"I","R")

I found the source in one of the tables and noticed some of them where
empty so I added values to the 'initialrecurrent' field in the table
but that did not seem to make a difference. Can I make use of NZ
anywhere to make this error go away?

Also, the report code is as follows:

----------------------REPORT---------------------------------------

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'GSI
If DatePart("m", Date) = DatePart("M", Me.GSI) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.GSI) Then
Me.GSI.Visible = True
Else
Me.GSI.Visible = False
End If

'SEC
If DatePart("m", Date) = DatePart("M", Me.SEC) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.SEC) Then
Me.SEC.Visible = True
Else
Me.SEC.Visible = False
End If

'SYS
If DatePart("m", Date) = DatePart("M", Me.SYS) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.SYS) Then
Me.SYS.Visible = True
Else
Me.SYS.Visible = False
End If

'SOPS
If DatePart("m", Date) = DatePart("M", Me.SOPS) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.SOPS) Then
Me.SOPS.Visible = True
Else
Me.SOPS.Visible = False
End If

'SPRO
If DatePart("m", Date) = DatePart("M", Me.SPRO) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.SPRO) Then
Me.SPRO.Visible = True
Else
Me.SPRO.Visible = False
End If

'SOBS
If DatePart("m", Date) = DatePart("M", Me.SOBS) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.SOBS) Then
Me.SOBS.Visible = True
Else
Me.SOBS.Visible = False
End If

'SSPC
If DatePart("m", Date) = DatePart("M", Me.SSPC) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.SSPC) Then
Me.SSPC.Visible = True
Else
Me.SSPC.Visible = False
End If

'WLKT
If DatePart("m", Date) = DatePart("M", Me.WLKT) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.WLKT) Then
Me.WLKT.Visible = True
Else
Me.WLKT.Visible = False
End If

'WLKC
If DatePart("m", Date) = DatePart("M", Me.WLKC) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.WLKC) Then
Me.WLKC.Visible = True
Else
Me.WLKC.Visible = False
End If

'DIF1
If DatePart("m", Date) = DatePart("M", Me.DIF1) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.DIF1) Then
Me.DIF1.Visible = True
Else
Me.DIF1.Visible = False
End If

'DIF2
If DatePart("m", Date) = DatePart("M", Me.DIF2) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.DIF2) Then
Me.DIF2.Visible = True
Else
Me.DIF2.Visible = False
End If

'TCE
If DatePart("m", Date) = DatePart("M", Me.TCE) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.TCE) Then
Me.TCE.Visible = True
Else
Me.TCE.Visible = False
End If

'TCEF
If DatePart("m", Date) = DatePart("M", Me.TCEF) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.TCEF) Then
Me.TCEF.Visible = True
Else
Me.TCEF.Visible = False
End If

'TCED
If DatePart("m", Date) = DatePart("M", Me.TCED) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.TCED) Then
Me.TCED.Visible = True
Else
Me.TCED.Visible = False
End If

'TCED
If DatePart("m", Date) = DatePart("M", Me.TCED) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.TCED) Then
Me.TCED.Visible = True
Else
Me.TCED.Visible = False
End If

'APRO
If DatePart("m", Date) = DatePart("M", Me.APRO) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.APRO) Then
Me.APRO.Visible = True
Else
Me.APRO.Visible = False
End If

'ASPC
If DatePart("m", Date) = DatePart("M", Me.ASPC) And DatePart("yyyy",
Date) = DatePart("yyyy", Me.ASPC) Then
Me.ASPC.Visible = True
Else
Me.ASPC.Visible = False
End If

-----------------------------------------END
REPORT----------------------------------------------
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

Type mismatch in Function 5
Should be Visible for 180 days 4
Invoice number 5
Function for week number 5
Query returns empty columns 2
Changing appointment to AllDayEvent,. Recipients get meeting request for specific tim 1
Nested loop 3
Invalid use of null 3

Top