Before Update Code error

[RBDU]

Hi all! thanking anyone for a reply:
Was A97 came into work & IT updated access to A2003.
Sob! Sob!

Anyway I have this code problem with the before update. Code is:

...................................................
Private Sub Command286_Click()
If me.Call_off > Date + 1 Then
Msgbox "This date is in the future, please redo!"
Me.Call_off.undo
Cancel = True

ElseIf Year (Nz (Me.Call_off)) < Year ((Now) - 1) Then
Msgbox "This date is for a previous year, please redo"
Me.Call_off.undo
Cancel = True

End if
End Sub
............................................................

Future date & time section works except for a date in a previous year does
not.

If I put a date (date & time format) in for this year it will except it,
but if I go to delete the date & time data (to correct it) it tells me the
date is for a previous year, but it is not.

Any ideas
Peter

 

[Allen Browne]

You used Nz() without specfying the value to use when Call_off is null, so
the Year() of that value will be calculated as 1899.

Drop the Nz():
ElseIf Year(Me.Call_off)) < Year (Date - 1) Then

 

[RBDU]

Your blood is worth bottling.
Regards Peter

You used Nz() without specfying the value to use when Call_off is null, so
the Year() of that value will be calculated as 1899.

Drop the Nz():
ElseIf Year(Me.Call_off)) < Year (Date - 1) Then

 

[RBDU]

I did that, but when I delete the date set in the past Access gives me an
error message, "Invalid use of Null"

Any ideas
Peter

 

[Allen Browne]

Which line gives that error?

Do you need to test for null first, e.g.:
If Not IsNull(Me.Call_off) Then

 

[RBDU]

The field will be null in the beginning in order for the user to input data.

The code traps that the date/time field is in the past. But on deleting the
date prior to this year to correct user error, the error message comes up.

Peter

 

[Allen Browne]

Which line gives the error?

Can you need to use IsNull() to avoid that condition?

 

[RBDU]

It must be the line that contained the Nz. Can you suggest the complete code
to avoid the error.

Regards
Peter

 

[Allen Browne]

I don't follow.

When the code runs, it errors. There is no error handler, so if you choose
Debug, Access will take you to the VBA window with the problem line
highlighed in yellow. You will then know which line is generating the error.

You can then work on finding a solution for the error.

I do not understand the error. Year() should be able to handle Null, so the
condition will not be met and the block will not execute.

About to quit for the night. Will check for your reply in 24 hours, or
someone else may be able to help sooner.

 

[Douglas J Steele]

RBDU: What do you want to happen if there isn't a value for Me.Call_off?

If you want the ElseIf to be False, try:

ElseIf Year(Nz(Me.Call_off, Date)) < Year (Date - 1) Then

 

[RBDU]

Nothing with no error message.

Thanks all for the help.
Peter

 

[RBDU]

Perhaps I am not making myself clear, been a busy night converting A97 to
2003 - trying.

The error happens in line:

ElseIf Year (Me.call_off) < Year ((Now) - 1) then
where Me.call_off = null, Now = current time & date.

In summary, I only want a date/time format input for this year, not last
year or before, or any future date.

Peter

 

[Douglas J Steele]

Did you try my suggestion of

ElseIf Year(Nz(Me.Call_off, Date)) < Year (Date - 1) Then

?

As Allen explained, when you had only Year(Nz(Me.Call_off)), the Nz function
converts Nulls to 0. When dealing with a date, 0 is 30 Dec, 1899. What this
will do is substitute the current date for a Null value.

 

[RBDU]

Now we can all get some sleep, well in 4 hours.
Thanks Guys.

Regards Peter McCartney

 

[RBDU]

Pushing my luck here but the other part of the code:
........................................
If me.Call_off > Date + 1 Then
Msgbox "This date is in the future, please redo!"
Me.Call_off.undo
Cancel = True
........................................
How could this be amended to include not only the date in the future but
also the time in the future. That is, it can have todays date but not the
time in the future for that day. The field is a date/time format.

Regards Peter

 

[Douglas J. Steele]

If me.Call_off > Now() Then

If Call_off doesn't have a time associated with it, that should still work,
because Now() includes time, so it's always going to be greater than Date().

 

[RBDU]

Thank You
Regards Peter

If me.Call_off > Now() Then

If Call_off doesn't have a time associated with it, that should still
work, because Now() includes time, so it's always going to be greater than
Date().