Test if Calculated Date field equal to Today

[mma40]

I need to evaluate if LastVisited Field is equal to today with an if
statement but can't get it to work. The Field is calculated as
=DMax("VisitDate", "VisitTable", "ClientID = " & Nz([ClientID],0)). I
am using Short Date format of 01/01/2006

I am using the following and have tried other variations:

If DateValue(Me.LastVisited) = Date Then
MsgBox "Today's Visit Already Exits"
End If

Any ideas?

 

[fredg]

I need to evaluate if LastVisited Field is equal to today with an if
statement but can't get it to work. The Field is calculated as
=DMax("VisitDate", "VisitTable", "ClientID = " & Nz([ClientID],0)). I
am using Short Date format of 01/01/2006

I am using the following and have tried other variations:

If DateValue(Me.LastVisited) = Date Then
MsgBox "Today's Visit Already Exits"
End If

Any ideas?

The format of the date is not important here.
Try this:

If DMax("[VisitDate]", "VisitTable", "[ClientID] = " & [ClientID]) =
Date Then
MsgBox "Today's Visit Already Exits"
End If

 

[mma40]

I tried it but it still does not work. The message is never displayed.

 

[fredg]

I tried it but it still does not work. The message is never displayed.

It's helpful to include the relevant portion of the previous message
that you are referring to. I doubt very much that any of us who
respond to questions remember more than a small fraction of the
messages we reply to. Having it on the same page as your follow-up
makes our work easier ... which helps you.
Also, it's important to include your exact code, copied and pasted, as
oftentimes it may be a simple error in copying that is causing your
problem.

Your DMax criteria is written as though [ClientID] is a Number
datatype. Is it?

If so, what value do you get when your use
msgBox Format(DMax("[VisitDate]", "VisitTable", "[ClientID] = " &
[ClientID]),"General Date")

If it includes a time value, then that is why you are not seeing any
messages. 3/3/2006 09:58:00 AM is not the same as 3/3/2006 (which is
the value returned by Date.
Instead use
If Format(DMax("[VisitDate]", "VisitTable", "[ClientID] = " &
[ClientID]),"mm/dd/yyyy") = format(Date,"mm/dd/yyyy") Then
etc....

 

[Douglas J Steele]

In an attempt to debug, try using:

If DateValue(Me.LastVisited) = Date Then
MsgBox "Today's Visit Already Exits"
Else
MsgBox "The last visit was " & _
DateValue(Me.LastVisited) & _
", and today is " & Date
End If

What shows up?

 

[Guest]

What Data Type is LastVisited?

 

[mma40]

With using the debug (great trick), I found that it said Last Visit was
3/3/2006 and today is 12/21/2001. Which set off a flag of a previous
thread that they said I should not use Date as a field name. Once I
looked back at my form, I found the the MembershipDate was named Date.
Once corrected, the
If DMax("VisitDate", "UserDates", "UserNumber = " & Nz([UserNumber],
0)) = Date Then
MsgBox "Today's Visit Already Exits"
End If

worked perfectly. Thanks to you both.

 

[John Vinson]

I tried it but it still does not work. The message is never displayed.

Does the field have a time component? The format is irrelevant - if
you have a date and time in the field (e.g. you're using the Now()
function to fill it), then the time value is causing the value to not
match.

Try

If DateValue([LastVisted]) = Date() Then ...

John W. Vinson[MVP]

 

[John Vinson]

I found that it said Last Visit was
3/3/2006 and today is 12/21/2001. Which set off a flag of a previous
thread that they said I should not use Date as a field name.

SNORK!!!!

Yep. That's EXACTLY why you shouldn't do that.

I know nobody likes to be held up as a Horrible Example, but may I
strip this post of identifying information and use it as one? <g>

John W. Vinson[MVP]

 

[mma40]

Sure. I had already changed the field to MembershipDate but forgot the
label on the form. (details, details)