Date Query

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

Thanks Ofer
Works fine.

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

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.

 

[Guest]

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

 

[Guest]

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

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

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.

 

[Guest]

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
_____________________________________________

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

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.