Expression builder in a Form

[Johnwats]

I am setting up a database for students on a course. I want to work out how
long students are taking on the course. In a form i have a start date, then
i have used expression builder to find number of days between the start date
& current date. That worked OK. But at some point i will be entering an end
date to the course. How do i change the expression so that it "stops" at the
end date(when entered), rather than keep on calculating to the current date?
I want the expression to somehow compare the calculated date to the end date
& use whichever is the less, but i am completely stuck.

Using Access 2000 in my first attempt at a database. Any help appreciated.

 

[Klatuu]

You could use and IIf statement for this:

=DateDiff("d",[StartDate], IIf(Date<[EndDate],Date,[EndDAte))

 

[Johnwats]

Thanks for the reply. I couldn't get it to work, got an error #Name. Is the
current date in your example, i am afraid i can't understand the syntax.
--
Johnwats

You could use and IIf statement for this:

=DateDiff("d",[StartDate], IIf(Date<[EndDate],Date,[EndDAte))
--
Dave Hargis, Microsoft Access MVP

I am setting up a database for students on a course. I want to work out how
long students are taking on the course. In a form i have a start date, then
i have used expression builder to find number of days between the start date
& current date. That worked OK. But at some point i will be entering an end
date to the course. How do i change the expression so that it "stops" at the
end date(when entered), rather than keep on calculating to the current date?
I want the expression to somehow compare the calculated date to the end date
& use whichever is the less, but i am completely stuck.

Using Access 2000 in my first attempt at a database. Any help appreciated.

 

[Klatuu]

Well, for starters, I left off a closing bracket: v
=DateDiff("d",[StartDate], IIf(Date<[EndDate],Date,[EndDAte))
Should be:
=DateDiff("d",[StartDate], IIf(Date<[EndDate],Date,[EndDate]))
[StartDate] and [EndDate] should both be controls on your form.
--
Dave Hargis, Microsoft Access MVP

Thanks for the reply. I couldn't get it to work, got an error #Name. Is the
current date in your example, i am afraid i can't understand the syntax.
--
Johnwats

You could use and IIf statement for this:

=DateDiff("d",[StartDate], IIf(Date<[EndDate],Date,[EndDAte))
--
Dave Hargis, Microsoft Access MVP

I am setting up a database for students on a course. I want to work out how
long students are taking on the course. In a form i have a start date, then
i have used expression builder to find number of days between the start date
& current date. That worked OK. But at some point i will be entering an end
date to the course. How do i change the expression so that it "stops" at the
end date(when entered), rather than keep on calculating to the current date?
I want the expression to somehow compare the calculated date to the end date
& use whichever is the less, but i am completely stuck.

Using Access 2000 in my first attempt at a database. Any help appreciated.