Dcount in report

[Sipozove]

I'm trying to use a report for the first time.
I want it to display some statistics.

I can do just fine in VBA though a button which output a message.

Case1 = Nz(DCount("*", "[tbl2022]", "[Field7] = '" & Replace("AAA", "'", "''") & "' "), 0)
Case2 = Nz(DCount("*", "[tbl2022]", "[Field7] = '" & Replace("BBB", "'", "''") & "' "), 0)

The popup message was a quick and effective output in the beginning.
Not anymore.

How can I create a report that displays the same information?

 

[GPGeorge]

Create a query that displays the two values you need, i.e. Case1 and Case2. I have a hard time, though, understanding the syntax there.

Use that query as the recordsource for the report.

Create two text box controls on the report. Bind one to the field "Case1" and one to the field "Case2".

There are a couple of additional concerns, though, suggested by the table and field names shown.
"tbl2022" suggests you are planning to segregate data in different tables by year. That's a problem waiting to spring itself on you next year. More than that, it offers NO clue as to what data is in that table.

"Field7" ? Why not name it for the contents in it? Accepting generic names like that makes future maintenance a nightmare.

 

[arnelgp]

add an 2 unbound textbox to your report.
the ControlSource of one is:

=Nz(DCount("*", "[tbl2022]", "[Field7] = '" & Replace("AAA", "'", "''") & "' "), 0)

the ControlSource of the other:

=Nz(DCount("*", "[tbl2022]", "[Field7] = '" & Replace("BBB", "'", "''") & "' "), 0)

 

[plog]

DCount will not return null, no need for NZ()

 

[Sipozove]

Create a query that displays the two values you need, i.e. Case1 and Case2. I have a hard time, though, understanding the syntax there.

Use that query as the recordsource for the report.

Create two text box controls on the report. Bind one to the field "Case1" and one to the field "Case2".

There are a couple of additional concerns, though, suggested by the table and field names shown.
"tbl2022" suggests you are planning to segregate data in different tables by year. That's a problem waiting to spring itself on you next year. More than that, it offers NO clue as to what data is in that table.

"Field7" ? Why not name it for the contents in it? Accepting generic names like that makes future maintenance a nightmare.

George,

Thank you for your concern.
This table is needed because of an operation happening this year. i don't plan to create any table each year.
The names of the fields are something I have to change but doing it carefully ;-)

I thought about creating a query but I didn't want to do ti "just for that". I'll look into it.

 

[Sipozove]

add an 2 unbound textbox to your report.
the ControlSource of one is:

=Nz(DCount("*", "[tbl2022]", "[Field7] = '" & Replace("AAA", "'", "''") & "' "), 0)

the ControlSource of the other:

=Nz(DCount("*", "[tbl2022]", "[Field7] = '" & Replace("BBB", "'", "''") & "' "), 0)

arnelgp,

I get this error: "The Expression you entered contains invalid syntax"
It works in a form but not in a report.

 

[arnelgp]

actually you dont need to use Replace since there is no single quote on your Expression:

=DCount("*", "[tbl2022]", "[Field7] = 'AAA'")

=DCount("*", "[tbl2022]", "[Field7] = 'BBB'")

 

[Sipozove]

I just broke something...
I'll come back to you...

 

[Pat Hartman]

This table is needed because of an operation happening this year. i don't plan to create any table each year.

Could you have a similar need in the future? If so, include a field with the date and use more generic names. Do NOT "hard code" things you generalize.

Think of this advice as "defensive programming".