Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Slow A97 module running in A2002

Discussion in 'Information Technology' started by Adam@nospam.com, Nov 15, 2005.

  1. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Hi All,

    I have the below module, which converts a database field from an
    integer into a date format (UK Date .i.e dd/mm/yyyy).

    Please see below:

    Function ConvertDate(InputValue As Variant) As Variant
    Dim lngDay As Long
    Dim lngMonth As Long
    Dim lngYear As Long

    If IsNull(InputValue) = False Then
    lngYear = InputValue \ 65536
    lngMonth = (InputValue Mod 65536) \ 256
    lngDay = InputValue - (lngYear * 65536) - (lngMonth * 256)
    ConvertDate = DateSerial(lngYear, lngMonth, lngDay)
    Else
    ConvertDate = Null
    End If

    End Function


    This ran quite quickly in Access 97 however now that I've exported it
    into my Access XP database it is running a lot slower!!!

    Can anyone see why it would be so slow now? Is there any improvements
    that could be made to the module code??

    Many Thanks for your help


    Adam
     
  2. Loading...

    Similar Threads Forum Date
    Opinion IDEAS: Revolution, Evolution Or Slow Death Sikh Sikhi Sikhism Jul 16, 2016
    Slow metabolism 'obesity excuse' true Health & Nutrition Oct 26, 2013
    India India in the Slow Lane Breaking News Feb 10, 2013
    USA Slow Recovery at US Sikh Temple Breaking News Jan 14, 2013
    UK Archbishop of Canterbury Visits Hounslow Sikh Temple Breaking News Oct 19, 2011

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Realistically, you don't need such a function. Dates are stored as numbers:
    there's no need to do any conversion to use the number as a date.

    Look at the following code I just ran in the Debug window (Ctrl-G):

    ?Format(Date(), "#")
    38670
    ?Format(38670, "dd/mm/yyyy")
    14/11/2005



    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Adam@nospam.com" <adamevans81@gmail.com> wrote in message
    news:1131986712.977432.323600@g14g2000cwa.googlegroups.com...
    > Hi All,
    >
    > I have the below module, which converts a database field from an
    > integer into a date format (UK Date .i.e dd/mm/yyyy).
    >
    > Please see below:
    >
    > Function ConvertDate(InputValue As Variant) As Variant
    > Dim lngDay As Long
    > Dim lngMonth As Long
    > Dim lngYear As Long
    >
    > If IsNull(InputValue) = False Then
    > lngYear = InputValue \ 65536
    > lngMonth = (InputValue Mod 65536) \ 256
    > lngDay = InputValue - (lngYear * 65536) - (lngMonth * 256)
    > ConvertDate = DateSerial(lngYear, lngMonth, lngDay)
    > Else
    > ConvertDate = Null
    > End If
    >
    > End Function
    >
    >
    > This ran quite quickly in Access 97 however now that I've exported it
    > into my Access XP database it is running a lot slower!!!
    >
    > Can anyone see why it would be so slow now? Is there any improvements
    > that could be made to the module code??
    >
    > Many Thanks for your help
    >
    >
    > Adam
    >
     
  4. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Will this work?? Seems so simple?

    You see all of the calculations that my module is running?
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Sorry, you're right. Your encoding scheme is different than what Access uses
    to store dates.

    Can you change so that you do use the same scheme? (In Access, dates are
    stored as the number of days relative to 30 Dec, 1899, and times are stored
    as a fraction of a day)

    I'm not sure there's any way to speed up your existing code.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Adam@nospam.com" <adamevans81@gmail.com> wrote in message
    news:1131990409.254866.90510@g43g2000cwa.googlegroups.com...
    > Will this work?? Seems so simple?
    >
    > You see all of the calculations that my module is running?
    >
     
  6. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    I'm using Access to pull data off of the SQL server. There is a call
    logging program called RoyalBlue Touchpaper which records dates in this
    format.
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Ouch. Not sure there's anything that can be done, then: nothing about your
    code looks like it should be causing delays.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Adam@nospam.com" <adamevans81@gmail.com> wrote in message
    news:1131997331.125746.84660@z14g2000cwz.googlegroups.com...
    > I'm using Access to pull data off of the SQL server. There is a call
    > logging program called RoyalBlue Touchpaper which records dates in this
    > format.
    >
     
  8. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    I've just been reading about these.

    Are they good? How do you use them?

    Wouldn't I just have the same problem? I still need to convert the
    Touchpaper date into something recognisable..

    I found this on the web, it explains that the date returned from
    Touchpaper is a HEX format? Apparently it means something to
    developers:
    http://www.vbcity.com/forums/faq.asp?fid=44&cat=Date/Time&

    Is this something I could do with Access Projects?
     
  9. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Ok, there's an example on this website:
    http://www.vbcity.com/forums/faq.asp?fid=44&cat=Date/Time&

    The hex sample: 131336971

    This represents the date 11/11/2004 (dd/mm/yyyy)


    Where are the queries and tables stored in an ADP ? I want to create
    forms which show the queries etc. Are these all stored in the ADP
    database? Is this still possible?
     
  10. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    So it stores them locally, in the ADP? Or on the SQL server?

    Also if I only have read-only access, does this effect anything?
     

Share This Page