Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|