Using Public Variables in my Query

[Joy]

Hi -

There are 2 public variables in my program.
Public curKilometerRate As Currency
Public curHSTRate As Currency

I have a subform called frmInstructorExpenseDetailsExtendedSUBF
based on the query called qryExpenseDetailsExtended

I need to use the public variables in several columns of my query.
for example KMExpense: KMValue*curKilometerRate
where KMValue was entered by the user and curKilometerRate is a public
variable.

(I cannot use a parameter box, the value of curKilometerRate is read in from
outside the program into a public variable.)

Any 'step by step' instructions on how to do this will be greatly
appreciated. I have already spent a lot of time reading the newsgroups and
implementing various suggestions - but I haven't been successful.

Thank you for your time. I appreciate your helping me.

Joy

 

[Ken Snell [MVP]]

Create a public function that returns the value of the public variable. Use
the public function in your query.

 

[Duane Hookom]

Create public functions that returns each variable:
Public Function GetKilRate() as Currency
GetKilRate = curKilometerRate
End Function
You can then use GetKilRate() where ever you need the value.

 

[Marshall Barton]

There are 2 public variables in my program.
Public curKilometerRate As Currency
Public curHSTRate As Currency

I have a subform called frmInstructorExpenseDetailsExtendedSUBF
based on the query called qryExpenseDetailsExtended

I need to use the public variables in several columns of my query.
for example KMExpense: KMValue*curKilometerRate
where KMValue was entered by the user and curKilometerRate is a public
variable.

(I cannot use a parameter box, the value of curKilometerRate is read in from
outside the program into a public variable.)

Any 'step by step' instructions on how to do this will be greatly
appreciated. I have already spent a lot of time reading the newsgroups and
implementing various suggestions - but I haven't been successful.

Variables exist only in the VBA environment. The only VBA
items that are known to the Access and SQL name spaces are
public functions.

Add a function for each variable to the module containing
the variables:

Public curKilometerRate As Currency
Public curHSTRate As Currency

Public Function GetKilometerRate()
GetKilometerRate = curKilometerRate
End Function

Public Function GetHSTRate()
GetHSTRate = curHSTRate
End Function

Then your query can use expressions like:
KMExpense: KMValue * GetKilometerRate()

 

[Joy]

Thank you for your replies. I did what Marshall said, and it worked very
well.
Thanks so much!

Joy