Access Recordset with Built-In Function

[Jeff Huff]

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.

 

[Michael Hopwood]

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?

 

[Jeff Huff]

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.

 

[Michael Hopwood]

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.