Access Recordset with Built-In Function

  • Thread starter Thread starter Jeff Huff
  • Start date Start date
J

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.
 
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?
 
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.
 
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.
 
Back
Top