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

Hawki_ice wrote:
> 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

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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

"Rick Brandt" wrote:

> Hawki_ice wrote:
> > 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
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>

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
*



Hawki_ice wrote:
> 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
>
> "Rick Brandt" wrote:
>
>> Hawki_ice wrote:
>>> 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
>>
>> --
>> Rick Brandt, Microsoft Access MVP
>> Email (as appropriate) to...
>> RBrandt at Hunter dot com
>>
>>
>>

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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Hawki_ice" <(E-Mail Removed)> wrote in message
news:2EFDA325-D290-4F6C-BB4E-(E-Mail Removed)...
> 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

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?
--
PeteCresswell

"(PeteCresswell)" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

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
*



Douglas J. Steele wrote:
> "(PeteCresswell)" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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?
>
> 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.
>