Solved Compare Dates

[mike60smart]

Hi Everyone

I have a Subform which contains the following Unbound Controls

=[cboCarrier].Column(4) Control name is txtIns which displays the following Date: 11/04/1968
=[cboCarrier].Column(5) Control name is txtCargo which displays the following Date: 11/04/1971
=Date() Control name is txtDate to display todays date: 28/12/2022

I am trying to use the after Update of cboCarrier to display a warning if the Dates are Less Than Todays Date.

The after Update event is as follows and it current does nothing and gives no error. Can someone point out where I am going wrong?

Any help appreciated.

Private Sub cboCarrier_AfterUpdate()

10        On Error GoTo cboCarrier_AfterUpdate_Error
20    If Me.txtDate > Me.txtIns Then
30    MsgBox "Insurance has Expired", vbCritical
35    Me.cboCarrier.SetFocus
40    End If
50    If Me.txtDate > Me.txtCargo Then
60    MsgBox "Cargo Insurance Expired", vbCritical
65     Me.cboCarrier.SetFocus
70    End If

80        On Error GoTo 0
90        Exit Sub

cboCarrier_AfterUpdate_Error:

100       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cboCarrier_AfterUpdate, line " & Erl & "."

End Sub

 

[Pat Hartman]

When you place dates in unbound textboxes, Access does not know they are date data types so you have to tell it. One way is to format the control to a date format such as Short Date.

The other alternative, is to reference the .Column property directly.

 

[MajP]

Another way is to wrap with a cdate. cdate(me.txtins) >...
Or
=Cdate([cbocarrier].column(5))

 

[mike60smart]

Another way is to wrap with a cdate. cdate(me.txtins) >...
Or
=Cdate([cbocarrier].column(5))

Hi Pat & MajP

Pat I had formatted the dates as ShortDate

MajP That works just great.

Many thanks both for looking

 

[Gasman]

Hi Pat & MajP

Pat I had formatted the dates as ShortDate

MajP That works just great.

Many thanks both for looking

Might want to mark it Solved then?

 

[mike60smart]

Might want to mark it Solved then?

Thanks Gasman

 

[Pat Hartman]

Pat I had formatted the dates as ShortDate

How? Did you set the format property of the control or did you use the Format() function?

 

[mike60smart]

How? Did you set the format property of the control or did you use the Format() function?

Hi Pat
I set the format property of the control

 

[Pat Hartman]

That has always worked for me. It could be that you are not using US standard date formatting and that is causing the confusion. At least the cdate() works for you,