Late Checkbox

[Milothicus]

Here's my code. it's in the afterupdate for both dtQuote_Date and dtQuote_Due_Date:

If Me.dtQuote_Date.Value > Me.dtQuote_Due_Date.Value Then
Me.chkLate.Value = True
Else
Me.chkLate.Value = False
End If
Me.chkLate.Requery

the chkLate box never changes. I've tried with and without the else, and with and without the requery. I don't know why it's not working.

am i right to refer to the controls on the form? should i be referring to the columns they're bound to? all the variables came up as options after typeing 'me.' so i assume they're all accessible to the code.

Am i doing something horribly wrong?

 

[Rich]

Your checkbox is actually redundant, use a hidden label instead, and you don't need .Value or the Requery

In the FormCurrent Event

If Me.dtQuote_Date > Me.dtQuote_Due_Date Then
Me.MyLabel.Visible = True
Else
Me.MyLabel.Visible = False
End If

 

[Milothicus]

I'd like to keep track of how many Quotes are late for statistical purposes, so shouldn't i have a yes/no field in my table to track late quotes? or is that a calculation to put in a query when i need it? if i were to keep track in a table, how do i update that field?

 

[Rich]

Repeat the calculation in the query, then you're not reliant on code which may not even be run

 

[Milothicus]

alright, thanks.

 

[Milothicus]

Still not working...... it won't decide either way with:

If Me.dtQuote_Date > Me.dtQuote_Due_Date Then
and
If Me.dtQuote_Date < Me.dtQuote_Due_Date Then

i put in a breakpoint and stepped through it, and it skips both. it doesn't seem to understand < and > with dates.

here's my full code. it always runs the ELSE:

If Me.dtQuote_Date <> Null And Me.dtQuote_Date > Me.dtQuote_Due_Date Then
Me.lblLate.Visible = True
Me.lblOnTime.Visible = False
Me.lblIncomplete.Visible = False
ElseIf Me.dtQuote_Date <> Null And Me.dtQuote_Date < Me.dtQuote_Due_Date Then
Me.lblLate.Visible = False
Me.lblOnTime.Visible = True
Me.lblIncomplete.Visible = False
Else
Me.lblLate.Visible = False
Me.lblOnTime.Visible = False
Me.lblIncomplete.Visible = True
End If

Also, all fields are formatted as short date/time, and are input with a calendar form i got from this forum.

when i stepped through the code, each date came up as a short date, as expected. it just skips all if's except the else.

edit: i tried an if statement with just:

If Me.dtQuote_Date = Null Then
Me.lblLate.Visible = False
Me.lblOnTime.Visible = False
Me.lblIncomplete.Visible = True
End If

and put in a breakpoint. it clearly tells me that Me.dtQuote_Date = Null, and then skips over the body of the if statement.

why does VBA hate me?

 

[Rich]

If Not IsNull(Me.YourControl) Then

 

[Milothicus]

I never got the date comparisons to work, so what i did was use datediff and choose the status based on the sign of datediff. negative means late. works great.