condition - year not the same then do task unless null

[Ruegen]

I'm trying to make a piece of code work



the idea is that if the years don't match (specific dates don't matter) then it won't let the user enter the date - however I don't want the alert to appear when the user clears the textbox for the bookingdate (if they change the year - it automatically has an action to null the bookingdate field)

code so far is:

Private Sub BookingDate_AfterUpdate()


Dim strYear As String
Dim strBooking As String


strYear = Nz(Year(Me.BookingYear.Column(1)), "no")
strBooking = Nz(Year(Me.BookingDate.Value), "") ' I get an error if I don't assign nz() to this




If strYear <> strBooking And IsNull(strBooking) Then
'do no task
ElseIf strYear <> strBooking And Not IsNull(strBooking) Then
MsgBox "You have selected a date that is not within the year, please select a correct booking date or change the year"
Me.BookingDate = Null
End If


End Sub

so um yeah, how do I get around something like this, the proper way.

[Xipooo]

I think this is what you're looking for... but not quite sure.

Private Sub BookingDate_AfterUpdate()

  If Not IsNull(Me.BookingDate) AND Not IsNull(Me.BookingYear.Column(1)) Then
    Dim intYear As Integer
    Dim intBooking As Integer


    intYear = Year(Me.BookingYear.Column(1))
    intBooking = Year(Me.BookingDate.Value)


    If intYear <> intBooking Then
    MsgBox "You have selected a date that is not within the year, please select a correct booking date or change the year"
    Me.BookingDate = Null
  End If
End Sub

BTW, you had your two variables strBooking and strYear as strings. Both the Year and Nz function return integers by default. You would need to do a Cstr conversion if you truly wanted to make them strings. You may also need to wrap a CDate function around your BookingDate and BookingYear.Column(1) depending on what kind of data is in them.

[ssanfu]

I just noticed an error in the code by Xipooo - there should be two "End If" lines.

<snip>
    Me.BookingDate = Null
  End If
  End If
End Sub

@Xipooo:

Both the Year and Nz function return integers by default

I just tested this... Access seems to automatically convert the integer year value to a string. But I will be aware of this possibility in the future. Thanks...

[Ruegen]

testing the code

[Ruegen]

I think this is what you're looking for... but not quite sure.

Private Sub BookingDate_AfterUpdate()

  If Not IsNull(Me.BookingDate) AND Not IsNull(Me.BookingYear.Column(1)) Then
    Dim intYear As Integer
    Dim intBooking As Integer


    intYear = Year(Me.BookingYear.Column(1))
    intBooking = Year(Me.BookingDate.Value)


    If intYear <> intBooking Then
    MsgBox "You have selected a date that is not within the year, please select a correct booking date or change the year"
    Me.BookingDate = Null
  End If
End Sub

BTW, you had your two variables strBooking and strYear as strings. Both the Year and Nz function return integers by default. You would need to do a Cstr conversion if you truly wanted to make them strings. You may also need to wrap a CDate function around your BookingDate and BookingYear.Column(1) depending on what kind of data is in them.

thanks

yeah I was aware of date conversion to string but it never has bothered me - but will keep in mind.

[Ruegen]

Works beautifully (and like I imagined it was so simple - I sometimes get scrambled that way)


thank you so much

[Xipooo]

No problem.

Yeah I forgot Access sometimes does implicit conversions. Other languages that I'm usually working in aren't that nice to play with. Wires get crossed sometimes.