By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,761 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
1 Reply


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.