Hi

A date in Access is also a valid date in Excel. The ConvertDate output is a
standard date. What does the passed data InputValue look like ? I find it
strange that it deals with 65536 and 256, those are multiples of 2.

Best wishes Harald

"Adam" skrev i melding
...
Hi, please see below a copy of the module:

Function ConvertDate(InputValue As Variant) As Variant
Dim lngDay As Long
Dim lngMonth As Long
Dim lngYear As Long

If IsNull(InputValue) = False Then
lngYear = InputValue \ 65536
lngMonth = (InputValue Mod 65536) \ 256
lngDay = InputValue - (lngYear * 65536) - (lngMonth * 256)
ConvertDate = DateSerial(lngYear, lngMonth, lngDay)
Else
ConvertDate = Null
End If

End Function


I've very unknowledgable with Access, if you know of a way of converting
this to work itself out in a query so I could use with Excel then that
would
be a way around this.

Many Thanks


"Nick Hodge" wrote:

Adam

What is convertDate doing? If it is re-assembling a date in Access from
say
US to UK format or simply adding slashes, you would do better to steer
away
from UDFs all together.

You could build your date query without using a UDF if that's what it's
doing.

Access UDFs and parameter queries do not operate well with the ODBC
drivers

HTH
Nick Hodge

"Adam" wrote:

Dear All,

I've had an unanswered post below and I really need some help on this
asap.
I have a project I need to complete cob today!
--------------

Hi All,

I've created an Access 97 database which performs some nice functions
that
have been created using Modules.

When I'm in Excel and create an Pivot table using an external source
(then
connect to the QUERY in my database) it gives me an error message
saying
"Undefined function 'ConvertDate' in expression".

'ConvertDate' is the function that my module creates in my Access
Database.

Does anyone know if its possible to connect to my database?? I still
need
the function to be working in Access, is there anyway I can define it
in
excel so its happy and pulls it in?
--
Adam
-----------
Windows 98 + Office Pro 97