Converting another Excel function to work with an Access Query

G

Guest

Hi All,

Mr Doug Steele answered a similar post to this previously, hopeing for
another type of answer as his answer worked a charm , see previous post
(http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us)

Anyway, I'm using Touchpaper Helpdesk (a database for Call logging in a
telephony centre)

In the data tables it records times as a large Integer .i.e the time
09:20:20 AM (hhmmss) reads as 152310784.

I'm using Access 97 to connect to the tables through ODBC and it works fine
however I want to search on times as well.

Below is the formula in Excel that converts the time integer into a usuable
time format of "hh:mm", note that the field [DB TIME ENCODED FIELD] is the
time interger .i.e as above 152310784.

=FLOOR([DB DATE ENCODED FIELD]/16777216,1)&":"&FLOOR((([DB DATE ENCODED
FIELD]-((FLOOR([DB DATE ENCODED FIELD]/16777216,1))*16777216))/65536),2)

I've posted something similar before on times but haven't had a resolution.
Has anyone got the skills/expertise to convert my excel formula into a
Module Code that reads as a time format?

Many Thanks !!
 
D

Douglas J. Steele

Not that I've figured out where those odd values are coming from, but the
following function should return the time for you:

Function TimeStuff(InputValue As Variant) As Variant
Dim lngHour As Long
Dim lngMinute As Long
Dim lngSecond As Long

If IsNull(InputValue) Then
TimeStuff = Null
Else
lngHour = InputValue \ 16777216
lngMinute = (InputValue Mod 16777216) \ 65536
lngSecond = (InputValue - (lngHour * 16777216) - (lngMinute *
65536)) \ 256
TimeStuff = TimeSerial(lngHour, lngMinute, lngSecond)
End If

End Function
 
G

Guest

Hi Doug,

This returns a debug error when running it.

Any ideas?

Adam

Douglas J. Steele said:
Not that I've figured out where those odd values are coming from, but the
following function should return the time for you:

Function TimeStuff(InputValue As Variant) As Variant
Dim lngHour As Long
Dim lngMinute As Long
Dim lngSecond As Long

If IsNull(InputValue) Then
TimeStuff = Null
Else
lngHour = InputValue \ 16777216
lngMinute = (InputValue Mod 16777216) \ 65536
lngSecond = (InputValue - (lngHour * 16777216) - (lngMinute *
65536)) \ 256
TimeStuff = TimeSerial(lngHour, lngMinute, lngSecond)
End If

End Function

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Adam said:
Hi All,

Mr Doug Steele answered a similar post to this previously, hopeing for
another type of answer as his answer worked a charm , see previous post
(http://www.microsoft.com/office/community/en-us/default.mspx?query=Adam&dg=
microsoft.public.access&cat=en-us-office-access&lang=en&cr=US&pt=327f343d-f0
d6-4e07-aa53-9656edb98ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us)

Anyway, I'm using Touchpaper Helpdesk (a database for Call logging in a
telephony centre)

In the data tables it records times as a large Integer .i.e the time
09:20:20 AM (hhmmss) reads as 152310784.

I'm using Access 97 to connect to the tables through ODBC and it works fine
however I want to search on times as well.

Below is the formula in Excel that converts the time integer into a usuable
time format of "hh:mm", note that the field [DB TIME ENCODED FIELD] is the
time interger .i.e as above 152310784.

=FLOOR([DB DATE ENCODED FIELD]/16777216,1)&":"&FLOOR((([DB DATE ENCODED
FIELD]-((FLOOR([DB DATE ENCODED FIELD]/16777216,1))*16777216))/65536),2)

I've posted something similar before on times but haven't had a resolution.
Has anyone got the skills/expertise to convert my excel formula into a
Module Code that reads as a time format?

Many Thanks !!
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Works fine on my machine. Where is the error occurring, and what error
message are you getting?

You do realize, I hope, that there was some wordwrap in the lngSecond
equation. It should be one line, ending in \ 256.

Just to avoid no word-wrap problems, here it is rewritten:

Function TimeStuff(InputValue As Variant) As Variant
Dim lngHour As Long
Dim lngMinute As Long
Dim lngSecond As Long

If IsNull(InputValue) Then
TimeStuff = Null
Else
lngHour = InputValue \ 16777216
lngMinute = (InputValue Mod 16777216) \ 65536
lngSecond = (InputValue - (lngHour * 16777216) _
- (lngMinute * 65536)) \ 256
TimeStuff = TimeSerial(lngHour, lngMinute, lngSecond)
End If

End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Adam said:
Hi Doug,

This returns a debug error when running it.

Any ideas?

Adam

Douglas J. Steele said:
Not that I've figured out where those odd values are coming from, but the
following function should return the time for you:

Function TimeStuff(InputValue As Variant) As Variant
Dim lngHour As Long
Dim lngMinute As Long
Dim lngSecond As Long

If IsNull(InputValue) Then
TimeStuff = Null
Else
lngHour = InputValue \ 16777216
lngMinute = (InputValue Mod 16777216) \ 65536
lngSecond = (InputValue - (lngHour * 16777216) - (lngMinute *
65536)) \ 256
TimeStuff = TimeSerial(lngHour, lngMinute, lngSecond)
End If

End Function

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Adam said:
Hi All,

Mr Doug Steele answered a similar post to this previously, hopeing for
another type of answer as his answer worked a charm , see previous post
Click to expand...
(http://www.microsoft.com/office/community/en-us/default.mspx?query=Adam&dg=
microsoft.public.access&cat=en-us-office-access&lang=en&cr=US&pt=327f343d-f0
d6-4e07-aa53-9656edb98ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us)
Anyway, I'm using Touchpaper Helpdesk (a database for Call logging in a
telephony centre)

In the data tables it records times as a large Integer .i.e the time
09:20:20 AM (hhmmss) reads as 152310784.

I'm using Access 97 to connect to the tables through ODBC and it works fine
however I want to search on times as well.

Below is the formula in Excel that converts the time integer into a usuable
time format of "hh:mm", note that the field [DB TIME ENCODED FIELD] is the
time interger .i.e as above 152310784.

=FLOOR([DB DATE ENCODED FIELD]/16777216,1)&":"&FLOOR((([DB DATE ENCODED
FIELD]-((FLOOR([DB DATE ENCODED FIELD]/16777216,1))*16777216))/65536),2)

I've posted something similar before on times but haven't had a resolution.
Has anyone got the skills/expertise to convert my excel formula into a
Module Code that reads as a time format?

Many Thanks !!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It was the word wrap thing, it works fine now, thank you.

Told you I was a total newb at Modules! I honestly didn't realise that sort
of thing effects it.

Douglas J. Steele said:
Works fine on my machine. Where is the error occurring, and what error
message are you getting?

You do realize, I hope, that there was some wordwrap in the lngSecond
equation. It should be one line, ending in \ 256.

Just to avoid no word-wrap problems, here it is rewritten:

Function TimeStuff(InputValue As Variant) As Variant
Dim lngHour As Long
Dim lngMinute As Long
Dim lngSecond As Long

If IsNull(InputValue) Then
TimeStuff = Null
Else
lngHour = InputValue \ 16777216
lngMinute = (InputValue Mod 16777216) \ 65536
lngSecond = (InputValue - (lngHour * 16777216) _
- (lngMinute * 65536)) \ 256
TimeStuff = TimeSerial(lngHour, lngMinute, lngSecond)
End If

End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Adam said:
Hi Doug,

This returns a debug error when running it.

Any ideas?

Adam

Douglas J. Steele said:
Not that I've figured out where those odd values are coming from, but the
following function should return the time for you:

Function TimeStuff(InputValue As Variant) As Variant
Dim lngHour As Long
Dim lngMinute As Long
Dim lngSecond As Long

If IsNull(InputValue) Then
TimeStuff = Null
Else
lngHour = InputValue \ 16777216
lngMinute = (InputValue Mod 16777216) \ 65536
lngSecond = (InputValue - (lngHour * 16777216) - (lngMinute *
65536)) \ 256
TimeStuff = TimeSerial(lngHour, lngMinute, lngSecond)
End If

End Function

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi All,

Mr Doug Steele answered a similar post to this previously, hopeing for
another type of answer as his answer worked a charm , see previous post

(http://www.microsoft.com/office/community/en-us/default.mspx?query=Adam&dg=
microsoft.public.access&cat=en-us-office-access&lang=en&cr=US&pt=327f343d-f0
d6-4e07-aa53-9656edb98ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us)

Anyway, I'm using Touchpaper Helpdesk (a database for Call logging in a
telephony centre)

In the data tables it records times as a large Integer .i.e the time
09:20:20 AM (hhmmss) reads as 152310784.

I'm using Access 97 to connect to the tables through ODBC and it works
fine
however I want to search on times as well.

Below is the formula in Excel that converts the time integer into a
usuable
time format of "hh:mm", note that the field [DB TIME ENCODED FIELD] is the
time interger .i.e as above 152310784.

=FLOOR([DB DATE ENCODED FIELD]/16777216,1)&":"&FLOOR((([DB DATE ENCODED
FIELD]-((FLOOR([DB DATE ENCODED FIELD]/16777216,1))*16777216))/65536),2)

I've posted something similar before on times but haven't had a
resolution.
Has anyone got the skills/expertise to convert my excel formula into a
Module Code that reads as a time format?

Many Thanks !!
Click to expand...
Click to expand...
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 an Excel formula to an Access query formula 1
QBF On multiple Tables 1
Export to a flat file 5
another conditional format question 4
Code not importing with forms 6
criteria 'between' explicit value 1 and Value 2 only. 2
Lookup fields - Yes or No? 12

Top