I receive a text file which I import into an access database, in the text
file each record is dated, however the date reads yyyymmdd (i.e. 20021028)
in integer format.  Is there a way I can change this into a more useable
date in date/time format, such as dd/mm/yyyy?

Many thanks
Ben Mildren



NOTICE AND DISCLAIMER:
This email (including attachments) is confidential.  If you have received
this email in error please notify the sender immediately and delete this
email from your system without copying or disseminating it or placing any
reliance upon its contents.  We cannot accept liability for any breaches of
confidence arising through use of email.  Any opinions expressed in this
email (including attachments) are those of the author and do not necessarily
reflect our opinions.  We will not accept responsibility for any commitments
made by our employees outside the scope of our business.  We do not warrant
the accuracy or completeness of such information.

<Original message>
I receive a text file which I import into an access database, in the text
file each record is dated, however the date reads yyyymmdd (i.e. 20021028)
in integer format.  Is there a way I can change this into a more useable
date in date/time format, such as dd/mm/yyyy?

<Reply>
I have a function to convert a date-like value from a field that is
actually a long integer. It looks long-winded because it is written to be
easily understood and could be cut down a bit. It works!


Public Function DateFromLong(pLDate As Long) As Date
Dim TheYear As String, TheMonth As String, TheDay As String

If pLDate = 0 Then
  DateFromLong = #12/31/1899#
Else
  TheYear = Int(pLDate / 10000)
  TheMonth = Int((pLDate - (TheYear * 10000)) / 100)
  TheDay = pLDate - (TheMonth * 100) - (TheYear * 10000)

  DateFromLong = FormatDateTime(TheDay & "/" & TheMonth & "/" & TheYear,
vbShortDate)
End If

End Function

Hope this helps
Joe Dunn


*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************

Thank you, thats great

-----Original Message-----
From: joe.dunn@c... [mailto:joe.dunn@c...]
Sent: 28 October 2002 12:12
To: Access
Subject: [access] Re: Integer to Date/Time



<Original message>
I receive a text file which I import into an access database, in the text
file each record is dated, however the date reads yyyymmdd (i.e. 20021028)
in integer format.  Is there a way I can change this into a more useable
date in date/time format, such as dd/mm/yyyy?

<Reply>
I have a function to convert a date-like value from a field that is
actually a long integer. It looks long-winded because it is written to be
easily understood and could be cut down a bit. It works!


Public Function DateFromLong(pLDate As Long) As Date
Dim TheYear As String, TheMonth As String, TheDay As String

If pLDate = 0 Then
  DateFromLong = #12/31/1899#
Else
  TheYear = Int(pLDate / 10000)
  TheMonth = Int((pLDate - (TheYear * 10000)) / 100)
  TheDay = pLDate - (TheMonth * 100) - (TheYear * 10000)

  DateFromLong = FormatDateTime(TheDay & "/" & TheMonth & "/" & TheYear,
vbShortDate)
End If

End Function

Hope this helps
Joe Dunn


*************************************************************************

This e-mail may contain confidential information or be privileged. It is
intended to be read and used only by the named recipient(s). If you are not
the intended recipient(s) please notify us immediately so that we can make
arrangements for its return: you should not disclose the contents of this
e-mail to any other person, or take any copies. Unless stated otherwise by
an authorised individual, nothing contained in this e-mail is intended to
create binding legal obligations between us and opinions expressed are those
of the individual author.

The CIS marketing group, which is regulated for Investment Business by the
Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R -
for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  -
for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number
3390839 - for ISAs and investment products bearing the CIS name
Registered offices: Miller Street, Manchester M60 0AL   Telephone
0161-832-8686   Internet  http://www.cis.co.uk   E-mail cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The
Co-operative Bank p.l.c., registered in England and Wales number 990937,
P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS
Policyholder Services Limited as agent of the Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

****************************************************************************
****




NOTICE AND DISCLAIMER:
This email (including attachments) is confidential.  If you have received
this email in error please notify the sender immediately and delete this
email from your system without copying or disseminating it or placing any
reliance upon its contents.  We cannot accept liability for any breaches of
confidence arising through use of email.  Any opinions expressed in this
email (including attachments) are those of the author and do not necessarily
reflect our opinions.  We will not accept responsibility for any commitments
made by our employees outside the scope of our business.  We do not warrant
the accuracy or completeness of such information.

Hi Ben,

Function NumToDate(ByVal num As Long, ByVal fmt As String)
    NumToDate = CDate(num \ 10000 & "/" & num \ 100 Mod 100 & _
        "/" & num Mod 100)
End Function

Call with NumToDate(20021028, "MMDDYYYY")

Best,

Bob

>From: Ben Mildren <Ben.Mildren@t...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Integer to Date/Time
>Date: Mon, 28 Oct 2002 11:51:29 -0000
>
>I receive a text file which I import into an access database, in the text
>file each record is dated, however the date reads yyyymmdd (i.e. 20021028)
>in integer format.  Is there a way I can change this into a more useable
>date in date/time format, such as dd/mm/yyyy?
>
>Many thanks
>Ben Mildren
>
>
>
>NOTICE AND DISCLAIMER:
>This email (including attachments) is confidential.  If you have received
>this email in error please notify the sender immediately and delete this
>email from your system without copying or disseminating it or placing any
>reliance upon its contents.  We cannot accept liability for any breaches of
>confidence arising through use of email.  Any opinions expressed in this
>email (including attachments) are those of the author and do not 
>necessarily
>reflect our opinions.  We will not accept responsibility for any 
>commitments
>made by our employees outside the scope of our business.  We do not warrant
>the accuracy or completeness of such information.
>
>


_________________________________________________________________
Get a speedy connection with MSN Broadband.  Join now! 
http://resourcecenter.msn.com/access/plans/freeactivation.asp