445,841 Members | 1,761 Online
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

# Trouble with an Expression

 P: n/a Hello. I am trying to write an expression to help me pull some data out of my database. We have a group of people travelling between two dates - [In] and [Out] Unfortunatley for me, the rooms are different prices in our block (a set of four dates) and outside the block. The block begins on 2/1/07 and ends on 2/4/07 (4 total room nights) The fields I am pulling this info to is [InBlock] and [OutBlock] So... saying the earliest time a presaon will travel to the location is 1/18/07 The latest date a person will come home is 2/10/07. I have been able to deterimine the entire amount of days which I have done here: Expr1: Abs(DateDiff("d",[In],[Out])) and the days inside the block: IIf([Out]>=#2/5/2007#,Abs(DateDiff("d",#2/1/2007#,#2/5/2007#,0))) but I cannot figure out how to get the dates Outside of the block. The reason being is that some people will travel to location [In] and return home [Out] before the block begins. I have tried: OutBlock: IIf([Out]>=#2/5/2007#,Abs(DateDiff("d",[In],[Out]-4,Abs(DateDiff("d",[In],[Out]))))) This only gives me the answer if the [Out] date is >=2/5/07. I have also tried modifying and reversing the formula: OutBlock: IIf([Out]<=#2/1/2007#,Abs(DateDiff("d",[In],[Out],Abs(DateDiff("d",[In],[Out]-4))))) Basically... the formula only works for the first half and does not recognize the 2nd possibility. What am I missing? Thanks! Damon Reynolds Dec 4 '06 #1

 P: n/a Nevermind... I just figured it out. OutBlock: IIf([Out]<=#2/1/2007#,Abs(DateDiff("d",[In],[Out])),Abs(DateDiff("d",[In],[Out]-4))) Whew. da************@films.nfl.com wrote: Hello. I am trying to write an expression to help me pull some data out of my database. We have a group of people travelling between two dates - [In] and [Out] Unfortunatley for me, the rooms are different prices in our block (a set of four dates) and outside the block. The block begins on 2/1/07 and ends on 2/4/07 (4 total room nights) The fields I am pulling this info to is [InBlock] and [OutBlock] So... saying the earliest time a presaon will travel to the location is 1/18/07 The latest date a person will come home is 2/10/07. I have been able to deterimine the entire amount of days which I have done here: Expr1: Abs(DateDiff("d",[In],[Out])) and the days inside the block: IIf([Out]>=#2/5/2007#,Abs(DateDiff("d",#2/1/2007#,#2/5/2007#,0))) but I cannot figure out how to get the dates Outside of the block. The reason being is that some people will travel to location [In] and return home [Out] before the block begins. I have tried: OutBlock: IIf([Out]>=#2/5/2007#,Abs(DateDiff("d",[In],[Out]-4,Abs(DateDiff("d",[In],[Out]))))) This only gives me the answer if the [Out] date is >=2/5/07. I have also tried modifying and reversing the formula: OutBlock: IIf([Out]<=#2/1/2007#,Abs(DateDiff("d",[In],[Out],Abs(DateDiff("d",[In],[Out]-4))))) Basically... the formula only works for the first half and does not recognize the 2nd possibility. What am I missing? Thanks! Damon Reynolds Dec 5 '06 #2

### This discussion thread is closed

Replies have been disabled for this discussion.