Error 2427 (1 Viewer)

[PatAccess]

Hello Guys,

I have a cmd button which opens a Report from selecting a name from a comboBox.
I've formatted to report to give me data within 30 days.

However if one of those names does not have anything within those 30 days, it gives me error message 2427 (Invalid use of Null) and takes me to debug it

The error highlights t = CDate(Me.Expires)

I believe I am supposed to enter IsNull somewhere but where?

Also, how do I enter a MsgBox if there is nothing to see?

Thank you for the help

Here is my code: :banghead:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.LicName.Visible = False
Me.State.Visible = False
Dim t As Date
t = CDate(Me.Expires)

If Me.LicName = "State PE License" Then
Me.LicText = Me.State
Else
Me.LicText = Me.LicName
End If
If Date > t And t > DateTime.DateAdd("d", 30, Date) Then
Me.Expires.ForeColor = vbRed
Me.Expires.FontBold = True
Else
Me.Expires.ForeColor = vbBlack
Me.Expires.FontBold = False
End If

 

[pbaldy]

The function will barf on a Null, so perhaps:

t = CDate(Nz(Me.Expires, 0))

which would give you a date in 1899, or you can code for it:

If IsNull(Me.Expires) Then

 

[MarkK]

Check out the Nz() function.

t = CDate(Nz(Me.Expires, #1/1/2000#))

Alternatively, you could use the IsDate() function, like...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   Dim t As Date

   If IsDate(Me.Expires) Then
      t = Me.Expires
      ...
   End If
End Sub

...and thereby don't run the operation if Me.Expires is not a date.

hth
Mark

 

[MarkK]

I tried it in the immediate pane...

? Nz(Null, "barfed")
 barfed

Lol, hey Paul

 

[PatAccess]

They both worked. However it is returning all the date until next year
But what if I wanted it to be within 30 days only? and want a MsgBox if nothing is available?
That's what I'm stuck
Thank you Guys for your help!

 

[ashleedawg]

But what if I wanted it to be within 30 days only? and want a MsgBox if nothing is available?
That's what I'm stuck

If Date > t And t > DateTime.DateAdd("d", 30, Date) Then

Your formula will never be 'True' - I think you have the signs backwards. If you would you like [Expires] to be Red & Bold when it's between today and 30 days from today, the code should be: If Date < t And t < DateAdd("d", 30, Date) (or <= to include today)

If you would like this code to run each time a new record is loaded, you might be better off putting it in the form's On Load event instead of the detail section's Format event.

Those points plus the "IsDate" suggestion, and the msgbox you want, would give you:

Private Sub Form_Load() 'runs when you load a new record
Me.LicName.Visible = False
Me.State.Visible = False
Dim t As Date

If Not IsDate(Me.Expires) Then
    MsgBox "No Date Found!" 'give an error
    Exit Sub 'don't do anything else
End If

t = CDate(Me.Expires)

If Me.LicName = "State PE License" Then
    Me.LicText = Me.State
Else
    Me.LicText = Me.LicName
End If

If Date <= t And t <= DateTime.DateAdd("d", 30, Date) Then
    'if within 30 days from today
    Me.Expires.ForeColor = vbRed
    Me.Expires.FontBold = True
Else
    'not within 30 days
    Me.Expires.ForeColor = vbBlack
    Me.Expires.FontBold = False
End If

End Sub

You were close

 

[pbaldy]

I tried it in the immediate pane...

? Nz(Null, "barfed")
 barfed

Lol, hey Paul

See, I said it would barf.

 

[PatAccess]

Ok so it works but now I have to click the Ok button 5 times (on the message box) before it disappears and then it opens the report anyway (So it's not doing "nothin") Why is it doing that?

 

[pbaldy]

Sounds like a parameter prompt. The text will tell you what it's looking for. Likely something is spelled wrong.