Null date errors

[garyholc]

Hiya

Probably something really quick to fix but I have an if statement which does something if a certain date exists in a date field.

So for example


If dob = 1970 then
msgbox "correct"
else
msgbox "incorrect"
endif


This works fine when a date exists in the date field, but brings up "invalid use of Null" error. The code is in the On_Open section of the form.

I tried adding...

If dob is not null and dob = 1970 then....


But this then brings up "Object required" error...


Any ideas?

 

[Rabbie]

Hiya

Probably something really quick to fix but I have an if statement which does something if a certain date exists in a date field.

So for example


If dob = 1970 then
msgbox "correct"
else
msgbox "incorrect"
endif


This works fine when a date exists in the date field, but brings up "invalid use of Null" error. The code is in the On_Open section of the form.

I tried adding...

If dob is not null and dob = 1970 then....


But this then brings up "Object required" error...


Any ideas?

Try something like

  If NZ(dob,0) = 1970 then
    msgbox "correct"
  else
     msgbox "incorrect"
endif

 

[garyholc]

Cheers for the prompt reply but unfortunately same error (invalid use of null)

Code is:

If Nz(Year(PASSIVE_START_DATE), 0) = 1899 Then
Me.PASSIVE_START_DATE.BackColor = 0
Else
Me.PASSIVE_START_DATE.BackColor = 16777215
End If

Basically, if a date is not required the user wants to show this. So I decided to use an old date (30/12/1899) and when this happens, the box turns black. Works fine using command buttons to choose Required/Not Required for the date and I can produce reports highlighting where the date is not required, however, I want the form to turn the fields black if the date 30/12/1899 exists. I just chose the year() function so I didnt have to type the full date in.

 

[garyholc]

Dont know why I didnt think of this before but I have fixed it. I re-wrote it like this


if isnull(dob) then
<change cell color)
else
if year(dob) = 1970 then
<change cell color>
endif
endif



Thanks anyway!!!