Results 1 to 3 of 3

Thread: Conversion

  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conversion

    Trying to import a FoxPro 2.5 .dbf type of database into MS Access. Date field is defined as D8 to represent date formatted as mm/dd/yy. Two other fields defined as N5 store a begin and end time. Upon completion of the import, Access represents the date as 1900 for 2000. I was able to do some math and convert these to the proper date. However, trying to convert the time fields has been the big headache. Converting the number type to date/time type changes all the numbers to 0:00. What I would like to happen is to have a number, eg. 1013, converted to 10:13 without writing a ton of VB code. If anyone has a suggestion, I will listen. If not, A-coding I will go. TIA.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conversion

    Hi Bill,
    This is actually surprisingly (to me anyway[img]/w3timages/icons/grin.gif[/img]) simple:
    add a new Date/Time field to your table formatted as Short Time. Then run an Update query that sets this new field to:
    Left([Field1],Len([Field1])-2)&":"&Right([Field1],2)
    where Field1 is your existing field.
    Hope that helps.
    PS I assumed that not all your numbers would have 4 digits hence the Len() bit.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conversion

    Rory: That did the trick for most of the records. I can manually fix those. Thanks again and pardon my brain cramp.
    [img]/w3timages/icons/nuts.gif[/img]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •