Calling MVP's & pro's urgently please !!
 

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

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

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

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

I suspect that the problem is with MSQuery in that it cannot understand your
UDF. Try creating a query of the data in Access and see if you can read the
query of the table. If not, it looks like you'll have to remove the date
conversion module and do the date conversion within Excel after you get the
data.

"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

You can create fields in an Access query to convert the number to a
date. For example, create a new blank query, and switch to SQL view.
Paste in the following SQL statement, substituting your table name for
aaMyTest, and your field name for TestDate:

'=======================
SELECT aaMyTest.TestDate, [TestDate]\65536 AS lngYear,
([TestDate] Mod 65536)\256 AS lngMonth,
[TestDate]-([lngYear]*65536)-([lngMonth]*256) AS lngDay,
IIf([TestDate] Is Null,Null,DateSerial([lngYear],[lngMonth],[lngDay]))
AS CalcDate
FROM aaMyTest;
'==========================

Switch to Design view, and you'll see the fields that were created.
In Excel, you'll be able to import the query results, using MS Query.

Adam wrote:
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




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Gary Rowe wrote:

I suspect that the problem is with MSQuery in that it cannot
understand your UDF.

The problem is that MS Access is not being used. The UDF is simply not
available outside of the MS Access UI. The only non-SQL functions
available are the VBA5 functions (as distinct from methods).