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
|
|||
|
|||
days between tweak
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 |
#2
|
|||
|
|||
days between tweak
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 |
#3
|
|||
|
|||
days between tweak
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 |
#4
|
|||
|
|||
days between tweak
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 |
#5
|
|||
|
|||
days between tweak
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 . |
#6
|
|||
|
|||
days between tweak
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 |
Thread Tools | |
Display Modes | |
|
|