Date function not working?

  • Thread starter Thread starter Nelson
  • Start date Start date
N

Nelson

I have 2 text box fields that I'm working with on one of my forms.
They are:
Remarks4 - this is a memo field data type
Remark Date - this is a date/time field data type

I'm trying to use the AfterUpdate Event on the Remarks4 field to validate
that the user has updated the [Remark Date] to today's date. This is
because if they are adding a remark here, they should also change the Remark
Date to today's date. Here is my code:

Private Sub Remarks4_AfterUpdate()
Dim strMsg As String
If Not [Remark Date].Value = DATE Then
strMsg = "Please update the Remark Date."
[Remark Date].SetFocus
MsgBox strMsg
End If
End Sub

It's not working though. Let's say the remark date currently reads
5/8/2003. When I update the Remark4 text box it should run the AfterUpdate
Event (which it does) and it should see that 5/8/2003 does not equal today's
date of 7/30/2003 and it should execute the If Statement (it doesn't do
this, it skips straight from the If line to the End If line without
executing the code in between).

When I debug and open the immediate window I typed in "Msgbox Date" and it
reads 5/8/2003 instead of 7/30/2003.

Does anyone know why this isn't working? I thought the Date function was
always supposed to show today's date?

Thanks,
Chris
 
Hi Nelson,

If you are forcing the user to change the date if it is
not equal to today's date, why not just automatically
change it to today's date in the AfterUpdate event of the
Remarks4 field?

You coul also check to make sure you do not have any
variables by the name of 'Date' .. not likely but you
never know.

I also had trouble with the date function .. so I
formatted it myself using

[textbox] = """" & Month(Now) & "/" & Day(Now) & "/" & Year
(Now) & """"

Good luck,
Gurtz
[email = no $]
 
I don't want to force the user to enter today's date because there are many
exceptions to the rule in this case. Many times we will want to keep the
date as is, but I do want to have a reminder (hence the message box) in case
they are just forgetting to update the date.

I took your advice to format the date manually and updated my code to read:
Private Sub Remarks4_AfterUpdate()
Dim strMsg As String
If Not Me.[Remark Date].Value = "" & Month(Now) & "/" & Day(Now) & "/" &
Year(Now) & "" Then
strMsg = "Please update the Remark Date and put your Initials in the
appropriate boxes."
[Remark Date].SetFocus
MsgBox strMsg
End If
End Sub

However, it still is not evaluating the statement correctly. I don't
understand why.

Anyone have any ideas?

Thanks,
Chris


Gurtz said:
Hi Nelson,

If you are forcing the user to change the date if it is
not equal to today's date, why not just automatically
change it to today's date in the AfterUpdate event of the
Remarks4 field?

You coul also check to make sure you do not have any
variables by the name of 'Date' .. not likely but you
never know.

I also had trouble with the date function .. so I
formatted it myself using

[textbox] = """" & Month(Now) & "/" & Day(Now) & "/" & Year
(Now) & """"

Good luck,
Gurtz
[email = no $]
-----Original Message-----
I have 2 text box fields that I'm working with on one of my forms.
They are:
Remarks4 - this is a memo field data type
Remark Date - this is a date/time field data type

I'm trying to use the AfterUpdate Event on the Remarks4 field to validate
that the user has updated the [Remark Date] to today's date. This is
because if they are adding a remark here, they should also change the Remark
Date to today's date. Here is my code:

Private Sub Remarks4_AfterUpdate()
Dim strMsg As String
If Not [Remark Date].Value = DATE Then
strMsg = "Please update the Remark Date."
[Remark Date].SetFocus
MsgBox strMsg
End If
End Sub

It's not working though. Let's say the remark date currently reads
5/8/2003. When I update the Remark4 text box it should run the AfterUpdate
Event (which it does) and it should see that 5/8/2003 does not equal today's
date of 7/30/2003 and it should execute the If Statement (it doesn't do
this, it skips straight from the If line to the End If line without
executing the code in between).

When I debug and open the immediate window I typed in "Msgbox Date" and it
reads 5/8/2003 instead of 7/30/2003.

Does anyone know why this isn't working? I thought the Date function was
always supposed to show today's date?

Thanks,
Chris


.
Click to expand...
Click to expand...
 
if [Remark Date] is a Date Field then it will never = "" & Month(Now) & "/"
& Day(Now) & "/" & Year(Now) & "" because you are comparing a Date to a
String.

For instance from the immediate window of Access Type
? ("" & Month(Now) & "/" & Day(Now) & "/" & Year(Now) & "" ) = date
and it will Print False They are not Equal
But
cdate("" & Month(Now) & "/" & Day(Now) & "/" & Year(Now) & "" ) = date
will evaluate to True because you converted the string to a date before
doing the compare.

Why don't you try

If Not cdate(Me.[Remark Date].Value) = date

Ron W
Nelson said:
I don't want to force the user to enter today's date because there are many
exceptions to the rule in this case. Many times we will want to keep the
date as is, but I do want to have a reminder (hence the message box) in case
they are just forgetting to update the date.

I took your advice to format the date manually and updated my code to read:
Private Sub Remarks4_AfterUpdate()
Dim strMsg As String
If Not Me.[Remark Date].Value = "" & Month(Now) & "/" & Day(Now) & "/" &
Year(Now) & "" Then
strMsg = "Please update the Remark Date and put your Initials in the
appropriate boxes."
[Remark Date].SetFocus
MsgBox strMsg
End If
End Sub

However, it still is not evaluating the statement correctly. I don't
understand why.

Anyone have any ideas?

Thanks,
Chris


Gurtz said:
Hi Nelson,

If you are forcing the user to change the date if it is
not equal to today's date, why not just automatically
change it to today's date in the AfterUpdate event of the
Remarks4 field?

You coul also check to make sure you do not have any
variables by the name of 'Date' .. not likely but you
never know.

I also had trouble with the date function .. so I
formatted it myself using

[textbox] = """" & Month(Now) & "/" & Day(Now) & "/" & Year
(Now) & """"

Good luck,
Gurtz
[email = no $]
-----Original Message-----
I have 2 text box fields that I'm working with on one of my forms.
They are:
Remarks4 - this is a memo field data type
Remark Date - this is a date/time field data type

I'm trying to use the AfterUpdate Event on the Remarks4 field to validate
that the user has updated the [Remark Date] to today's date. This is
because if they are adding a remark here, they should also change the Remark
Date to today's date. Here is my code:

Private Sub Remarks4_AfterUpdate()
Dim strMsg As String
If Not [Remark Date].Value = DATE Then
strMsg = "Please update the Remark Date."
[Remark Date].SetFocus
MsgBox strMsg
End If
End Sub

It's not working though. Let's say the remark date currently reads
5/8/2003. When I update the Remark4 text box it should run the AfterUpdate
Event (which it does) and it should see that 5/8/2003 does not equal today's
date of 7/30/2003 and it should execute the If Statement (it doesn't do
this, it skips straight from the If line to the End If line without
executing the code in between).

When I debug and open the immediate window I typed in "Msgbox Date" and it
reads 5/8/2003 instead of 7/30/2003.

Does anyone know why this isn't working? I thought the Date function was
always supposed to show today's date?

Thanks,
Chris


.
Click to expand...
Click to expand...
Click to expand...
 
Thanks guys!

For some reason "If Not cdate(Me.[Remark Date].Value) = date" didn't work.
The only way I could get it to work was by using a combination of Gurtz and
Ron's solutions. Here is how the final WORKING If statement reads:
If Not CDate(Me.[Remark Date].Value) = "" & Month(Now) & "/" & Day(Now) &
"/" & Year(Now) & "" Then


Thanks very much,
Chris


Ron Weiner said:
if [Remark Date] is a Date Field then it will never = "" & Month(Now) & "/"
& Day(Now) & "/" & Year(Now) & "" because you are comparing a Date to a
String.

For instance from the immediate window of Access Type
? ("" & Month(Now) & "/" & Day(Now) & "/" & Year(Now) & "" ) = date
and it will Print False They are not Equal
But
cdate("" & Month(Now) & "/" & Day(Now) & "/" & Year(Now) & "" ) = date
will evaluate to True because you converted the string to a date before
doing the compare.

Why don't you try

If Not cdate(Me.[Remark Date].Value) = date

Ron W
Nelson said:
I don't want to force the user to enter today's date because there are many
exceptions to the rule in this case. Many times we will want to keep the
date as is, but I do want to have a reminder (hence the message box) in case
they are just forgetting to update the date.

I took your advice to format the date manually and updated my code to read:
Private Sub Remarks4_AfterUpdate()
Dim strMsg As String
If Not Me.[Remark Date].Value = "" & Month(Now) & "/" & Day(Now) &
Click to expand...
"/"
&
Year(Now) & "" Then
strMsg = "Please update the Remark Date and put your Initials in the
appropriate boxes."
[Remark Date].SetFocus
MsgBox strMsg
End If
End Sub

However, it still is not evaluating the statement correctly. I don't
understand why.

Anyone have any ideas?

Thanks,
Chris


Gurtz said:
Hi Nelson,

If you are forcing the user to change the date if it is
not equal to today's date, why not just automatically
change it to today's date in the AfterUpdate event of the
Remarks4 field?

You coul also check to make sure you do not have any
variables by the name of 'Date' .. not likely but you
never know.

I also had trouble with the date function .. so I
formatted it myself using

[textbox] = """" & Month(Now) & "/" & Day(Now) & "/" & Year
(Now) & """"

Good luck,
Gurtz
[email = no $]

-----Original Message-----
I have 2 text box fields that I'm working with on one of
my forms.
They are:
Remarks4 - this is a memo field data type
Remark Date - this is a date/time field data type

I'm trying to use the AfterUpdate Event on the Remarks4
field to validate
that the user has updated the [Remark Date] to today's
date. This is
because if they are adding a remark here, they should
also change the Remark
Date to today's date. Here is my code:

Private Sub Remarks4_AfterUpdate()
Dim strMsg As String
If Not [Remark Date].Value = DATE Then
strMsg = "Please update the Remark Date."
[Remark Date].SetFocus
MsgBox strMsg
End If
End Sub

It's not working though. Let's say the remark date
currently reads
5/8/2003. When I update the Remark4 text box it should
run the AfterUpdate
Event (which it does) and it should see that 5/8/2003
does not equal today's
date of 7/30/2003 and it should execute the If Statement
(it doesn't do
this, it skips straight from the If line to the End If
line without
executing the code in between).

When I debug and open the immediate window I typed
in "Msgbox Date" and it
reads 5/8/2003 instead of 7/30/2003.

Does anyone know why this isn't working? I thought the
Date function was
always supposed to show today's date?

Thanks,
Chris


.
Click to expand...
Click to expand...
Click to expand...
 
No problem Nelson,

However, it doesn't look as though you need the "" before
and after your date construction .. I had """ which begins
the string, then puts a literal " before my date
construction.. this was for formatting purposes. So it
looks like you can get rid of your double quotes before
and after..

Gurtz
-----Original Message-----
Thanks guys!

For some reason "If Not cdate(Me.[Remark Date].Value) = date" didn't work.
The only way I could get it to work was by using a combination of Gurtz and
Ron's solutions. Here is how the final WORKING If statement reads:
If Not CDate(Me.[Remark Date].Value) = "" & Month(Now) & "/" & Day(Now) &
"/" & Year(Now) & "" Then


Thanks very much,
Chris


Ron Weiner said:
if [Remark Date] is a Date Field then it will never
Click to expand...
= "" & Month(Now) &
"/"
& Day(Now) & "/" & Year(Now) & "" because you are comparing a Date to a
String.

For instance from the immediate window of Access Type
? ("" & Month(Now) & "/" & Day(Now) & "/" & Year(Now) & "" ) = date
and it will Print False They are not Equal
But
cdate("" & Month(Now) & "/" & Day(Now) & "/" & Year (Now) & "" ) = date
will evaluate to True because you converted the string to a date before
doing the compare.

Why don't you try

If Not cdate(Me.[Remark Date].Value) = date

Ron W
Nelson said:
I don't want to force the user to enter today's date
Click to expand...
because there are
many
exceptions to the rule in this case. Many times we
Click to expand...
Click to expand...
will want to keep
the
date as is, but I do want to have a reminder (hence
Click to expand...
the message box) in
case
they are just forgetting to update the date.

I took your advice to format the date manually and
Click to expand...
updated my code to
read:
Private Sub Remarks4_AfterUpdate()
Dim strMsg As String
If Not Me.[Remark Date].Value = "" & Month(Now)
Click to expand...
Click to expand...
& "/" & Day(Now) &
"/"
&
Year(Now) & "" Then
strMsg = "Please update the Remark Date and
Click to expand...
put your Initials in
the
appropriate boxes."
[Remark Date].SetFocus
MsgBox strMsg
End If
End Sub

However, it still is not evaluating the statement correctly. I don't
understand why.

Anyone have any ideas?

Thanks,
Chris


Hi Nelson,

If you are forcing the user to change the date if it is
not equal to today's date, why not just automatically
change it to today's date in the AfterUpdate event of the
Remarks4 field?

You coul also check to make sure you do not have any
variables by the name of 'Date' .. not likely but you
never know.

I also had trouble with the date function .. so I
formatted it myself using

[textbox] = """" & Month(Now) & "/" & Day(Now) & "/" & Year
(Now) & """"

Good luck,
Gurtz
[email = no $]

-----Original Message-----
I have 2 text box fields that I'm working with on one of
my forms.
They are:
Remarks4 - this is a memo field data type
Remark Date - this is a date/time field data type

I'm trying to use the AfterUpdate Event on the Remarks4
field to validate
that the user has updated the [Remark Date] to today's
date. This is
because if they are adding a remark here, they should
also change the Remark
Date to today's date. Here is my code:

Private Sub Remarks4_AfterUpdate()
Dim strMsg As String
If Not [Remark Date].Value = DATE Then
strMsg = "Please update the Remark Date."
[Remark Date].SetFocus
MsgBox strMsg
End If
End Sub

It's not working though. Let's say the remark date
currently reads
5/8/2003. When I update the Remark4 text box it should
run the AfterUpdate
Event (which it does) and it should see that 5/8/2003
does not equal today's
date of 7/30/2003 and it should execute the If Statement
(it doesn't do
this, it skips straight from the If line to the End If
line without
executing the code in between).

When I debug and open the immediate window I typed
in "Msgbox Date" and it
reads 5/8/2003 instead of 7/30/2003.

Does anyone know why this isn't working? I thought the
Date function was
always supposed to show today's date?

Thanks,
Chris


.
Click to expand...
Click to expand...


.
Click to expand...
 
Chris,

This works for me...
If Me.Remark_Date <> Date

I think the main problem is your use of the Not operator, which is in
the wrong place, (or else you need parantheses around the expression
you are trying to evaluate).

The only other reason I can think of why this type of procedure would
run into strife is if the original entry in [Remark Date] was done via
a Now() function, for example as a Default Value setting, in which
case the comparison with Date will not work.

- Steve Schapel, Microsoft Access MVP
 
Back
Top