Remember Me?

#1
December 14th 04, 04:19 PM
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

#2
December 15th 04, 03:38 AM
 Debra Dalgleish Posts: n/a

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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Karen Middleton Excel Discussion (Misc queries) 1 December 13th 04 07:54 PM Hari Excel Discussion (Misc queries) 0 December 3rd 04 06:03 AM Hari Excel Discussion (Misc queries) 0 December 3rd 04 05:47 AM Hari Excel Discussion (Misc queries) 0 December 3rd 04 05:45 AM Hari Excel Discussion (Misc queries) 1 December 3rd 04 02:32 AM

All times are GMT +1. The time now is 05:12 PM.