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

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

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

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

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
> .
> 

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

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