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.

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.

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.


.

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.


.