date function

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

 

[Gary Miller]

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

 

[Robert Taylor]

Try using the datediff function as follows.

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

Robert Taylor

 

[Douglas J. Steele]

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

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]

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

.

 

[Gary Miller]

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

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.

 

[Van T. Dinh]

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)