Prev: Query between 2 tables with one-to-many relationship
Next: Help with 3 scenerio query
From: Bibi on 21 Apr 2010 15:31
I do not know how to tweak the code below to calculate the number of days
available from the current day in those cases where the first day available
is before the current date.

Could someone help with an " If ...........then.........else...........

Here's the code:

[LastDayAvailable]-[Depart] AS [Days Available]

I need to add something here to show that if the [LastDayAvailable] is
before the current Date() then [Days Available] should be calculated from
Date()- [Depart] rather than from the [LastDayAvailalble].

All help appreciated.

TIA
Bibi
From: John Spencer on 21 Apr 2010 15:38
IIF([LastDayAvailable]>Date(),[LastDayAvailable],Date()) - [Days Available]

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Bibi wrote:
> I do not know how to tweak the code below to calculate the number of days
> available from the current day in those cases where the first day available
> is before the current date.
>
> Could someone help with an " If ...........then.........else...........
>
> Here's the code:
>
> [LastDayAvailable]-[Depart] AS [Days Available]
>
> I need to add something here to show that if the [LastDayAvailable] is
> before the current Date() then [Days Available] should be calculated from
> Date()- [Depart] rather than from the [LastDayAvailalble].
>
> All help appreciated.
>
> TIA
> Bibi
From: golfinray on 21 Apr 2010 15:49
Try a datediff command:
Datediff("d", [first date],[second date])
That will give you days, even if it is neagative.
--
Milton Purdy
ACCESS
State of Arkansas


"Bibi" wrote:

> I do not know how to tweak the code below to calculate the number of days
> available from the current day in those cases where the first day available
> is before the current date.
>
> Could someone help with an " If ...........then.........else...........
>
> Here's the code:
>
> [LastDayAvailable]-[Depart] AS [Days Available]
>
> I need to add something here to show that if the [LastDayAvailable] is
> before the current Date() then [Days Available] should be calculated from
> Date()- [Depart] rather than from the [LastDayAvailalble].
>
> All help appreciated.
>
> TIA
> Bibi
From: Bibi on 21 Apr 2010 16:53
I don't think that will do it - It is still calculating the difference
between both dates , not from the current date in those instances when the
start date is BEFORE the current date......
I'm sorry I'm not expressing this very clearly..........
For example:
If a home becomes vacant on April 5th and is not booked until April 20th -
at the start, it is available for 15 days..... but on April 12th it is only
available for 8 days .
So for those cases where the start date is BEFORE the current date I need a
different calculation that if the start date is on or after the current date
.........
TIA
Bibi


"golfinray" wrote:

> Try a datediff command:
> Datediff("d", [first date],[second date])
> That will give you days, even if it is neagative.
> --
> Milton Purdy
> ACCESS
> State of Arkansas
>
>
> "Bibi" wrote:
>
> > I do not know how to tweak the code below to calculate the number of days
> > available from the current day in those cases where the first day available
> > is before the current date.
> >
> > Could someone help with an " If ...........then.........else...........
> >
> > Here's the code:
> >
> > [LastDayAvailable]-[Depart] AS [Days Available]
> >
> > I need to add something here to show that if the [LastDayAvailable] is
> > before the current Date() then [Days Available] should be calculated from
> > Date()- [Depart] rather than from the [LastDayAvailalble].
> >
> > All help appreciated.
> >
> > TIA
> > Bibi
From: Bibi on 21 Apr 2010 17:13
Thank you - i"m getting closer but I'm getting a circular reference if I
replace my initial code
[LastDayAvailable]-[Depart] AS [Days Available]
with the new code...........and the alternates I've tried aren't working -
I'm sorry I'm being so dense on this.....
--
TIA
Bibi


"John Spencer" wrote:

> IIF([LastDayAvailable]>Date(),[LastDayAvailable],Date()) - [Days Available]
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Bibi wrote:
> > I do not know how to tweak the code below to calculate the number of days
> > available from the current day in those cases where the first day available
> > is before the current date.
> >
> > Could someone help with an " If ...........then.........else...........
> >
> > Here's the code:
> >
> > [LastDayAvailable]-[Depart] AS [Days Available]
> >
> > I need to add something here to show that if the [LastDayAvailable] is
> > before the current Date() then [Days Available] should be calculated from
> > Date()- [Depart] rather than from the [LastDayAvailalble].
> >
> > All help appreciated.
> >
> > TIA
> > Bibi
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Query between 2 tables with one-to-many relationship
Next: Help with 3 scenerio query