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!


-- 
Adam
-----------
Windows 98 + Office Pro 97
0
adam1685 (340)
12/14/2004 4:19:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
684 Views

Similar Articles

[PageSpeed] 2

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

Adam wrote:
> 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
dsd1 (5911)
12/15/2004 3:38:10 AM
Reply:

Similar Artilces: