calculate between two days if the second date is blank.

[Guest]

I am trying to figure out how to calculate between two days if the second
date is blank. Currently, I run a query that subtracks CheckOutDate by
CheckInDate. and gives me the number of nights. Is there a way to say if
CheckOutDate Null calculate by Now() or by Date Entered []?

Nights: [CheckOutDate]-[CheckInDate]

 

[pietlinden]

I am trying to figure out how to calculate between two days if the second
date is blank. Currently, I run a query that subtracks CheckOutDate by
CheckInDate. and gives me the number of nights. Is there a way to say if
CheckOutDate Null calculate by Now() or by Date Entered []?

Nights: [CheckOutDate]-[CheckInDate]

use an IIF statement in your query.

iif(IsNull([CheckInDate]), function1, function2)
Then use DateDiff with Date() and the CheckinDate. There are examples
in the helpfile.

 

[Guest]

This uses today if the [CheckOutDate] is null.

Nights: IIF(DateDiff(IIf([CheckOutDate] Is Null, Date(),
[CheckOutDate])-[CheckInDate] =0, 1, DateDiff(IIf([CheckOutDate] Is Null,
Date(), [CheckOutDate])-[CheckInDate])

This compare the checkin to checkout and if the same ( zero days difference
) it displays one as the number of nights.

 

[Guest]

I am getting to many arguments when I try to input your information. Could
you try it again. I included an example below. Thank You.

Currently I have a query that states Nights:[CheckOutDate]-[CheckInDate].
The problem is if there is no checkout date yet, how can I get it to run
either today or by date input.


Check In Date Check Out Date Nights
09/01/2006 09/05/2006 4
09/10/2006 09/11/2006 1
09/12/2006

--
Rose

This uses today if the [CheckOutDate] is null.

Nights: IIF(DateDiff(IIf([CheckOutDate] Is Null, Date(),
[CheckOutDate])-[CheckInDate] =0, 1, DateDiff(IIf([CheckOutDate] Is Null,
Date(), [CheckOutDate])-[CheckInDate])

This compare the checkin to checkout and if the same ( zero days difference
) it displays one as the number of nights.

I am trying to figure out how to calculate between two days if the second
date is blank. Currently, I run a query that subtracks CheckOutDate by
CheckInDate. and gives me the number of nights. Is there a way to say if
CheckOutDate Null calculate by Now() or by Date Entered []?

Nights: [CheckOutDate]-[CheckInDate]

 

[Guest]

I am not familiar with Iff statements and the helpline just conflused me
more. Could you help me out. I included an example below. Thank You.

Currently I have a query that states Nights:[CheckOutDate]-[CheckInDate].
The problem is if there is no checkout date yet, how can I get it to run
either today or by date input.


Check In Date Check Out Date Nights
09/01/2006 09/05/2006 4
09/10/2006 09/11/2006 1
09/12/2006
--
Rose

I am trying to figure out how to calculate between two days if the second
date is blank. Currently, I run a query that subtracks CheckOutDate by
CheckInDate. and gives me the number of nights. Is there a way to say if
CheckOutDate Null calculate by Now() or by Date Entered []?

Nights: [CheckOutDate]-[CheckInDate]

use an IIF statement in your query.

iif(IsNull([CheckInDate]), function1, function2)
Then use DateDiff with Date() and the CheckinDate. There are examples
in the helpfile.

 

[Guest]

I had several things wrong in it.
Nights: IIf(DateDiff("d",[CheckInDate],IIf([CheckOutDate] Is
Null,Date(),[CheckOutDate]))=0,1,DateDiff("d",[CheckInDate],IIf([CheckOutDate] Is Null,Date(),[CheckOutDate])))

I am getting to many arguments when I try to input your information. Could
you try it again. I included an example below. Thank You.

Currently I have a query that states Nights:[CheckOutDate]-[CheckInDate].
The problem is if there is no checkout date yet, how can I get it to run
either today or by date input.


Check In Date Check Out Date Nights
09/01/2006 09/05/2006 4
09/10/2006 09/11/2006 1
09/12/2006

--
Rose

This uses today if the [CheckOutDate] is null.

Nights: IIF(DateDiff(IIf([CheckOutDate] Is Null, Date(),
[CheckOutDate])-[CheckInDate] =0, 1, DateDiff(IIf([CheckOutDate] Is Null,
Date(), [CheckOutDate])-[CheckInDate])

This compare the checkin to checkout and if the same ( zero days difference
) it displays one as the number of nights.

I am trying to figure out how to calculate between two days if the second
date is blank. Currently, I run a query that subtracks CheckOutDate by
CheckInDate. and gives me the number of nights. Is there a way to say if
CheckOutDate Null calculate by Now() or by Date Entered []?

Nights: [CheckOutDate]-[CheckInDate]