Using a User Function to Store a formula

[Guest]

I understand that in Microsoft Access, functions written in Visual Basic can
do wonderful things. This is not one of the requests. I have a formula, a
mathematical function that needs to be applied to multiple variables in a
specific query. The formula is a variation of the Bureau of Public Roads
Speed to Demand function. It contains about five constants and the formula is
complicated enough that I'd like to be able to treat it as a function where
I can plug in a few parameters and have it spit out the formula result.

B/(1+a*[(C+2*D*F)/(G*H*J*u)]^y

I'm going against all mathematical conventions here, but the values in CAPS
are parameters (numbers that change and are based on values of variables
within the database) and the lower case variables are formula constants. I
have successfully entered the formula in Microsoft Excel, but the data is
already in Access, so I'd like to be able to calculate the result in Access.

Congested Speed = BPR(B,C,D,F,G,H,J)

where a = 0.15
u = 0.75
y = 4

I'd rather have the values of a, u and y assigned in the
function before the formula as these values may change at some time.

I assume this is not difficult, but I have been completely unable
to find a coherent explanation of how functions work in Access 2002. The only
reference seems to be a reference in the Build Formula dialog.
Any help is appreciated.

 

[pietlinden]

Okay, so what's the question?

Using your own functions in a query is no different than using
predefined ones.

You might need to make the functions public, but then in the query you
can do something like

SELECT TableA.Field1, TableA.Field2, MyFunction(TableA.Field1,
TableA.Field2) As MyFunctionResult
FROM TableA
WHERE...

then your function would look like

Public Function MyFunction(arg1 as Single, arg2 as Single) As Single

MyFunction=arg1 + arg2

End Function

Of course, your function could be ridiculously complicated... this is
just a really simple example.

 

[Marshall Barton]

I understand that in Microsoft Access, functions written in Visual Basic can
do wonderful things. This is not one of the requests. I have a formula, a
mathematical function that needs to be applied to multiple variables in a
specific query. The formula is a variation of the Bureau of Public Roads
Speed to Demand function. It contains about five constants and the formula is
complicated enough that I'd like to be able to treat it as a function where
I can plug in a few parameters and have it spit out the formula result.

B/(1+a*[(C+2*D*F)/(G*H*J*u)]^y

I'm going against all mathematical conventions here, but the values in CAPS
are parameters (numbers that change and are based on values of variables
within the database) and the lower case variables are formula constants. I
have successfully entered the formula in Microsoft Excel, but the data is
already in Access, so I'd like to be able to calculate the result in Access.

Congested Speed = BPR(B,C,D,F,G,H,J)

where a = 0.15
u = 0.75
y = 4

I'd rather have the values of a, u and y assigned in the
function before the formula as these values may change at some time.

Your BPR function can include the constants in itself:

Public Function BPR(B,C,D,F,G,H,J)
Const a As Double =0.15
Const u As Double =0.75
Const y As Double =4.0

BPR = B/(1+a*[(C+2*D*F)/(G*H*J*u)]^y
End Function

But that would mean that you will need to redristribute your
application when you change the value of a, u and/or y.

Since a database's natural storage is tables, I suggest that
you use a one record table with three fields to hold the
values of a, u and y. The function could then look like:

Public Function BPR(B,C,D,F,G,H,J)
With CurrentDb.OpenRecordset(onerowtable)
BPR = B/(1+!a*[(C+2*D*F)/(G*H*J*!u)]^!y
End With
End Function

This way, if you need to change the value of a, u or y, you
only need to edit the one row in the table.

 

[Guest]

NO PROBLEMS, CONTACT ME...
--
PACALA JAN [ACCESS DEVELOPER]
SEND 10$ TO...
ACCOUNT:
SK31 1100 0000 0026 1674 0428
SwiftCode: TATRSKBX
Tatra banka as,Hodzovo nam.3, 811 06 Bratislava
(e-mail address removed)
SKYPE: PACALA.BA1

 

[Douglas J. Steele]

These newsgroups are for the FREE exchange of ideas, information, and
assistance. This is absolutely NOT the place to troll for business: such
postings are not welcome, and they make such a bad impression that it can
only hurt your reputation in the Access community.

If you want to post actual advice and assistance here in the newsgroups for
all to benefit from, or post questions of your own, then your participation
will be welcomed.

 

[DAVID]

That does an OpenRecordset every time that
function BPR is referenced. That is, at least
once for each derived field on each line of
the result.

OK for small queries, but gets slow fast.

use static variables for a,u,y, only lookup once.

I understand that in Microsoft Access, functions written in Visual Basic can
do wonderful things. This is not one of the requests. I have a formula, a
mathematical function that needs to be applied to multiple variables in a
specific query. The formula is a variation of the Bureau of Public Roads
Speed to Demand function. It contains about five constants and the formula is
complicated enough that I'd like to be able to treat it as a function where
I can plug in a few parameters and have it spit out the formula result.

B/(1+a*[(C+2*D*F)/(G*H*J*u)]^y

I'm going against all mathematical conventions here, but the values in CAPS
are parameters (numbers that change and are based on values of variables
within the database) and the lower case variables are formula constants. I
have successfully entered the formula in Microsoft Excel, but the data is
already in Access, so I'd like to be able to calculate the result in Access.

Congested Speed = BPR(B,C,D,F,G,H,J)

where a = 0.15
u = 0.75
y = 4

I'd rather have the values of a, u and y assigned in the
function before the formula as these values may change at some time.

Your BPR function can include the constants in itself:

Public Function BPR(B,C,D,F,G,H,J)
Const a As Double =0.15
Const u As Double =0.75
Const y As Double =4.0

BPR = B/(1+a*[(C+2*D*F)/(G*H*J*u)]^y
End Function

But that would mean that you will need to redristribute your
application when you change the value of a, u and/or y.

Since a database's natural storage is tables, I suggest that
you use a one record table with three fields to hold the
values of a, u and y. The function could then look like:

Public Function BPR(B,C,D,F,G,H,J)
With CurrentDb.OpenRecordset(onerowtable)
BPR = B/(1+!a*[(C+2*D*F)/(G*H*J*!u)]^!y
End With
End Function

This way, if you need to change the value of a, u or y, you
only need to edit the one row in the table.

 

[Marshall Barton]

Good point David.

Public Function BPR(B,C,D,F,G,H,J)
Static a As Double, u As Double, y As Double

If IsEmpty(a) Then
With CurrentDb.OpenRecordset(onerowtable)
a = !a : u = !u : y = !y
End With
End If

BPR = B/(1+a*[(C+2*D*F)/(G*H*J*u)]^y
End Function
--
Marsh
MVP [MS Access]

That does an OpenRecordset every time that
function BPR is referenced. That is, at least
once for each derived field on each line of
the result.

OK for small queries, but gets slow fast.

use static variables for a,u,y, only lookup once.

I understand that in Microsoft Access, functions written in Visual Basic can
do wonderful things. This is not one of the requests. I have a formula, a
mathematical function that needs to be applied to multiple variables in a
specific query. The formula is a variation of the Bureau of Public Roads
Speed to Demand function. It contains about five constants and the formula is
complicated enough that I'd like to be able to treat it as a function where
I can plug in a few parameters and have it spit out the formula result.

B/(1+a*[(C+2*D*F)/(G*H*J*u)]^y

I'm going against all mathematical conventions here, but the values in CAPS
are parameters (numbers that change and are based on values of variables
within the database) and the lower case variables are formula constants. I
have successfully entered the formula in Microsoft Excel, but the data is
already in Access, so I'd like to be able to calculate the result in Access.

Congested Speed = BPR(B,C,D,F,G,H,J)

where a = 0.15
u = 0.75
y = 4

I'd rather have the values of a, u and y assigned in the
function before the formula as these values may change at some time.

Your BPR function can include the constants in itself:

Public Function BPR(B,C,D,F,G,H,J)
Const a As Double =0.15
Const u As Double =0.75
Const y As Double =4.0

BPR = B/(1+a*[(C+2*D*F)/(G*H*J*u)]^y
End Function

But that would mean that you will need to redristribute your
application when you change the value of a, u and/or y.

Since a database's natural storage is tables, I suggest that
you use a one record table with three fields to hold the
values of a, u and y. The function could then look like:

Public Function BPR(B,C,D,F,G,H,J)
With CurrentDb.OpenRecordset(onerowtable)
BPR = B/(1+!a*[(C+2*D*F)/(G*H*J*!u)]^!y
End With
End Function

This way, if you need to change the value of a, u or y, you
only need to edit the one row in the table.