Date Format

I am working with a government database that has a number field that contains
date information formatted to read 20080623. I am trying to get the
difference between two dates, but it's showing the difference between
20080313 and 20080417 as 104 days. I've tried a number of fixes shown for
others, but I keep getting a message that the fix is too complicated. Any
ideas on how to fix this so that a beginner can figure it out?

Convert the number into a true date/time field like this:
DateSerial(Left([d],4), Mid([d],5,2), Right([d],2))
where d represents the name of your numeric field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jim" wrote in message
...
I am working with a government database that has a number field that
contains
date information formatted to read 20080623. I am trying to get the
difference between two dates, but it's showing the difference between
20080313 and 20080417 as 104 days. I've tried a number of fixes shown for
others, but I keep getting a message that the fix is too complicated. Any
ideas on how to fix this so that a beginner can figure it out?

Jim wrote:
I am working with a government database that has a number field that
contains date information formatted to read 20080623. I am trying to
get the difference between two dates, but it's showing the difference
between 20080313 and 20080417 as 104 days. I've tried a number of
fixes shown for others, but I keep getting a message that the fix is
too complicated. Any ideas on how to fix this so that a beginner can
figure it out?
You need to convert them to dates and then use DateDiff().

I believe I have a function in my library to convert these integers to dates
.... let's see ... yes, here it is:

Public Function ConvIntToDate(ByVal num As Long) As Date
'Converts integers that represent dates in yyyymmdd or yymmdd format
'to actual dates
'Returns 12/31/9999 if there's an error

On Error GoTo Err_Handler
Dim s as string
s=CStr(num)
If num 19000000 Then
ConvIntToDate = Cdate(Mid(s, 3, 2) & "/" & _
Right(s, 2) & "/" & Left(s, 2))
Else
ConvIntToDate = DateSerial(Left(s, 4),Mid(s, 5, 2), _
Right(s, 2))
End If

Exit_Func:
Exit Function

Err_Handler:
ConvIntToDate = #12/31/9999#
End Function

Just copy this into a module in your database. Then, in your query, use it
like this:

DateDifference: DateDiff("d",ConvIntToDate([Date1]),ConvIntToDate([Date2]))

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

And how many days are actually between these two dates ... ?
Fewer than 104 I would imagine ... :-)

Jeff Boyce wrote:
Jim

I'm not understanding your problem...

When I subtract 313 from 417, I get 104.


"Jim" wrote in message
...
I am working with a government database that has a number field that
contains date information formatted to read 20080623. I am trying to
get the difference between two dates, but it's showing the
difference between 20080313 and 20080417 as 104 days. I've tried a
number of fixes shown for others, but I keep getting a message that
the fix is too complicated. Any ideas on how to fix this so that a
beginner can figure it out?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

As presented, the numbers are in yyyymmdd format.
Using Allen Browne's example:

x = 20080313
x = dateserial(left(x, 4), mid(x,5,2), mid(x, 7,2))
y = 20080417
y = dateserial(left(y, 4), mid(y,5,2), mid(y, 7,2))
? x
3/13/2008
? y
4/17/2008
? datediff("d", x, y)
35

Bob

Bob Barrows [MVP] wrote:
I am working with a government database that has a number field that
contains date information formatted to read 20080623. I am trying to
[quoted text clipped - 3 lines]
too complicated. Any ideas on how to fix this so that a beginner can
figure it out?
You need to convert them to dates and then use DateDiff().

I believe I have a function in my library to convert these integers to dates
... let's see ... yes, here it is:

Public Function ConvIntToDate(ByVal num As Long) As Date
'Converts integers that represent dates in yyyymmdd or yymmdd format
'to actual dates
'Returns 12/31/9999 if there's an error

On Error GoTo Err_Handler
Dim s as string
s=CStr(num)
If num 19000000 Then
ConvIntToDate = Cdate(Mid(s, 3, 2) & "/" & _
Right(s, 2) & "/" & Left(s, 2))
Else
ConvIntToDate = DateSerial(Left(s, 4),Mid(s, 5, 2), _
Right(s, 2))
End If

Exit_Func:
Exit Function

Err_Handler:
ConvIntToDate = #12/31/9999#
End Function

Just copy this into a module in your database. Then, in your query, use it
like this:

DateDifference: DateDiff("d",ConvIntToDate([Date1]),ConvIntToDate([Date2]))


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200806/1

I'm not sure I understand your point.
You seem to be doing the same thing I do in my function.

raskew via AccessMonster.com wrote:
As presented, the numbers are in yyyymmdd format.
Using Allen Browne's example:

x = 20080313
x = dateserial(left(x, 4), mid(x,5,2), mid(x, 7,2))
y = 20080417
y = dateserial(left(y, 4), mid(y,5,2), mid(y, 7,2))
? x
3/13/2008
? y
4/17/2008
? datediff("d", x, y)
35

Bob

Bob Barrows [MVP] wrote:
I am working with a government database that has a number field that
contains date information formatted to read 20080623. I am trying to
[quoted text clipped - 3 lines]
too complicated. Any ideas on how to fix this so that a beginner can
figure it out?
You need to convert them to dates and then use DateDiff().

I believe I have a function in my library to convert these integers
to dates ... let's see ... yes, here it is:

Public Function ConvIntToDate(ByVal num As Long) As Date
'Converts integers that represent dates in yyyymmdd or yymmdd format
'to actual dates
'Returns 12/31/9999 if there's an error

On Error GoTo Err_Handler
Dim s as string
s=CStr(num)
If num 19000000 Then
ConvIntToDate = Cdate(Mid(s, 3, 2) & "/" & _
Right(s, 2) & "/" & Left(s, 2))
Else
ConvIntToDate = DateSerial(Left(s, 4),Mid(s, 5, 2), _
Right(s, 2))
End If

Exit_Func:
Exit Function

Err_Handler:
ConvIntToDate = #12/31/9999#
End Function

Just copy this into a module in your database. Then, in your query,
use it like this:

DateDifference:
DateDiff("d",ConvIntToDate([Date1]),ConvIntToDate([Date2]))

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Should have been more concise. Was attempting to clear
up this misconception.

When I subtract 313 from 417, I get 104.

Bob
Bob Barrows [MVP] wrote:
I'm not sure I understand your point.
You seem to be doing the same thing I do in my function.

As presented, the numbers are in yyyymmdd format.
Using Allen Browne's example:
[quoted text clipped - 50 lines]
DateDifference:
DateDiff("d",ConvIntToDate([Date1]),ConvIntToDate([Date2]))


--
Message posted via http://www.accessmonster.com

Ah! You replied to the wrong post ... I see now.

raskew via AccessMonster.com wrote:
Should have been more concise. Was attempting to clear
up this misconception.

When I subtract 313 from 417, I get 104.

Bob
Bob Barrows [MVP] wrote:
I'm not sure I understand your point.
You seem to be doing the same thing I do in my function.

As presented, the numbers are in yyyymmdd format.
Using Allen Browne's example:
[quoted text clipped - 50 lines]
DateDifference:
DateDiff("d",ConvIntToDate([Date1]),ConvIntToDate([Date2]))

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Jim

I'm not understanding your problem...

When I subtract 313 from 417, I get 104.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


"Jim" wrote in message
...
I am working with a government database that has a number field that
contains
date information formatted to read 20080623. I am trying to get the
difference between two dates, but it's showing the difference between
20080313 and 20080417 as 104 days. I've tried a number of fixes shown for
others, but I keep getting a message that the fix is too complicated. Any
ideas on how to fix this so that a beginner can figure it out?

You can also return a true date/time value with:

CDate(Format([TheDateField],"0000-00-00"))

YYYY-MM-DD is the ISO standard for date notation, so returning your number
in this format allows the CDate function to use it as an argument to return a
value of date/time data type, with which you can then do normal date
arithmetic.

In fact the DateDiff function will accept the string expressions without
converting them to true date/time values. To try it out enter the following
as a single line in the debug window:

? DateDiff("d",Format(20080313,"0000-00-00"),Format(20080417,"0000-00-00"))

Ken Sheridan
Stafford, England

"Jim" wrote:

I am working with a government database that has a number field that contains
date information formatted to read 20080623. I am trying to get the
difference between two dates, but it's showing the difference between
20080313 and 20080417 as 104 days. I've tried a number of fixes shown for
others, but I keep getting a message that the fix is too complicated. Any
ideas on how to fix this so that a beginner can figure it out?