Calculating subscription length when cancel date = null

[Guest]

Good morning. Trying to get my feet wet with SQL and Access 2000. Working
on a customer sub database (approx 300K records) that updates daily/monthly
with new records.

Records are imported from one of two files: Signups or Cancellations. The
records themselves are identical in each file (i.e. SKU, signup date, name,
email, address, so on), with the addition of a cancel date in the
Cancellations file.

The two are merged, updated, etc. and a master list results with Current
subs and Cancelled subs. Current subs have a [Cancel Date] of null.

In the query I am building, I want to calculate [Sub Length] based on the
difference between Cancel Date and Create Date. Easy enough when the value
is not null. For a null value, the query should always calculate sub length
using today's date (Date()) - [Create Date].

I pulled an example from another site that looked like this:

Function SubLength(CancelDate As Variant) As Integer
If IsNull(CancelDate) Then
SubLength = Date()-CancelDate
Exit Function
End If
SubLength = DateDiff(?yyyy?, CreateDate, CancelDate)
End Function

I couldn't quite get this to work in expression builder. Sorry for my
ignorance, just getting started with this type of query.

Thanks,
Don

 

[John Viescas]

SELECT ... (NZ([CancelDate], Date()) - [CreateDate]) As SubscriptionLength
FROM MyTable

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Guest]

John,

Thank you for the prompt response.

My modified code looks like:
SELECT Avg(NZ([MASTER LIST]![CANCEL DATE], Date())-[MASTER LIST]![CREATE
DATE]) AS SubLength
FROM [MASTER LIST];

My interpretation of this is if [Cancel Date] is null, convert to the
current date, else keep the existing value of [Cancel Date], then subtract
[Create Date] which results in the value SubLength. Is that correct?

 

[John Viescas]

Yup, correct. NZ (NullToZero) is one of the most useful functions you can
use in a query.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)