Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 14th 04, 04:19 PM
Adam
 
Posts: n/a
Default 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

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

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use a Access Query that as a parameter into Excel database query Karen Middleton Excel Discussion (Misc queries) 1 December 13th 04 07:54 PM
Excel aficionado wants to learn Access Hari Excel Discussion (Misc queries) 0 December 3rd 04 06:03 AM
Excel aficionado wants to learn Access Hari Excel Discussion (Misc queries) 0 December 3rd 04 05:47 AM
Excel aficionado wants to learn Access Hari Excel Discussion (Misc queries) 0 December 3rd 04 05:45 AM
Excel user desires to learn ABC of Access Hari Excel Discussion (Misc queries) 1 December 3rd 04 02:32 AM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017