If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Expression builder in a Form
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. -- Johnwats |
#2
|
|||
|
|||
Expression builder in a Form
You could use and IIf statement for this:
=DateDiff("d",[StartDate], IIf(Date[EndDate],Date,[EndDAte)) -- Dave Hargis, Microsoft Access MVP "Johnwats" wrote: 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. -- Johnwats |
#3
|
|||
|
|||
Expression builder in a Form
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 "Klatuu" wrote: You could use and IIf statement for this: =DateDiff("d",[StartDate], IIf(Date[EndDate],Date,[EndDAte)) -- Dave Hargis, Microsoft Access MVP "Johnwats" wrote: 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. -- Johnwats |
#4
|
|||
|
|||
Expression builder in a Form
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 "Johnwats" wrote: 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 "Klatuu" wrote: You could use and IIf statement for this: =DateDiff("d",[StartDate], IIf(Date[EndDate],Date,[EndDAte)) -- Dave Hargis, Microsoft Access MVP "Johnwats" wrote: 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. -- Johnwats |
Thread Tools | |
Display Modes | |
|
|