Invalid use of Null & runtime error 94

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

melwester - 08 Apr 2005 14:19 GMT

Hi Ken,

Still trying to get my dates to work. With the code you supplied before
when I test it I do dates less then 2002 and more than 2005 (i.e 2000 or
2006) this works fine. But when I try to erase the date to go to the next
field I get "Run time error 94 - Invalid use of Null message".

The user is going to have to be able to delete the date that is in the date
field and tab to the next field.

Here is the code:

Private Sub CADLDL_Date_Text_BeforeUpdate(Cancel As Integer)

Dim TextDate As Date
TextDate = DateSerial(Right(Me!CAD_LDL_Date.Value, 4), _
Left(Me!CAD_LDL_Date.Value, 2), _
Mid(Me!CAD_LDL_Date.Value, 3, 2))
Select Case TextDate
Case #10/1/2002# To #9/30/2005#
'do nothing'

Case Else
MsgBox "You have entered an invalid date."
Cancel = True

End Select
End Sub

Someone had mention changing the Cancel to = False, tried this and it still
doesn't work. Have any ideas on this?

Thanks.

Reply to this Message

Wayne Morgan - 08 Apr 2005 14:28 GMT

> TextDate = DateSerial(Right(Me!CAD_LDL_Date.Value, 4), _
> Left(Me!CAD_LDL_Date.Value, 2), _
> Mid(Me!CAD_LDL_Date.Value, 3, 2))

This is probably where your error message is coming from. You are trying to
assign a Null value to a date variable. One option would be to substitute a
value if the result is Null.

Example:
TextDate = Nz(DateSerial(Right(Me!CAD_LDL_Date.Value, 4), _
Left(Me!CAD_LDL_Date.Value, 2), _
Mid(Me!CAD_LDL_Date.Value, 3, 2)), Date)

This will pass today's date if the value is Null. Another option would be to
exit the routine if the textbox is Null.

Example:
If IsNull(Me!CAD_LDL_Date) Then Exit Sub

This is probably the better option if you want to allow the user to clear
the textbox and continue. You would place this before the "TextDate ="
statement.

Signature

Wayne Morgan
MS Access MVP

> Hi Ken,
>
[quoted text clipped - 31 lines]
>
> Thanks.

Reply to this Message

melwester - 08 Apr 2005 14:37 GMT

Thank you!!!!! This works. I used "If IsNull(Me!CAD_LDL_Date) Then Exit
Sub" before TextDate =

Thank you so much!!!

> > TextDate = DateSerial(Right(Me!CAD_LDL_Date.Value, 4), _
> > Left(Me!CAD_LDL_Date.Value, 2), _
[quoted text clipped - 54 lines]
> >
> > Thanks.

Reply to this Message