Confusing Dates Query

[Brianwarnock]

Actually just had an idea and discovered that
yearend = Forms![booking in]![booking in lookup sub]![year end (dd/mm)]
can be replaced by
yearend = me.[booking in lookup sub]![year end (dd/mm)]
I think I got lost in your naming before and also had some testing problems, not a criticism just a fact.
Brian

 

[Brianwarnock]

Hassle is how we learn

Database attached.

 

[fagash]

It works

But the "then" and "else" are the wrong way round!

Thanks so much though!

 

[Brianwarnock]

But the "then" and "else" are the wrong way round!

Thanks so much though!

Just testing

Brian

Actually it was proably the < should have been a > but the logic was confusing the old grey matter

 

[fagash]

Actually, I did think of that too but was confusing my poor old grey matter and I'm less than half your age!

All working now though so can get some of the people who are going to be using this to start testing it. No doubt the minute I stop working here they'll do something stupid and mess it all up!

 

[fagash]

One more problem...

Using the code:

Private Sub Year_End_Date_GotFocus()
mydate = Date 'set to system date
'yearend = Forms![booking in]![booking in lookup sub]![year end (dd/mm)]
yearend = Me.[booking in lookup sub]![year end (dd/mm)]
MsgBox (yearend)
If mydate < DateValue(yearend) Then
Me.Year_End_Date = DateAdd("yyyy", -1, DateValue(yearend))
Else: Me.Year_End_Date = DateValue(yearend)
End If
End Sub

A nasty looking error message is created when the "booking in lookup sub" form does not return a result, i.e. the client code does not exist.

How can this be changed to make a slightly nicer "Client not found!" error

I'm thinking something along the lines of using:

"If Forms![booking in]![booking in lookup sub]![year end (dd/mm)] is NULL then msgbox "Client not found!" else (mydate = Date 'set to system date
'yearend = Forms![booking in]![booking in lookup sub]![year end (dd/mm)]
yearend = Me.[booking in lookup sub]![year end (dd/mm)]
MsgBox (yearend)
If mydate < DateValue(yearend) Then
Me.Year_End_Date = DateAdd("yyyy", -1, DateValue(yearend))
Else: Me.Year_End_Date = DateValue(yearend)
End If)
End If

But I haven't a clue what I am doing or if that would work.

 

[Brianwarnock]

Hi

Our system was giving us grief last week so I missed your message.

Below is the code to do what you require copy and paste into the two subroutines as indicated by the names, first removing the current code.
Avoid deleting the subroutines ie the code to delete and copy is that between the 1st and last lines.

You may want to expand the error message, the code in error is visible on the form but will the operative know what to do?

Brian

Private Sub Year_End_Date_GotFocus()

If IsNull(Me.[booking in lookup sub]![year end (dd/mm)]) Then
MsgBox ("Client not Found")
Else: mydate = Date 'set to system date
yearend = Me.[booking in lookup sub]![year end (dd/mm)]
If mydate < DateValue(yearend) Then
Me.Year_End_Date = DateValue(yearend)
Else: Me.Year_End_Date = DateAdd("yyyy", -1, DateValue(yearend))
End If
End If

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.[booking in lookup sub]![year end (dd/mm)]) Then
MsgBox ("Client not Found")
Else: mydate = Date 'set to system date
yearend = Me.[booking in lookup sub]![year end (dd/mm)]
If mydate < DateValue(yearend) Then
Me.Year_End_Date = DateValue(yearend)
Else: Me.Year_End_Date = DateAdd("yyyy", -1, DateValue(yearend))
End If
End If

End Sub