Date Query

B

baconroll

There is a database that has the following:

Date of Notification to Sponsor field
Date Case Closed field

I need to calculate days open - I put this info in the query:
Days Open: [Date Case Closed]-[Date of Notification to Sponsor].
This works fine.

Is there a way that I can say:
If Date Case Closed is Null, then use the system date [Date()]-[Date of
Notification to Sponsor]?

Any help would be greatly appreciated.
 
O

Ofer

You can use that

Nz([Date Case Closed],Date())-[Date of Notification to Sponsor]

Or
DateDiff("d",Nz([Date Case Closed],Date()),[Date of Notification to Sponsor])
 
T

Tom Wickerath

The Nz function is your friend here. You can use this function to convert
null to just about anything you want. Try this:

Days Open: Nz([Date Case Closed],Date())-[Date of Notification to Sponsor]


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

There is a database that has the following:

Date of Notification to Sponsor field
Date Case Closed field

I need to calculate days open - I put this info in the query:
Days Open: [Date Case Closed]-[Date of Notification to Sponsor].
This works fine.

Is there a way that I can say:
If Date Case Closed is Null, then use the system date [Date()]-[Date of
Notification to Sponsor]?

Any help would be greatly appreciated.
 
B

baconroll

Thanks, but this doesn't seem to work. Just returns a blank value. Should
this syntax be a column of it's own?
 
B

baconroll

Thanks Ofer
Works fine.

Ofer said:
You can use that

Nz([Date Case Closed],Date())-[Date of Notification to Sponsor]

Or
DateDiff("d",Nz([Date Case Closed],Date()),[Date of Notification to Sponsor])
--
I hope that helped
Good luck


baconroll said:
There is a database that has the following:

Date of Notification to Sponsor field
Date Case Closed field

I need to calculate days open - I put this info in the query:
Days Open: [Date Case Closed]-[Date of Notification to Sponsor].
This works fine.

Is there a way that I can say:
If Date Case Closed is Null, then use the system date [Date()]-[Date of
Notification to Sponsor]?

Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
O

Ofer

Yes it should

Select [Date Case Closed],[Date of Notification to Sponsor] ,Nz([Date Case
Closed],Date())-[Date of Notification to Sponsor] as DateDifferent From
TableName

--
I hope that helped
Good luck


baconroll said:
Thanks, but this doesn't seem to work. Just returns a blank value. Should
this syntax be a column of it's own?

Tom Wickerath said:
The Nz function is your friend here. You can use this function to convert
null to just about anything you want. Try this:

Days Open: Nz([Date Case Closed],Date())-[Date of Notification to Sponsor]


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

There is a database that has the following:

Date of Notification to Sponsor field
Date Case Closed field

I need to calculate days open - I put this info in the query:
Days Open: [Date Case Closed]-[Date of Notification to Sponsor].
This works fine.

Is there a way that I can say:
If Date Case Closed is Null, then use the system date [Date()]-[Date of
Notification to Sponsor]?

Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
T

Tom Wickerath

No problem. Glad you got it to work!

Please consider marking my original reply as an answer. That way, it will
stay saved for a longer time, so that other people can find it if they search.

Tom
_____________________________________________

baconroll said:
Sorry! - Does work, me have a thick moment.
Thanks for your help

Tom Wickerath said:
The Nz function is your friend here. You can use this function to convert
null to just about anything you want. Try this:

Days Open: Nz([Date Case Closed],Date())-[Date of Notification to Sponsor]


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

There is a database that has the following:

Date of Notification to Sponsor field
Date Case Closed field

I need to calculate days open - I put this info in the query:
Days Open: [Date Case Closed]-[Date of Notification to Sponsor].
This works fine.

Is there a way that I can say:
If Date Case Closed is Null, then use the system date [Date()]-[Date of
Notification to Sponsor]?

Any help would be greatly appreciated.
Click to expand...
Click to expand...
 
Top