Slow A97 module running in A2002

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

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!)


" wrote in message
oups.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

Will this work?? Seems so simple?

You see all of the calculations that my module is running?

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!)


" wrote in message
ups.com...
Will this work?? Seems so simple?

You see all of the calculations that my module is running?

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.

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!)


" wrote in message
ups.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.

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...t=Date%2FTime&

Is this something I could do with Access Projects?

Ok, there's an example on this website:
http://www.vbcity.com/forums/faq.asp...t=Date%2FTime&

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?

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?