Date Subtraction in MSAccess

[Laura1 via AccessMonster.com]

I have two date fields. The underlying data has them stored as the following:


Sales Event date: 1/6/2009
Created date:1/6/2009 5:09:23 PM

I have converted both of them to short date and did this calculation SEV-
CREDATE: Int(Tbl_CMO_SLS_CT_STATUS![Sales Event Date]-Tbl_CMO_SLS_CT_STATUS!
[Created Date])

It is still taking into consideration the time in the created date field.

Can anyone help. I just one a single number. The calculation for the above
should be 0 I am getting -1 because of the time.

 

[Jeff Boyce]

Take a look at the DateDiff() function in Access HELP for syntax and
example.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Laura1 via AccessMonster.com]

I am missing it, datediff appears to give you the week day translation.
SORRY, additional help?

Take a look at the DateDiff() function in Access HELP for syntax and
example.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two date fields. The underlying data has them stored as the
following:

[quoted text clipped - 12 lines]

above
should be 0 I am getting -1 because of the time.

 

[Laura1 via AccessMonster.com]

I am missing it, datediff appears to give you the week day translation.
SORRY, additional help?

Take a look at the DateDiff() function in Access HELP for syntax and
example.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two date fields. The underlying data has them stored as the
following:

[quoted text clipped - 12 lines]

above
should be 0 I am getting -1 because of the time.

 

[John Spencer]

Use the DateDiff function
Sev-CreDate: DateDiff("D",[Sales Event Date],[Created Date])

Or this one which will generate an error if either date is Null (Blan)
CLng([Sales Event Date]) - CLng([Created Date])

You can "fix" that by using the NZ function to convert nulls in the
dates to zero before using the CLng function. Or you can test both
dates with IsDate before trying to convert

IIF(IsDate([Sales Event Date]) AND IsDate([Created Date]
, CLng([Sales Event Date]) - CLng([Created Date]) ,Null)

Or you can replace Null with some known value to indicate the value
cannot be calculated (-9999 for instance)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Laura1 via AccessMonster.com]

John:

Thank you so much!! The first worked like a charm... I never would have
figured that out w/o your code!!! GREAT HELP! THANK YOU!

Use the DateDiff function
Sev-CreDate: DateDiff("D",[Sales Event Date],[Created Date])

Or this one which will generate an error if either date is Null (Blan)
CLng([Sales Event Date]) - CLng([Created Date])

You can "fix" that by using the NZ function to convert nulls in the
dates to zero before using the CLng function. Or you can test both
dates with IsDate before trying to convert

IIF(IsDate([Sales Event Date]) AND IsDate([Created Date]
, CLng([Sales Event Date]) - CLng([Created Date]) ,Null)

Or you can replace Null with some known value to indicate the value
cannot be calculated (-9999 for instance)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have two date fields. The underlying data has them stored as the following:

[quoted text clipped - 9 lines]

Can anyone help. I just one a single number. The calculation for the above
should be 0 I am getting -1 because of the time.

 

[Jeff Boyce]

Not sure what you mean by "week day translation". The syntax for DateDiff()
allows for the difference in minutes, hours, days, ... you pick.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am missing it, datediff appears to give you the week day translation.
SORRY, additional help?

Take a look at the DateDiff() function in Access HELP for syntax and
example.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two date fields. The underlying data has them stored as the
following:

[quoted text clipped - 12 lines]

above
should be 0 I am getting -1 because of the time.