quick question on a formula

[Guest]

Good day everyone.

It's been a while since i've played in Access, so please bare with me.
I've got this 'text box' where in the 'control source' i have the following
expression:

=([cost]*Globals!GST)

Now the result of that is: #name?

My entention was this:

I created a global table where i will store my GST (Tax) and nothing else
for now.
That table is set as a number, decimal.

And in view mode, i inputed the value in my GST 1st record: 0.06.

Why am i getting that error message??

Im not seing something for sure here, but i dont know what..

Can anyone help?

My goal here is to have a central focol point where i could store valuable
static values that the entire system could see. And also, easy for my user to
just go and update them when required to do so...

 

[storrboy]

Good day everyone.

It's been a while since i've played in Access, so please bare with me.
I've got this 'text box' where in the 'control source' i have the following
expression:

=([cost]*Globals!GST)

Now the result of that is: #name?

My entention was this:

I created a global table where i will store my GST (Tax) and nothing else
for now.
That table is set as a number, decimal.

And in view mode, i inputed the value in my GST 1st record: 0.06.

Why am i getting that error message??

Im not seing something for sure here, but i dont know what..

Can anyone help?

My goal here is to have a central focol point where i could store valuable
static values that the entire system could see. And also, easy for my user to
just go and update them when required to do so...

The #name? message is telling you that Access has no idea what the
reference points to. The Globals!GST value must be included in the
query, table or form that you are applying this in. Either another
field in the table/query or a control on the form must be available to
reference, or use the DLookup function.

 

[Jeff Boyce]

Patrick

Are you saying that you want Access to use a value located in a field named
"GST" on a table named "Globals" as part of your expression?

I don't believe you can directly refer to a table's field value this way.

Have you looked into creating a function that returns the value located in
that table's field, then using the value returned by the function in your
control source expression?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Ya thats what i was saying. I could and will try a function then. Ill keep ya
posted.

Patrick

Are you saying that you want Access to use a value located in a field named
"GST" on a table named "Globals" as part of your expression?

I don't believe you can directly refer to a table's field value this way.

Have you looked into creating a function that returns the value located in
that table's field, then using the value returned by the function in your
control source expression?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Good day everyone.

It's been a while since i've played in Access, so please bare with me.
I've got this 'text box' where in the 'control source' i have the following
expression:

=([cost]*Globals!GST)

Now the result of that is: #name?

My entention was this:

I created a global table where i will store my GST (Tax) and nothing else
for now.
That table is set as a number, decimal.

And in view mode, i inputed the value in my GST 1st record: 0.06.

Why am i getting that error message??

Im not seing something for sure here, but i dont know what..

Can anyone help?

My goal here is to have a central focol point where i could store valuable
static values that the entire system could see. And also, easy for my user to
just go and update them when required to do so...

 

[Guest]

o i created a function and tested it. It returns my value.
How do i set it up inside that original formula i included in my first
message now.

Heres the code:
Public Function ReturnGST() As Double
On Error Resume Next

Set dbs = CurrentDb

ReturnGST = DLookup("GST", "Globals")
'MsgBox ReturnGST

End Function

what will this formula look like now;
=([cost]*Globals!GST)

?? thx again for your help.

Ya thats what i was saying. I could and will try a function then. Ill keep ya
posted.

Patrick

Are you saying that you want Access to use a value located in a field named
"GST" on a table named "Globals" as part of your expression?

I don't believe you can directly refer to a table's field value this way.

Have you looked into creating a function that returns the value located in
that table's field, then using the value returned by the function in your
control source expression?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Good day everyone.

It's been a while since i've played in Access, so please bare with me.
I've got this 'text box' where in the 'control source' i have the following
expression:

=([cost]*Globals!GST)

Now the result of that is: #name?

My entention was this:

I created a global table where i will store my GST (Tax) and nothing else
for now.
That table is set as a number, decimal.

And in view mode, i inputed the value in my GST 1st record: 0.06.

Why am i getting that error message??

Im not seing something for sure here, but i dont know what..

Can anyone help?

My goal here is to have a central focol point where i could store valuable
static values that the entire system could see. And also, easy for my user to
just go and update them when required to do so...

 

[storrboy]

o i created a function and tested it. It returns my value.
How do i set it up inside that original formula i included in my first
message now.

Heres the code:
Public Function ReturnGST() As Double
On Error Resume Next

Set dbs = CurrentDb

ReturnGST = DLookup("GST", "Globals")
'MsgBox ReturnGST

End Function

what will this formula look like now;
=([cost]*Globals!GST)

?? thx again for your help.

Ya thats what i was saying. I could and will try a function then. Ill keep ya
posted.

Patrick
Are you saying that you want Access to use a value located in a field named
"GST" on a table named "Globals" as part of your expression?
I don't believe you can directly refer to a table's field value this way.
Have you looked into creating a function that returns the value located in
that table's field, then using the value returned by the function in your
control source expression?
--
Regards
Jeff Boyce
Microsoft Office/Access MVP

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
Good day everyone.
It's been a while since i've played in Access, so please bare with me.
I've got this 'text box' where in the 'control source' i have the
following
expression:
=([cost]*Globals!GST)
Now the result of that is: #name?
My entention was this:
I created a global table where i will store my GST (Tax) and nothing else
for now.
That table is set as a number, decimal.
And in view mode, i inputed the value in my GST 1st record: 0.06.
Why am i getting that error message??
Im not seing something for sure here, but i dont know what..
Can anyone help?
My goal here is to have a central focol point where i could store valuable
static values that the entire system could see. And also, easy for my user
to
just go and update them when required to do so...

You can put the DLookUp right in the controlsource as long as you
account for unexpected values.
=([cost]*Nz(DLookup("GST", "Globals"),0)
However this will cause the DLookup to run evertime the value needs to
change and you may notice a lack of speed after a while. Put an
additional control on the form and set it's control source to
=Nz(DLookup("GST", "Globals"),0)

Your orginal control would be

=[cost]*[GSTResultControl]

 

[Jeff Boyce]

Patrick

If your form only needs to calculate the value once per CurrentRecord event
(as you load a record into the form), you should be able to substitute the
function (name) in your calculation expression for the control source.

If your form loads many records but only show one, the solution offered by
Storrboy (a hidden control on the form) may be a bit more work, but cuts
down on multiple calls.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

o i created a function and tested it. It returns my value.
How do i set it up inside that original formula i included in my first
message now.

Heres the code:
Public Function ReturnGST() As Double
On Error Resume Next

Set dbs = CurrentDb

ReturnGST = DLookup("GST", "Globals")
'MsgBox ReturnGST

End Function

what will this formula look like now;
=([cost]*Globals!GST)

?? thx again for your help.

Ya thats what i was saying. I could and will try a function then. Ill keep ya
posted.

Patrick

Are you saying that you want Access to use a value located in a field named
"GST" on a table named "Globals" as part of your expression?

I don't believe you can directly refer to a table's field value this way.

Have you looked into creating a function that returns the value located in
that table's field, then using the value returned by the function in your
control source expression?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Good day everyone.

It's been a while since i've played in Access, so please bare with me.
I've got this 'text box' where in the 'control source' i have the
following
expression:

=([cost]*Globals!GST)

Now the result of that is: #name?

My entention was this:

I created a global table where i will store my GST (Tax) and nothing else
for now.
That table is set as a number, decimal.

And in view mode, i inputed the value in my GST 1st record: 0.06.

Why am i getting that error message??

Im not seing something for sure here, but i dont know what..

Can anyone help?

My goal here is to have a central focol point where i could store valuable
static values that the entire system could see. And also, easy for my user
to
just go and update them when required to do so...

 

[Jamie Collins]

Heres the code:
Public Function ReturnGST() As Double
On Error Resume Next

Set dbs = CurrentDb

ReturnGST = DLookup("GST", "Globals")
'MsgBox ReturnGST

End Function

You said in your OP that the column "is set as a number, decimal." You
probably don't want to cast the result as Double. I suggest you change
the type of the return value to

As Variant

to preserve the DECIMAL type of the column.

Jamie.

--