#### Converting an Excel formula to an Access query formula

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

--
-----------
Windows 98 + Office Pro 97
``` 0 12/14/2004 4:19:04 PM excel.misc  78881 articles. 5 followers. 1 Replies 684 Views Similar Articles

[PageSpeed] 2

```The formula should work in Access if you replace TRUNC with INT, and IF
with IIF.

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

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

``` 0 12/15/2004 3:38:10 AM Similar Artilces: