By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,054 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

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

P: n/a
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_AfterUpdate()

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

End Sub
Aug 3 '07 #1
Share this Question
Share on Google+
14 Replies


P: n/a

"MLH" <CR**@NorthState.netschreef in bericht news:94********************************@4ax.com...
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_AfterUpdate()

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
Aug 3 '07 #2

P: n/a
On Fri, 03 Aug 2007 11:29:07 -0400, MLH <CR**@NorthState.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_AfterUpdate()

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

End Sub
Aug 4 '07 #3

P: n/a
On Aug 4, 5:26 am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Fri, 03 Aug 2007 11:29:07 -0400, MLH <C...@NorthState.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_AfterUpdate()
If Me!DateSigned Now Then
Me!DateSigned = Null
MsgBox "No Way Jose!"
End If
End Sub
You could also use

If CDate(Me!DateSigned) Date() Then
....

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

Regards,
Branislav Mihaljev

Aug 4 '07 #4

P: n/a
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

Aug 5 '07 #5

P: n/a
"SmartbizAustralia" <to*@smartbiz.com.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
Aug 6 '07 #6

P: n/a

"SmartbizAustralia" <to*@smartbiz.com.auschreef in bericht news:11**********************@i13g2000prf.googlegr oups.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
Aug 6 '07 #7

P: n/a
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_AfterUpdate() displays "No Way Jose!".
I did not expect the following Sub to evaluate Me!DateSigned
being greater than Now().

Private Sub DateSigned_AfterUpdate()
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
Aug 8 '07 #8

P: n/a

"MLH" <CR**@NorthState.netschreef in bericht news:5u********************************@4ax.com...
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_AfterUpdate() displays "No Way Jose!".
I did not expect the following Sub to evaluate Me!DateSigned
being greater than Now().

Private Sub DateSigned_AfterUpdate()
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!NameOfForm!DateSigned
?Now

Arno R

Aug 8 '07 #9

P: n/a
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!MyControl,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.
Aug 8 '07 #10

P: n/a
MLH
<snip>
>
The obvious:
Check your SystemDate first .... What value does Now() give you ??
Please.
>
BTW: If you are validating input, better use the BeforeUpdate event.
Point well taken. But if invalid input is discovered during the Before
Update event, one cannot take action to correct it. A very good
correction is to throw away the user input by setting the control to
Null. That WORKS in the AfterUpdate event but NOT in the Before
Update event...

Private Sub DateSigned_AfterUpdate()
If Me!DateSigned Now Then
Me!DateSigned = Null
DoCmd.CancelEvent
MsgBox "No Way Jose!"
End If
End Sub

The above will give you errors. There is NO WAY to take away the value
entered, leaving nothing in the control - during BeforeUpdate event
processing. AfterUpdate code is the ONLY logical place. Maybe I'm
wrong, but I cannot recall ever having seen any code posted that could
dynamically modify the contents of a textbox during BeforeUpdate event
code processing.
>
Arno R
Aug 8 '07 #11

P: n/a
On Aug 8, 9:04 am, MLH <C...@NorthState.netwrote:
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!MyControl,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.
All data are strings. You can trust Access/VBA/JET to treat these
strings in the way you want them treated or you can declare and cast
everything yourself the way you would be required to do in many
programming languages. I choose the latter. If you have an unbound
text box how could Access "know" the string value in it should be
treated as date?
It's not very labor intensive to use the
CBool(expression)
CByte(expression)
CCur(expression)
CDate(expression)
CDbl(expression)
CDec(expression)
CInt(expression)
CLng(expression)
CSng(expression)
CStr(expression)
CVar(expression)
functions. And one might actually learn a little in the process.
Oh yeah, did I mention that his/her programs might work too.
Aug 8 '07 #12

P: n/a
MLH wrote:
<snip>
>>
The obvious:
Check your SystemDate first .... What value does Now() give you ??
Please.

BTW: If you are validating input, better use the BeforeUpdate event.
Point well taken. But if invalid input is discovered during the Before
Update event, one cannot take action to correct it. A very good
correction is to throw away the user input by setting the control to
Null. That WORKS in the AfterUpdate event but NOT in the Before
If you cancel the update and then issue an Undo on the control it will be
cleared, BUT if you use the Cancel = True in the BeforeUpdate it is better
to allow the user to correct the value (he will be forced to after all).
That way he can still see his invalid entry to determine what the problem
is.

For example if he accidentally enters the wrong year and you give him a
message like "Invalid Date" and then blank it out, sooner or later you will
have a user telling you that "Sometimes it tells me my entry is incorrect
when it is NOT incorrect". This of course would mean that he entered an
incorrect value but was not aware of it. By leaving the incorrect value in
the control you allow the user to examine it so he can slap his forehead and
say "Doh! That should be 2007, not 2006".

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Aug 8 '07 #13

P: n/a
MLH wrote:
>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!MyControl,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.

As others have said, when you enter something like 8/15/2007
into a text box, Access has to try to figure out what it
should do with the characters. The first clue Access uses
in its guessing process is to check the type of the field
bound to the text box. If it's a Date type, you might be
ok. If it's a Text field, then you will probably see your
problem.

OTOH, if the text box is not bound, then Access looks at the
text box's Format property to see if it provides any clues.
If the Format is the kind used for a date/time, then you are
probably ok. Any other kind of format, including none will
lead Access off in the wrong direction and your data might
be interpreted in some way that you don't like.

If you follow Lyle's advice, you can avoid the "might" and
"probably" involved when you let Access play its guessing
game.

--
Marsh
Aug 8 '07 #14

P: n/a
MLH
Thx for the explanation, Marshall.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Wed, 08 Aug 2007 13:29:12 -0500, Marshall Barton
<ma*********@wowway.comwrote:
>MLH wrote:
>>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!MyControl,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.


As others have said, when you enter something like 8/15/2007
into a text box, Access has to try to figure out what it
should do with the characters. The first clue Access uses
in its guessing process is to check the type of the field
bound to the text box. If it's a Date type, you might be
ok. If it's a Text field, then you will probably see your
problem.

OTOH, if the text box is not bound, then Access looks at the
text box's Format property to see if it provides any clues.
If the Format is the kind used for a date/time, then you are
probably ok. Any other kind of format, including none will
lead Access off in the wrong direction and your data might
be interpreted in some way that you don't like.

If you follow Lyle's advice, you can avoid the "might" and
"probably" involved when you let Access play its guessing
game.
Aug 9 '07 #15

This discussion thread is closed

Replies have been disabled for this discussion.