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

[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_Afte rUpdate()
If Me!DateSigned Now Then
Me!DateSigned = Null
DoCmd.CancelEve nt
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

[lyle]

On Aug 8, 9:04 am, MLH <C...@NorthStat e.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!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.

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(expressio n)
CByte(expressio n)
CCur(expression )
CDate(expressio n)
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.

[Rick Brandt]

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

[Marshall Barton]

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!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.

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

[MLH]

Thx for the explanation, Marshall.
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxx
On Wed, 08 Aug 2007 13:29:12 -0500, Marshall Barton
<ma*********@wo wway.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!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.

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.