Where do I put a string I want to flow to a textbox?

[jasonbarnes]

I am trying to set a string to show up in a textbox.



I DIMed the string as

Public Sub Tons()

Dim SumofBusTEUS As String

SumofBusTEUS = "SELECT Sum(tblTestData.SumOfTEUS) AS SumOfSumOfTEUS " _
& " FROM tblTestData WHERE (((([tblTestData].[#BUSRULE])) Is Not Null)) "

End Sub


Then in the control source to the textbox I did

=(SumofBusTEUS)


What I am missing causing this not to work??

[jzwp11]

You have to open up a recordset based on the query. An easier way might be to use the DSum() domain aggregate function to populate your control.


DSum("sumOfTEUS","tblTestData","tblTestdata.[#BUSRULE] is not null")

[jasonbarnes]

Wow. Much easier to accomplish this task. If I can only apply the same method to the other things I want to display.

[jzwp11]

If the values are in the same table, you can use a form to display the info. If you want to display data from another table, the domain aggregate functions can help (DLookup(), DSum(), DMin(), DMax() etc.)

I probably use DLookup() the most.

[jasonbarnes]

Can you help me with the proper syntax for DCount?

Thought it would be...

=DCount("NAMEFUNCTION","tblTestData","tblTestData.[NAMEFUNCTION]="ORDER""]

This should reflect a count of the field NAMEFUNCTION in tblTestData that is equal to ORDER. I think my problem centers around not having to use NAMEFUNCTION in the expr area. Or possibly not having to do "" around ORDER in the criteria. I've played around with it quite a bit. Maybe I'm missing something.

[jzwp11]

Since the DCount() only counts records, a field is not normally specifed; also you were using a square bracket rather than an ending parenthesis. Also if order is a fixed value (rather than a variable), you can include the whole section in double quotes. This should be the correct syntax for counting all records whose namefunction is equal to the worde order.

DCount("*","tblTestData","tblTestData.[NAMEFUNCTION]=ORDER")

[jasonbarnes]

Thanks again!

[jzwp11]

You're welcome.