Display Query in a text field

[cstickman]

Hello everyone

I was hoping someone could assist me with displaying a query result within a text field on a form. I created a query called qrychannelcount. I have two columns in it. It counts the different channels in the channel column based by vendor. Below is the sql from the query:

<code>
SELECT settlement.channel, Count(settlement.vendor) AS CountOfvendor
FROM settlement
GROUP BY settlement.channel
HAVING (((settlement.channel)="DOCA"));
</code>

I thought I could write a function and in the default value of the text field to call the function. Below is the function:

<code>
Public Function docacount()
docacount = CurrentDb.OpenRecordset("qrychannelcount").Fields(0)
End Function
</code>

Any suggestions on how I can get this to work or displayed in a text field? Thanks

 

[ranman256]

use a form.
the form is connected to the query
the query field is connected to the text box

 

[cstickman]

Thanks for the suggestion ranman, but do we have any other way of displaying the information? I have numerous queries that I have not written yet that will need to be displayed as well. Thanks

 

[Micron]

By the looks of your sql, several records will be returned, otherwise there'd be no need to group. In that case, you can't show several records for a field in a single textbox. As ranman says, a form may be your best bet since you say you want to show the results "in a text field". Aside from a report, there is no other place you can hold a "text field" (control) that I know of. If you did not mean a textbox control, the assumption was made because so many people use the word "field" when they should be saying "control". There must be something that determines which query you'd want to run, so in code, use that event as the trigger to set the recordsource of the form on the fly, and set the controlsource property accordingly. Or, your form could be a continuous form that will display your records in a repeating fashion. Or you can simply open the query as a data sheet if you are against using a form. I say all of this without knowing what the rest of the queries are like and how many fields they might contain, but the objective of the design solution should be to re-use as much as possible (for the other queries) whatever object you create.

 

[cstickman]

Hey Guys,

So I believe I did not ask the right question, but here is what I came up with after searching Google for many hours:

In the text field box under control source =DCount("[channel]","settlement","[channel] = 'DOCA'")

That returned the number I was hoping to see and I was able to add criteria to it. Here is the syntax for using DCount
DCount ( expression, domain, [criteria] )
DCount ( Column Name, Table Name, Criteria)

Thanks for your suggestions!!