query tied to text box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brennan - 29 May 2008 19:00 GMT

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

Reply to this Message

Klatuu - 29 May 2008 19:18 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> 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

Reply to this Message

Brennan - 29 May 2008 19:23 GMT

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

Reply to this Message

Klatuu - 29 May 2008 19:31 GMT

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")

Signature

Dave Hargis, Microsoft Access MVP

> 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
[quoted text clipped - 7 lines]
>
> Brennan

Reply to this Message

Brennan - 29 May 2008 19:53 GMT

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")

Reply to this Message

Klatuu - 29 May 2008 20:44 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> 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
[quoted text clipped - 5 lines]
> =DSum("[Net Amount - US]", "MFGCosts","[Accounting Period *]=200804 AND [FML
> Account Code *])=4435")

Reply to this Message