Reports and the Expression Builder (1 Viewer)

bobunknown

Registered User.
Local time
Today, 14:50
Joined
May 25, 2018
Messages
77
Hi all you Report wizards :D

im trying to learn more about reports. Specifically the expression builder.

*Context
I have a database for Waste removal (bins and stuff) and I want a report that tells me how many bins of a particular size we have out currently. The database I have has a "Bin Size ID" assigned to each different size of bin (120L = 1 , 140L = 2, 240L = 3 etc).

My idea was to use the expression builder to count how many times the ID 4 (for example) shows up but the expression builder has other ideas.

This is what I have so far:

=DCount("[Bin Pricing]![Bin Size ID] = 4 and [Collections]>0")

(The bit at the end is because I don't want it counting bins we don't collect)

Any thoughts? is my approach correct? or is there anther way?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:50
Joined
May 21, 2018
Messages
8,463

bobunknown

Registered User.
Local time
Today, 14:50
Joined
May 25, 2018
Messages
77
Thanks for the info im reading up on it and making a little headway but I've not managed to get a query that can be shown in a report yet only one using the totals button in datasheet view

Im having trouble making a query in the Criteria box, when I enter Count("1") I keep getting an error saying that that cant happen. Do I need to write it outs as a string even though I have defined where I want the data from by using the query design form?
 

Minty

AWF VIP
Local time
Today, 21:50
Joined
Jul 26, 2013
Messages
10,355
Can you post up an example of your starting data and what your end goal is, you can use a spreadsheet if you want, or a stripped down version of your database.

Make sure we have enough sample data to give you your results.
 

bobunknown

Registered User.
Local time
Today, 14:50
Joined
May 25, 2018
Messages
77
Hi all sorry about the long lack of reply. I have trying to teach myself before annoying anymore of you. Those links that have been posted were massively useful thanks.

I went with using a query as suggested and have made one that should suit my needs (after some trying) however when it comes to getting it in to a report I have hit a snag.

I am trying to use the following:

=Count([Total bins])

From what I have read this should add up all the returns in the query and display them.... but it doesn't. All I get is a #Error message that isn't to useful as it gives me no direction.

Everything is spelled correctly im sure of that so dose anyone have any suggestions?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:50
Joined
Aug 30, 2003
Messages
36,118
Where is it? I don't think it will work in the page footer. It should work in a group or report footer.
 

bobunknown

Registered User.
Local time
Today, 14:50
Joined
May 25, 2018
Messages
77
Okey, ill do some more reading on the suggested. I wanna try solve it on my own just need prodding in the right direction.

Thanks
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 14:50
Joined
Aug 30, 2003
Messages
36,118
No problem, post back if you get stuck. Another thought, [Total bins] has to be a field in the report's record source. It can't be a calculated control on the report, like

=Quantity * Price
 

Users who are viewing this thread

Top Bottom