Text box in report to count field in a query/table. (1 Viewer)

[Skeu-Skeu]

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

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

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

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.

 

[ebs17]

For direct table access:

=DCount("*", "tblNumbers", "Weights > 10")

 

[Skeu-Skeu]

For direct table access:

=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?

 

[Pat Hartman]

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

=Sum(IIf([Weights] > 10, 1, 0))

 

[Skeu-Skeu]

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?

 

[Pat Hartman]

There is something else wrong. Does the application compile cleanly? Perhaps the form is corrupted.

 

[AccessBlaster]

like this

 

[Mike Krailo]

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?

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

 

[Skeu-Skeu]

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.

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?

 

[AccessBlaster]

See if this helps

 

[Mike Krailo]

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.

 

[AccessBlaster]

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.

 

[Pat Hartman]

You are using the name of a bound field "Weights" as the name of an unbound control. Change the name of the control.

 

[Skeu-Skeu]

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?

(Test_AWF)

(My File)

 

[Pat Hartman]

The answer is in #13

 

[Skeu-Skeu]

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?

=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.