query tied to text box

[Brennan]

I have a query that results in a single record. I would like to tie that to
a text box in a report. In the past, I have used SQL with VBA behind the
scenes to do this. Is there an easier way to get a text box to display the
results of a query?

Brennan

 

[Klatuu]

If the query returns only one field for one record, a DLookup in the text
box's Control source would be a better way to do it. If you are trying to
get more than one field in the text box, post back with more detail.

 

[Brennan]

I am just looking to return one field, but it is important to note that the
one field is the result of the following query. Will the Dlookup work with
this as well? Is so could you provide an example? Thanks

SELECT Sum(MFGCosts.[Net Amount - US]) AS [SumOfNet Amount - US]
FROM MFGCosts
GROUP BY MFGCosts.[Accounting Period *], MFGCosts.[FML Account Code *]
HAVING (((MFGCosts.[Accounting Period *])=200804) AND ((MFGCosts.[FML
Account Code *])=4435));

Brennan

 

[Klatuu]

In this case, it would be a DSum.
Is [Accounting Period *] A text field or a numeric field? It will make a
difference in whether it returns a value correctly. Same for [FML Account
Code *].

=DSum("[Net Amount - US]", "MFGCosts","[Accounting Period *]=200804 AND [FML
Account Code *])=4435")

Also, if you want to be able to use periods other than 200804, you can
reference another text box on your form that would have that value. For
example, say you have a control name txtAcctPeriod:

=DSum("[Net Amount - US]", "MFGCosts","[Accounting Period *]=" &
txtAcctPeriod & " AND [FML Account Code *])=4435")

 

[Brennan]

Thanks Dave - I appreciate all of your help. Both Account Period and FML
Account Code are numbers, but they are treated like text. I changed the
field properties in the table to text for those fields.

Is this still the same formula that I need to put in the control source of
the text box?

=DSum("[Net Amount - US]", "MFGCosts","[Accounting Period *]=200804 AND [FML
Account Code *])=4435")

 

[Klatuu]

It all depends on the data types of the fields in the table. If they are
numeric fields, then the code should be correct, but if they are text fields,
you would need to change it to:
=DSum("[Net Amount - US]", "MFGCosts","[Accounting Period *]=""200804"" AND
[FML Account Code *])=""4435""")

Now, when I say it is correct, that doesn't rule out a syntax problem
because this was written in this editor, so there is no way for me to ensure
I didn't make a mistake.
Let me know how it works out.