Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Recordset with Built-In Function
I have Query1 in Access, in which one of the fields is a
built-in function, which passes the value of two other fields to a Function on Module1. When I attempt to run the following in Excel VBA, the code bombs: Sub attemptquery() Dim db As DAO.Database, rs As DAO.Recordset Dim rng As Excel.Range, i As Byte Set db = OpenDatabase("C:\a_Sales Tech\Presentations\AAF Reports\db1.mdb") Set rs = db.OpenRecordset("Query1", dbOpenDynaset) <-- BOMBS HERE, CUZ "Query1" HAS A BUILT IN FUNCTION, RELATING TO MODULE1 IN ACCESS!! Set rng = Range("A1") For i = 0 To rs.Fields.Count - 1 rng.Offset(0, i).Range("A1").Value = rs.Fields(i) Next i rs.Close Set rs = Nothing db.Close Set db = Nothing Set rng = Nothing End Sub Thank You. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Recordset with Built-In Function
Yep, you can't export custom functions directly, you can make your query a
"MAKE-TABLE" query, run that then import the table though. What is the code for the UDF? -- Michael Hopwood (Phobos) "Jeff Huff" wrote in message ... I have Query1 in Access, in which one of the fields is a built-in function, which passes the value of two other fields to a Function on Module1. When I attempt to run the following in Excel VBA, the code bombs: Sub attemptquery() Dim db As DAO.Database, rs As DAO.Recordset Dim rng As Excel.Range, i As Byte Set db = OpenDatabase("C:\a_Sales Tech\Presentations\AAF Reports\db1.mdb") Set rs = db.OpenRecordset("Query1", dbOpenDynaset) <-- BOMBS HERE, CUZ "Query1" HAS A BUILT IN FUNCTION, RELATING TO MODULE1 IN ACCESS!! Set rng = Range("A1") For i = 0 To rs.Fields.Count - 1 rng.Offset(0, i).Range("A1").Value = rs.Fields(i) Next i rs.Close Set rs = Nothing db.Close Set db = Nothing Set rng = Nothing End Sub Thank You. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Recordset with Built-In Function
Here's the UDF, it just calculates "last years sales"
based on "this years sales" and "sales pct chg" Public Function salesyag(sales As Double, pctchg As Double) As Double If pctchg < -100 Then salesyag = sales / (pctchg / 100 + 1) Else salesyag = 0 End If End Function I'll try the make table function. Thank you for your help. -----Original Message----- Yep, you can't export custom functions directly, you can make your query a "MAKE-TABLE" query, run that then import the table though. What is the code for the UDF? -- Michael Hopwood (Phobos) "Jeff Huff" wrote in message ... I have Query1 in Access, in which one of the fields is a built-in function, which passes the value of two other fields to a Function on Module1. When I attempt to run the following in Excel VBA, the code bombs: Sub attemptquery() Dim db As DAO.Database, rs As DAO.Recordset Dim rng As Excel.Range, i As Byte Set db = OpenDatabase("C:\a_Sales Tech\Presentations\AAF Reports\db1.mdb") Set rs = db.OpenRecordset("Query1", dbOpenDynaset) <-- BOMBS HERE, CUZ "Query1" HAS A BUILT IN FUNCTION, RELATING TO MODULE1 IN ACCESS!! Set rng = Range("A1") For i = 0 To rs.Fields.Count - 1 rng.Offset(0, i).Range("A1").Value = rs.Fields(i) Next i rs.Close Set rs = Nothing db.Close Set db = Nothing Set rng = Nothing End Sub Thank You. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Recordset with Built-In Function
Instead of using a UDF, change the field from:
salesyag([sales], [pctchg]) To: iif([pctchg]<-100,[sales]/([pctchg]/100+1),0) And get rid of the UDF. -- Michael Hopwood (Phobos) "Jeff Huff" wrote in message ... Here's the UDF, it just calculates "last years sales" based on "this years sales" and "sales pct chg" Public Function salesyag(sales As Double, pctchg As Double) As Double If pctchg < -100 Then salesyag = sales / (pctchg / 100 + 1) Else salesyag = 0 End If End Function I'll try the make table function. Thank you for your help. -----Original Message----- Yep, you can't export custom functions directly, you can make your query a "MAKE-TABLE" query, run that then import the table though. What is the code for the UDF? -- Michael Hopwood (Phobos) "Jeff Huff" wrote in message ... I have Query1 in Access, in which one of the fields is a built-in function, which passes the value of two other fields to a Function on Module1. When I attempt to run the following in Excel VBA, the code bombs: Sub attemptquery() Dim db As DAO.Database, rs As DAO.Recordset Dim rng As Excel.Range, i As Byte Set db = OpenDatabase("C:\a_Sales Tech\Presentations\AAF Reports\db1.mdb") Set rs = db.OpenRecordset("Query1", dbOpenDynaset) <-- BOMBS HERE, CUZ "Query1" HAS A BUILT IN FUNCTION, RELATING TO MODULE1 IN ACCESS!! Set rng = Range("A1") For i = 0 To rs.Fields.Count - 1 rng.Offset(0, i).Range("A1").Value = rs.Fields(i) Next i rs.Close Set rs = Nothing db.Close Set db = Nothing Set rng = Nothing End Sub Thank You. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the oldest date in an Access recordset | Excel Discussion (Misc queries) | |||
is there any built in function to draw lines | Excel Worksheet Functions | |||
Sqr built-in function in Excel 2003 VBA | Excel Discussion (Misc queries) | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming | |||
Access a built-In command? | Excel Programming |