Unsure why VBA thinks dates entered are greater than Now() - when they are not?

[MLH]

After entering a date (earlier than today's date) into a textbox
control though, I'm stumped as to why VBA thinks date I enter into the
control is greater than Now - when they are CLEARLY less than Now.
Here's the code I have in the AfterUpdate event. I must be overlooking
the obvious. Ideas?

Private Sub DateSigned_Afte rUpdate()

If Me!DateSigned Now Then
Me!DateSigned = Null
MsgBox "No Way Jose!"
End If

End Sub

[Arno R]

"MLH" <CR**@NorthStat e.netschreef in bericht news:94******** *************** *********@4ax.c om...

After entering a date (earlier than today's date) into a textbox
control though, I'm stumped as to why VBA thinks date I enter into the
control is greater than Now - when they are CLEARLY less than Now.
Here's the code I have in the AfterUpdate event. I must be overlooking
the obvious. Ideas?

Private Sub DateSigned_Afte rUpdate()

If Me!DateSigned Now Then
Me!DateSigned = Null
MsgBox "No Way Jose!"
End If

End Sub

The obvious:
Check your SystemDate first .... What value does Now() give you ??

BTW: If you are validating input, better use the BeforeUpdate event.

Arno R

[Tom van Stiphout]

On Fri, 03 Aug 2007 11:29:07 -0400, MLH <CR**@NorthStat e.netwrote:

Arno has a good point about BeforeUpdate: set Cancel=True and have the
user try again.
But the real point of Why (Assuming you were not that stupid to reset
your computer clock) is one of type coercion. My guess is that the
whole expression is evaluated as strings rather than as dates. Enter
this in the Immediate Window:
(Note: today is 8/3/2007 in USA format)

?(8/15/2007 Now)
False

?("8/15/2007" Now)
False

?(#8/15/2007# Now)
True

So my guess is your expression is evaluated as a string comparison,
and the ASCII value of "8/1" is lower than that of "8/3".

To compare dates, exclusively use DateDiff. It will cast both
arguments to Date before comparing, similar to my third example.

-Tom.

>After entering a date (earlier than today's date) into a textbox
control though, I'm stumped as to why VBA thinks date I enter into the
control is greater than Now - when they are CLEARLY less than Now.
Here's the code I have in the AfterUpdate event. I must be overlooking
the obvious. Ideas?

Private Sub DateSigned_Afte rUpdate()

If Me!DateSigned Now Then
Me!DateSigned = Null
MsgBox "No Way Jose!"
End If

End Sub

[banem2]

On Aug 4, 5:26 am, Tom van Stiphout <no.spam.tom7.. [e-mail]:

On Fri, 03 Aug 2007 11:29:07 -0400, MLH <C...@NorthStat e.netwrote:

Arno has a good point about BeforeUpdate: set Cancel=True and have the
user try again.
But the real point of Why (Assuming you were not that stupid to reset
your computer clock) is one of type coercion. My guess is that the
whole expression is evaluated as strings rather than as dates. Enter
this in the Immediate Window:
(Note: today is 8/3/2007 in USA format)

?(8/15/2007 Now)
False

?("8/15/2007" Now)
False

?(#8/15/2007# Now)
True

So my guess is your expression is evaluated as a string comparison,
and the ASCII value of "8/1" is lower than that of "8/3".

To compare dates, exclusively use DateDiff. It will cast both
arguments to Date before comparing, similar to my third example.

-Tom.

After entering a date (earlier than today's date) into a textbox
control though, I'm stumped as to why VBA thinks date I enter into the
control is greater than Now - when they are CLEARLY less than Now.
Here's the code I have in the AfterUpdate event. I must be overlooking
the obvious. Ideas?

Private Sub DateSigned_Afte rUpdate()

If Me!DateSigned Now Then
Me!DateSigned = Null
MsgBox "No Way Jose!"
End If

End Sub

You could also use

If CDate(Me!DateSi gned) Date() Then
....

Also check if the date format in Control Panel/Regional Settings
matches date in Access field.

Regards,
Branislav Mihaljev

[SmartbizAustralia]

Dates are a massive pain especially if you are not american!
(Note: No offense meant to americans, it's just that the dates don't
work intuitively for the rest of the world!)

THe problem is that Access only thinks in American format but stores
it anyway you want.

To combat date issues we always use the longer month format in
everything we do in Access. e.g. dd-MMM-yyyy e.g. 5 Mar 2006.

So first things first, figure out if the problem is the date format.
e.g. 5 Mar 2006 is 3/5/2006 in american terms....as they put the month
first.

Regards,
Tom Bizannes
Sydney, Australia
http://www.smartbiz.com.au

[Larry Linson]

"SmartbizAustra lia" <to*@smartbiz.c om.auwrote

THe problem is that Access only thinks in American
format but stores it anyway you want.

Actually, that is not the case... all date/time values in Access are stored
the same way, regardless of the Regional Settings -- that is, a date/time
field or variable is the equivalent of a Double Precision Floating Point
value with the integral (or whole number) portion representing days since
Dec. 30, 1899 and the fractional portion representing time since midnight.

The Regional Settings only affect how date data you enter is interpreted and
how stored date data is displayed.

The problem you encounter is that there are some situations in Access and
the Jet database engine which ignore the Regional Settings.

(YMMV on date data kept by other applications or by Windows itself.)

Larry Linson
Microsoft Access MVP

[Arno R]

"SmartbizAustra lia" <to*@smartbiz.c om.auschreef in bericht news:11******** **************@ i13g2000prf.goo glegroups.com.. .

Dates are a massive pain especially if you are not american!
(Note: No offense meant to americans, it's just that the dates don't
work intuitively for the rest of the world!)

THe problem is that Access only thinks in American format but stores
it anyway you want.

To combat date issues we always use the longer month format in
everything we do in Access. e.g. dd-MMM-yyyy e.g. 5 Mar 2006.

So first things first, figure out if the problem is the date format.
e.g. 5 Mar 2006 is 3/5/2006 in american terms....as they put the month
first.

Regards,
Tom Bizannes
Sydney, Australia
http://www.smartbiz.com.au

Would be nice if MLH would let us know what the actual problem was here ....

Arno R

[MLH]

Arno:
The problem is as I stated in the O.P.

After entering a date (earlier than today's date) into a textbox
control, Sub DateSigned_Afte rUpdate() displays "No Way Jose!".
I did not expect the following Sub to evaluate Me!DateSigned
being greater than Now().

Private Sub DateSigned_Afte rUpdate()
If Me!DateSigned Now Then
Me!DateSigned = Null
MsgBox "No Way Jose!"
End If
End Sub

<snip>

>
Would be nice if MLH would let us know what the actual problem was here ...

Arno R

[Arno R]

"MLH" <CR**@NorthStat e.netschreef in bericht news:5u******** *************** *********@4ax.c om...

Arno:
The problem is as I stated in the O.P.

After entering a date (earlier than today's date) into a textbox
control, Sub DateSigned_Afte rUpdate() displays "No Way Jose!".
I did not expect the following Sub to evaluate Me!DateSigned
being greater than Now().

Private Sub DateSigned_Afte rUpdate()
If Me!DateSigned Now Then
Me!DateSigned = Null
MsgBox "No Way Jose!"
End If
End Sub

<snip>

>>
Would be nice if MLH would let us know what the actual problem was here ...

Arno R

So you are telling us that you still have this very same problem ??
I had the impression that your problem was solved and was interested to hear what caused the problem ....
I should have said:
"Would be nice if MLH would let us know what the actual problem has been here ..."

What about all the suggestions ?? Did you work that out ?
What is the outcome of the following in the immediate window?
?Forms!NameOfFo rm!DateSigned
?Now

Arno R

[MLH]

Seems that Tom van Stiphout hit it on the nose. Must-a-been
some kind-a-string comparison going on, rather than a date-to-date
comparison. I ran his suggested tests in the immediate window.
So I took banem2 up on his suggestion to encapsulate the textbox
reference inside CDate(). Apparently, that had an effect and seems
like the one I want.

Your comments re: Before vs After Update were well received too.
Thx.

After having made the 'fix', I found more places in my code were
greater-than / lesser-than comparisons may be returning questionable
results. So I'll likely take Tom van Stiphout up on his suggestion to
use DateDiff in some of these places. Although, I haven't tested and
am unsure what DateDiff(Forms! MyForm!MyContro l,Now) will do if user
types 8/15/2007 in a textbox without surrounding it with #...#

I guess, in the OP, I was hoping someone might know how I could
inform A97 that a particular textbox on a form contained a date and
that any subsequent use of the value in there would be treated as if
it were a date. I mean, I can use datediff, yeah. But, looking at an
integer difference representing the number of days between two dates
and considering the sign of the value returned is more trouble than
asking "Hey, is Date1 Date2?" Huh? Huh? That's quick & easy
whereas the former is labor-laden.