calculate between two days if the second date is blank.

R

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]
 
P

pietlinden

Rose said:
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.
 
K

KARL DEWEY

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

Rose

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


KARL DEWEY said:
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.

Rose said:
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]
 
R

Rose

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

KARL DEWEY

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])))

Rose said:
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


KARL DEWEY said:
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.

Rose said:
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top