IIF Statement to Equal todays date?

[Zdiane]

Hi all, First Post
I am currently trying to make a database based on a Video Shop for my ITQ course and I am completely confused why my IIF statements wont show the dates to equal todays date:

I am Using Date Add to give me a date 2 days+
DateDue: DateAdd("d",2,[DateRented])
then using another to get dates below current date
OverDue: IIf([DateDue]<Date(),"Over Due")

but when it comes to Current date I use
DueToday: IIf([DateDue]=Date(),"Due Today")
Nothing happens ?
Whyyy????
Is It possible to show todays date this way?
Diane

 

[NickHa]

What is the value in DateRented? Is it a date and time? If so, the comparison will fail because the time part will never match. You could try:

DateDue = Int(DateAdd("d",2,[DateRented))

which will force the result to the date part only.
Incidentally, your IIf statements must have a false part as well as a true part, because both are evaluated before the result is returned. e.g.

Overdue = IIf(datDueDate < Date, "Overdue", vbNullString)

 

[Zdiane]

Thnx for reply
The [DateRented] is set to Short Date & also the [DueDate] Field ,
This was the original I was working
=IIf([Due Date]<Date(),"OVERDUE",IIf([Due Date]=Date(),"Due today","Not Yet Due"))
(From Microsoft Access Site)

but found it to return opposite results

If I use this:
OverDue: IIf([DateDue]=Date(),"Over Due","") Results are Blank
I even tried
OverDue: IIf([DateDue]=Now(),"Over Due","") which also Results are Blank
So I used this
OverDue: IIf([DateDue]>Date(),"Over Due","")
Results showed the dates as 'Over Due' after and also the date today which is not what I want it to do

This shows both results but still shows todays date as 'Not Due'
OverDue: IIf([DateDue]>Date(),"Over Due","Not Due")

I was trying all separate into three Fields
I'm a total Noob to this..Can You tell ...? lol

 

[NickHa]

Short Date is a format, not a data type. The field defiition should be set to Date/Time and this will be stored as a number with the integer part being the date and the decimal part being the time. The code I gave you before forces the integer part (i.e. the date) to be returned.

Overdue = IIf(datDueDate < Date, "Overdue", vbNullString)

So if you change your code from

OverDue: IIf([DateDue]=Date(),"Over Due","")

to

OverDue: IIf(Int([DateDue])=Date(),"Over Due","")

it should work. Same for the other IIf staments also.

 

[Zdiane]

Worked Excellent!!!, So Much Appreciated ..