Textboxes = Result of Query? (1 Viewer)

[ChampionDuy]

Can I make a textbox on a report = the result of a query? I have tried numerous ways and I cant seem to figure it out. I am not very good with the expression builder either. I tried that but it did not work. Thanks

 

[dcx693]

That depends what the result of the query is. Often, a query returns a recordset with multiple rows and fields. If you're using a totals query with just one row and field, then it's a bit easier.

The result of the query needs to be returned in VBA code and the value of the textbox needs to be set equal to the value returned by the query.

Tell us more about the query.

 

[ChampionDuy]

It is a query that counts the total of a field when it equeal a certain value. I will copy and paste an example.

SELECT Count([Orig_Stat]) AS Orig_MIL
FROM review
WHERE [review].[ORIG_STAT]='MIL'
HAVING (((Review.DATE_COMP)>=GetStartingDate() And (Review.DATE_COMP)<=GetEndingDate()));

Sorry I should have done this before.

 

[Pat Hartman]

Use the DCount() function.

=Dcount("[Orig_Stat]", "review", "Review.DATE_COMP)>=GetStartingDate() And (Review.DATE_COMP)<=GetEndingDate()")

 

[ChampionDuy]

I could put that in the control source of a text box on a Report? I will try.

 

[ChampionDuy]

Control source didnt work, but when I added another "(" and set the text box equal to that in the On Activate event of the report it worked fine. Thanks for the help.

 

[Pat Hartman]

Sorry, there were too many parentheses and I didn't cut enough of them:

=Dcount("[Orig_Stat]", "review", "Review.DATE_COMP >= GetStartingDate() And Review.DATE_COMP <= GetEndingDate()")