Converting an Excel formula to an Access query formula

G

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

Debra Dalgleish

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!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Converting an Excel Formula to work in an Access query 4
Converting another Excel function to work with an Access Query 4
Converting an Integer into a Date 3
Access Module coded converted to Excel Function 1
how to tighten up unwieldy formula 3
Formula to add 12 months to an existing date field 3
Slow A97 module running in A2002 12
No Results returned from Access query 3

Top