date function

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hey,

I have a text box with the following code (listed in the
control source line of the properties) below. I would
like the message, "Class starts within 1 week" to display
if the date/time is 7 days from now. This is not doing
the trick. Any suggestions?

=IIf([ClassDate] & [ClassTime]=Now()+7,"Class starts
within 1 week","Upcoming Class")

Thanks a million.
 
Mark,

([ClassDate] & [ClassTime]= is giving you the main problem.
You cannot combine two fields for one comparison like this.
I am going to take the liberty of assuming that you really
want any class a week or less away to display the message,
not just the ones exactly seven days out. If this is the
case, the ClassTime is irrelevant and the following should
do.

=IIf(Me![ClassDate] > Date() - 8,"Class starts within 1
week","Upcoming Class")

Gary Miller
Sisters, OR
 
Try using the datediff function as follows.

IIf(DateDiff("d",[ClassDate],now())=7,"Class starts
within 1 week","Upcoming Class")

Robert Taylor
 
Since Dates and Times are just numbers, you can use arithmetic on them:
[ClassDate] + [ClassTime]. You're correct that you can't use concatenation
on them though.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Gary Miller said:
Mark,

([ClassDate] & [ClassTime]= is giving you the main problem.
You cannot combine two fields for one comparison like this.
I am going to take the liberty of assuming that you really
want any class a week or less away to display the message,
not just the ones exactly seven days out. If this is the
case, the ClassTime is irrelevant and the following should
do.

=IIf(Me![ClassDate] > Date() - 8,"Class starts within 1
week","Upcoming Class")

Gary Miller
Sisters, OR

Mark said:
Hey,

I have a text box with the following code (listed in the
control source line of the properties) below. I would
like the message, "Class starts within 1 week" to display
if the date/time is 7 days from now. This is not doing
the trick. Any suggestions?

=IIf([ClassDate] & [ClassTime]=Now()+7,"Class starts
within 1 week","Upcoming Class")

Thanks a million.
Click to expand...
Click to expand...
 
that almost did the trick. it still isn't returning the
correct message, but you got me looking in the right
direction. thanks!!!

-----Original Message-----
Try using the datediff function as follows.

IIf(DateDiff("d",[ClassDate],now())=7,"Class starts
within 1 week","Upcoming Class")

Robert Taylor
-----Original Message-----
Hey,

I have a text box with the following code (listed in the
control source line of the properties) below. I would
like the message, "Class starts within 1 week" to display
if the date/time is 7 days from now. This is not doing
the trick. Any suggestions?

=IIf([ClassDate] & [ClassTime]=Now()+7,"Class starts
within 1 week","Upcoming Class")

Thanks a million.
.
Click to expand...
.
Click to expand...
 
Thanks Doug, good tip. I couldn't really see where the time
was critical for him in this case.

Gary

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com>
wrote in message
Since Dates and Times are just numbers, you can use arithmetic on them:
[ClassDate] + [ClassTime]. You're correct that you can't use concatenation
on them though.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Mark,

([ClassDate] & [ClassTime]= is giving you the main problem.
You cannot combine two fields for one comparison like this.
I am going to take the liberty of assuming that you really
want any class a week or less away to display the message,
not just the ones exactly seven days out. If this is the
case, the ClassTime is irrelevant and the following should
do.

=IIf(Me![ClassDate] > Date() - 8,"Class starts within 1
week","Upcoming Class")

Gary Miller
Sisters, OR

Mark said:
Hey,

I have a text box with the following code (listed in the
control source line of the properties) below. I would
like the message, "Class starts within 1 week" to display
if the date/time is 7 days from now. This is not doing
the trick. Any suggestions?

=IIf([ClassDate] & [ClassTime]=Now()+7,"Class starts
within 1 week","Upcoming Class")

Thanks a million.
Click to expand...
Click to expand...
Click to expand...
 
It seems to me that there are 3 possibilities and you left out the case that
the Class has already started. Also, the Text is not consistent with the
criteria you posted.

Assuming that you were aware of the 3rd possibility and the data will have
no ClassDate that is today or earlier, you can try:

=IIf([ClassDate] <= Date() + 7, "Class starts within 1 week.",
"Upcoming Class")

HTH
Van T. Dinh
MVP (Access)
 
Back
Top