Problem with date comparison

  • Thread starter Thread starter Steve G.
  • Start date Start date
S

Steve G.

I'm wrote the following code to compare two different date fields, "Text377"
and "CurrentDate". When I view the data the dates they are the same, but
when I execute the following code, I receive the MsgBox error. Any idea
what's wrong ?

Private Sub Form_Load()
Dim CurrentDate
CurrentDate = Date

If [Text377] <> [CurrentDate] Then
MsgBox "Meeting Schedule Not Current", vbOKOnly, "Error!"
DoCmd.Close acForm, "Meeting Schedule List"
DoCmd.OpenForm "Decision Form"
End If
End Sub
 
Are you saying that you have an error about the message box OR the message
box error is showing up when it shouldn't?

If you are getting an error in the code, it looks like you forgot the ( ). A
compile should catch that.

MsgBox("Meeting Schedule Not Current", vbOKOnly, "Error!")

If you are saying that [Text377] <> [CurrentDate] should not be true, then
are both fields Date/Time data types?

If Date/Time data types, then the Time might be the problem. You could be
storing the time but not have the field formatted to display it. Go to the
tables and set the format for those fields to General Date. That will show
the dates AND times. Look at the records which should match now.
 
How is text377 defined? Since Access uses date/time variables, are
you sure that text377 doesn't have a time associated to it that would
cause it not to equal Date() ?
 
If you place ( ) around a msgbox it will require that a variable be
equal to that function to store the user's answer.

i would bet it's the time that is throwing off the comparison.
 
Thanks Ken...the statement "If DateValue(CDate([Text377])) <> VBA.Date Then"
fixed the problem !

KenSheridan via AccessMonster.com said:
As you are declaring the CurrentDate variable as a Variant (the default)
rather than as Date its possible that its value is being interpreted as an
arithmetical expression rather than a date when compared with the value of
Text377. This would almost certainly (depending on your default system short
date format) evaluate to a date in the late 19th century because of the way
Access implements date/time data as a 64 bit floating point number with its
origin at 30 December 1899 00:00:00. This variable is unnecessary in any
case as you can call the VBA Date function directly:

If [Text377] <> VBA.Date Then

If that doesn't on its own work then also try returning Text377 as a
date/time data type by means of the CDate function, using the DateValue
function to cater for any values with non-zero times of day:

If DateValue(CDate([Text377])) <> VBA.Date Then

Ken Sheridan
Stafford, England
I'm wrote the following code to compare two different date fields, "Text377"
and "CurrentDate". When I view the data the dates they are the same, but
when I execute the following code, I receive the MsgBox error. Any idea
what's wrong ?

Private Sub Form_Load()
Dim CurrentDate
CurrentDate = Date

If [Text377] <> [CurrentDate] Then
MsgBox "Meeting Schedule Not Current", vbOKOnly, "Error!"
DoCmd.Close acForm, "Meeting Schedule List"
DoCmd.OpenForm "Decision Form"
End If
End Sub
Click to expand...
Click to expand...
 
Back
Top