Converting an Excel Formula to work in an Access query

[Guest]

Hi All,

In our office we are using Touchpaper Helpdesk (effectively its a database
for Call logging in a Telephony centre).

In the data tables it records dates as a large Integer .i.e the date
24/05/2004 appears in the database tables as 131335448.

I am using Microsoft Access 97 to connect to the tables through ODBC and it
works fine however I want to search on data between dates however I have only
calculated a formula in Excel.

Below is the formula in Excel that converts the date integer into a usuable
date format of "dd/mm/yyyy" , note that the field [DB DATE ENCODED FIELD] is
the date interger .i.e as above 131335448.

=[DB DATE ENCODED FIELD]-(TRUNC([DB DATE ENCODED
FIELD]/65536)*65536)-((TRUNC((([DB DATE ENCODED FIELD])-((TRUNC([DB DATE
ENCODED FIELD]/65536))*65536))/256))*256)&"/"&IF(LEN(TRUNC((([DB DATE ENCODED
FIELD])-((TRUNC([DB DATE ENCODED FIELD]/65536))*65536))/256))<2,0&TRUNC((([DB
DATE ENCODED FIELD])-((TRUNC([DB DATE ENCODED
FIELD]/65536))*65536))/256),TRUNC((([DB DATE ENCODED FIELD])-((TRUNC([DB DATE
ENCODED FIELD]/65536))*65536))/256))&"/"&TRUNC([DB DATE ENCODED FIELD]/65536)

Has anyone got the skill level to convert this from an excel working
funcation to a Function that will work in a Access Query? So I can
effectively search on dates.

Would be so chuffed is someone knew this!

 

[Douglas J. Steele]

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

 

[Andi Mayer]

On Tue, 14 Dec 2004 08:17:12 -0800, "Adam"

I don't looked at this strange formular, but Have you ever tried:

cdbl(#24/05/2004#)

it calculates to 38131, this are the days after 31/12/1899


If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[Guest]

Hi Doug, what do I do with this code and how do I use it?

Adam

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




--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi All,

In our office we are using Touchpaper Helpdesk (effectively its a database
for Call logging in a Telephony centre).

In the data tables it records dates as a large Integer .i.e the date
24/05/2004 appears in the database tables as 131335448.

I am using Microsoft Access 97 to connect to the tables through ODBC and it
works fine however I want to search on data between dates however I have only
calculated a formula in Excel.

Below is the formula in Excel that converts the date integer into a usuable
date format of "dd/mm/yyyy" , note that the field [DB DATE ENCODED FIELD] is
the date interger .i.e as above 131335448.

=[DB DATE ENCODED FIELD]-(TRUNC([DB DATE ENCODED
FIELD]/65536)*65536)-((TRUNC((([DB DATE ENCODED FIELD])-((TRUNC([DB DATE
ENCODED FIELD]/65536))*65536))/256))*256)&"/"&IF(LEN(TRUNC((([DB DATE ENCODED
FIELD])-((TRUNC([DB DATE ENCODED FIELD]/65536))*65536))/256))<2,0&TRUNC((([DB
DATE ENCODED FIELD])-((TRUNC([DB DATE ENCODED
FIELD]/65536))*65536))/256),TRUNC((([DB DATE ENCODED FIELD])-((TRUNC([DB DATE
ENCODED FIELD]/65536))*65536))/256))&"/"&TRUNC([DB DATE ENCODED FIELD]/65536)

Has anyone got the skill level to convert this from an excel working
funcation to a Function that will work in a Access Query? So I can
effectively search on dates.

Would be so chuffed is someone knew this!

 

[Douglas J. Steele]

Sorry: I assumed that since you were asking for a function, you knew what to
do with them...

Open a new module and copy that code into it. Do NOT name the module
ConvertDate.

You can use this function in a query as ConvertDate([DB DATE ENCODED FIELD])


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi Doug, what do I do with this code and how do I use it?

Adam

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




--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi All,

In our office we are using Touchpaper Helpdesk (effectively its a database
for Call logging in a Telephony centre).

In the data tables it records dates as a large Integer .i.e the date
24/05/2004 appears in the database tables as 131335448.

I am using Microsoft Access 97 to connect to the tables through ODBC

and
it

works fine however I want to search on data between dates however I

have
only

calculated a formula in Excel.

Below is the formula in Excel that converts the date integer into a usuable
date format of "dd/mm/yyyy" , note that the field [DB DATE ENCODED

FIELD]
is

the date interger .i.e as above 131335448.

=[DB DATE ENCODED FIELD]-(TRUNC([DB DATE ENCODED
FIELD]/65536)*65536)-((TRUNC((([DB DATE ENCODED FIELD])-((TRUNC([DB DATE
ENCODED FIELD]/65536))*65536))/256))*256)&"/"&IF(LEN(TRUNC((([DB DATE ENCODED
FIELD])-((TRUNC([DB DATE ENCODED FIELD]/65536))*65536))/256))<2,0&TRUNC((([DB
DATE ENCODED FIELD])-((TRUNC([DB DATE ENCODED
FIELD]/65536))*65536))/256),TRUNC((([DB DATE ENCODED

FIELD])-((TRUNC([DB
DATE

ENCODED FIELD]/65536))*65536))/256))&"/"&TRUNC([DB DATE ENCODED FIELD]/65536)

Has anyone got the skill level to convert this from an excel working
funcation to a Function that will work in a Access Query? So I can
effectively search on dates.

Would be so chuffed is someone knew this!