days between tweak

[Bibi]

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

 

[John Spencer]

IIF([LastDayAvailable]>Date(),[LastDayAvailable],Date()) - [Days Available]

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

 

[golfinray]

Try a datediff command:
Datediff("d", [first date],[second date])
That will give you days, even if it is neagative.

 

[Bibi]

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

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

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

 

[Bibi]

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

IIF([LastDayAvailable]>Date(),[LastDayAvailable],Date()) - [Days Available]

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

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

.

 

[John Spencer]

IT would help if you posted the entire SQL statement (View: SQL).

As a GUESSS, you are in query design view and have something like the
following (all on one line)

Field: [Days Available]: IIF([LastDayAvailable]>Date(), [LastDayAvailable],
Date())-[Days Available]

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

IIF([LastDayAvailable]>Date(),[LastDayAvailable],Date()) - [Days Available]

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

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