Text box in report to count field in a query/table.

Skeu-Skeu

New member
Local time
Today, 08:37
Joined
Nov 19, 2023
Messages
6
Hi All,

I'm trying to build a report that has text boxes which will count fields in a given query or table, for example I have multiple products in this table with various weights

1700402099713.png


Now I want a report which will count all the weights in that field which are >10 (which should be 2). So I've made a blank report, inserted a text box inside and within the "data" property inputted this expression =Sum(IIf([tblNumbers]![Weights] > 10, 1, 0)) but I return this instead

1700402335178.png


I double checked the fields in the numbers table just to see if its numeric as opposed to short text and it is numeric.

1700402752796.png


I'm definitely missing a step but I cannot figure where it is, I'm thinking it should be rather simple to add a count to a report for fields in table or query but there seems to be some nuance to it. Any help would be greatly appreciated, if I need to add more context please I can do so.
 
For direct table access:
Code:
=DCount("*", "tblNumbers", "Weights > 10")
 
For direct table access:
Code:
=DCount("*", "tblNumbers", "Weights > 10")
Thanks for the response, I've pasted this in the control source but I'm still returning an error, is there any checks I should do in the formatting or fields in the table etc?

1700405222731.png
 
Your Sum() is not SQL. Do NOT include the table name.

=Sum(IIf([Weights] > 10, 1, 0))
 
Your Sum() is not SQL. Do NOT include the table name.

=Sum(IIf([Weights] > 10, 1, 0))
Understood, I used the formula you provided and I still return this. Very stumped as to what is giving me error still. Is it just the case that I cannot just make a blank report, put a textbox in and have it count a field from any other table/query without doing something first?

1700408608552.png
 
There is something else wrong. Does the application compile cleanly? Perhaps the form is corrupted.
 
That textbox has to be in the footer or header area for starters. Plus, why is there a sum function if all you want is a count of the records that are over the 10 value of weight?

Code:
=DCount("Weights","tblNumbers","Weights > 10")
 

Attachments

like this

I've given this a shot also but the expression is invalid, I think I've matched it unless there's something I misspelled etc.

1700410154902.png


There is something else wrong. Does the application compile cleanly? Perhaps the form is corrupted.
That's possible, I checked out what the error was in design view and I'm getting that it's a circular reference. I just made this database to test the report out as a proof of concept, how would I check for corruptions?

1700410386708.png
 
As I said, you cannot put that text box in the detail section. It has to go in the page header or footer.
Edit: Well I guess you can do it like AccessBlaster has shown, but that's a pretty useless report if that's done that way.
 
As I said, you cannot put that text box in the detail section. It has to go in the page header or footer.
Edit: Well I guess you can do it like AccessBlaster has shown, but that's a pretty useless report if that's done that way.
I agree, it's needs to be reworked, I was just replicating what the OP already had.
 
You are using the name of a bound field "Weights" as the name of an unbound control. Change the name of the control.
 
See if this helps
Yours works, but when I copy paste the same formula for my database I get an error. Usually it would give a warning triangle for the error (like the circular reference above) but in this case I don't get a result. I also put it in the footer, yeah it's a wip presentation wise. Is there a way I can find if I have any conflicting settings etc which would prevent me from being able to use these formulas?

1700411403974.png


(Test_AWF)

1700411519595.png


(My File)
 
The answer is in #13
That textbox has to be in the footer or header area for starters. Plus, why is there a sum function if all you want is a count of the records that are over the 10 value of weight?

Code:
=DCount("Weights","tblNumbers","Weights > 10")
Sorry for the waste of time guys just saw my formula had this for some reason:
=DCount("Weight","tblNumbers","Weight > 10")
which as you can see doesn't match the "Weights" tblNumbers, the minute I replaced it everything was sorted.

Many thanks for your patience with helping me out.
 

Users who are viewing this thread

Back
Top Bottom