How do i make a constant for query use?

[Guest]

Ok this is driving me nutz, all I am trying to do is create a Constant which
i can easy change in access and be able to use it in Querys to calculate
totals

can someone plse tell me how to make a Constant for my database and the
"expression" i use to look it up

seeing as microsoft cant put this simple information anywhere in there help
or website

 

[Rick Brandt]

Ok this is driving me nutz, all I am trying to do is create a
Constant which i can easy change in access and be able to use it in
Querys to calculate totals

can someone plse tell me how to make a Constant for my database and
the "expression" i use to look it up

seeing as microsoft cant put this simple information anywhere in
there help or website

To be used in a query you need a user defined function that returns the value fo
the constant...

Public Const MyConstant = "SomeValue"

Function GetMyConstant() As String
GetMyConstant = MyConstant
End Function

 

[Guest]

Gah thanks so mush, i knew it was simple just the stupid help dosent give me
any of that information just "you can do it" but not HOW to actualy do it

Thanks so much

 

[strive4peace]

You can reference a function in, for instance, the Field cell of a query
like this:

Field --> MyColumn: GetMyConstant() * [fieldname]

~~~

How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Douglas J. Steele]

An alternative approach to what Rick and Crystal have suggested is to have a
form that's constant open contain the value in a control, and have the
queries refer to that control. (the Switchboard form makes a good choice if
you have one)

You refer to a control on an opened form as Forms!NameOfForm!NameOfControl

That would make it easier to change what value's used (although it might
also increase the possibility that it's changed incorrectly)

 

[(PeteCresswell)]

Per Douglas J. Steele:

An alternative approach to what Rick and Crystal have suggested is to have a
form that's constant open contain the value in a control, and have the
queries refer to that control. (the Switchboard form makes a good choice if
you have one)

You refer to a control on an opened form as Forms!NameOfForm!NameOfControl

That would make it easier to change what value's used (although it might
also increase the possibility that it's changed incorrectly)

Would it also be faster than having the query call a function for every row?

 

[Douglas J. Steele]

Per Douglas J. Steele:

Would it also be faster than having the query call a function for every
row?

I'm reluctant to say yes or no without testing.

It certainly depends on how the values are being used: if, for example,
they're only referenced in the Where clause, the function would only be
called once.

 

[strive4peace]

The function will only be called once for the query anyway -- because no
parameter is passed... FORCING a function to evaluate each time must be
done by passing a field, even if it is not needed.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*