Newbie: can't get a calculated value on the form?

[Ed from AZ]

I have a table with numbers and a form that shows the numbers. I have
a query that takes one of the numbers and mulitplies it. I put a text
box on the form from the query result field, but I get a "#Name" error
instead of the result. When I run the query, I get the correct
result.

Help, please?
Ed

 

[Dirk Goldgar]

I have a table with numbers and a form that shows the numbers. I have
a query that takes one of the numbers and mulitplies it. I put a text
box on the form from the query result field, but I get a "#Name" error
instead of the result. When I run the query, I get the correct
result.

Is the query the recordsource of the form?

Is the name of the text box the same as the name of some other field?

 

[Ed from AZ]

Hi, Dirk.

Is the query the recordsource of the form?

No. The form is built from the table containing the field that needs
calculating.

Is the name of the text box the same as the name of some other field?

No. It's Text24 (or something like that). And the result field of
the query has a unique name also.

Ed

 

[fredg]

Hi, Dirk.

No. The form is built from the table containing the field that needs
calculating.

No. It's Text24 (or something like that). And the result field of
the query has a unique name also.

Ed

If the query returns just one record you could use DLookUp.
As control source of an unbound control:
=DLookUp("[FieldName]","QueryName")

Otherwise you cannot use a query value in the control source of a
control...only as the Record source of a form. In which case the query
field can be referred to directly, just like any other bound control.

Why do you need to perform just a calculation in the query? Why not
directly in the unbound control?
=[FieldA] * .25 or =[FieldA] * [FieldB]

 

[Dirk Goldgar]

Hi, Dirk.

No. The form is built from the table containing the field that needs
calculating.

No. It's Text24 (or something like that). And the result field of the
query has a unique name also.

So how are you referring to it in the controlsource of the text box? It
sounds like you're going about it the wrong way, and may be doing
unnecessary work to boot.

Fred has posted some observations and suggestions that look good to me.

 

[Ed from AZ]

Fred:

What I've got is values in kilometers that I want to convert to
miles. I have four of these values across the same record.

I tried this two different ways. In one unbound text box, I reference
the table field holding the value like this:
=[tabPartsInstl!InstallKM]*0.6214
In another unbound text box, I reference the query field which
contains the result of the calculation like this:
=[qryPrevMI!InstallMI]
In a third, I followed your example and typed this into the Control
Source field of the Properties dialog:
=DLookUp(["InstallMI"],["qryPrevMI"])

All three return a "#Name" error??

Ed

Hi, Dirk.
No.  The form is built from the table containing the field that needs
calculating.
No.  It's Text24 (or something like that).  And the result field of
the query has a unique name also.

If the query returns just one record you could use DLookUp.
As control source of an unbound control:
=DLookUp("[FieldName]","QueryName")

Otherwise you cannot use a query value in the control source of a
control...only as the Record source of a form. In which case the query
field can be referred to directly, just like any other bound control.

Why do you need to perform just a calculation in the query? Why not
directly in the unbound control?
=[FieldA] * .25 or =[FieldA] * [FieldB]

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -

 

[Dirk Goldgar]

What I've got is values in kilometers that I want to convert to
miles. I have four of these values across the same record.

I tried this two different ways. In one unbound text box, I reference
the table field holding the value like this:
=[tabPartsInstl!InstallKM]*0.6214
In another unbound text box, I reference the query field which
contains the result of the calculation like this:
=[qryPrevMI!InstallMI]
In a third, I followed your example and typed this into the Control
Source field of the Properties dialog:
=DLookUp(["InstallMI"],["qryPrevMI"])

All three return a "#Name" error??

They are all using incorrect syntax, though the last is incorrect in a
different way. Assuming that your form is based on tabPartsInst, and the
InstallKM field is included in the form's recordsource, then your text box
could have this controlsource:

=[InstallKM]*0.6214

That's a corrected version of the first of your three approaches. You can't
use the second approach at all, because the query is not part of the form's
recordsource. The third approach, the DLookup, could be made to work, but
would be slower and more complicated than just using the controlsource
expression above.

 

[Ed from AZ]

That did it, Dirk!

So I need to stay within the fields of the form's recordsource, and
reference the field without the table name. I'm sure there's more and
different ways of accomplishing the same thing, but this will do me
now for this application.

I appreciate the boost.
Ed

What I've got is values in kilometers that I want to convert to
miles.  I have four of these values across the same record.
I tried this two different ways.  In one unbound text box, I reference
the table field holding the value like this:
     =[tabPartsInstl!InstallKM]*0.6214
In another unbound text box, I reference the query field which
contains the result of the calculation like this:
    =[qryPrevMI!InstallMI]
In a third, I followed your example and typed this into the Control
Source field of the Properties dialog:
    =DLookUp(["InstallMI"],["qryPrevMI"])

All three return a "#Name" error??

They are all using incorrect syntax, though the last is incorrect in a
different way.  Assuming that your form is based on tabPartsInst, and the
InstallKM field is included in the form's recordsource, then your text box
could have this controlsource:

    =[InstallKM]*0.6214

That's a corrected version of the first of your three approaches.  You can't
use the second approach at all, because the query is not part of the form's
recordsource.  The third approach, the DLookup, could be made to work, but
would be slower and more complicated than just using the controlsource
expression above.

 

[fredg]

What I've got is values in kilometers that I want to convert to
miles. I have four of these values across the same record.

I tried this two different ways. In one unbound text box, I reference
the table field holding the value like this:
=[tabPartsInstl!InstallKM]*0.6214
In another unbound text box, I reference the query field which
contains the result of the calculation like this:
=[qryPrevMI!InstallMI]
In a third, I followed your example and typed this into the Control
Source field of the Properties dialog:
=DLookUp(["InstallMI"],["qryPrevMI"])

All three return a "#Name" error??

They are all using incorrect syntax, though the last is incorrect in a
different way. Assuming that your form is based on tabPartsInst, and the
InstallKM field is included in the form's recordsource, then your text box
could have this controlsource:

=[InstallKM]*0.6214

That's a corrected version of the first of your three approaches. You can't
use the second approach at all, because the query is not part of the form's
recordsource. The third approach, the DLookup, could be made to work, but
would be slower and more complicated than just using the controlsource
expression above.

In addition to what Dirk has pointed out to you, the DLookUp is
incorrectly written.
You have
=DLookUp(["InstallMI"],["qryPrevMI"])
The quotes must be outside the brackets.
=DLookUp("[InstallMI]","[qryPrevMI]")

Also, make sure the name of the control that uses an expression as
it's control source is not the same as the name of any field used in
the expression.