Question Formula Problems

[bellis]

I have two formulas that seem like they should work, but have something wrong. I'm guessing it's a syntax problem, but can't for the life of me figure it out.

This one doesn't give me an error, its just blank:
=Count("[QueryName]![QueryField]='String'")

This one gives me an Error. It's supposed to calculate a percentage. [ReportField] is working properly:
=[ReportField]/Sum([QueryName]![QueryField])

Thanks in advance!

 

[vbaInet]

Is this for a report or a form?
In what section did you place the textbox?

Like so:

=Count(IIF([[COLOR=blue]FieldName[/COLOR]] = "SomeString", [B][I][COLOR=blue]True value[/COLOR][/I][/B], Null))

 

[bellis]

Both are in the Details section of a Report.

I tried separating the functions and can't even get a =SUM([Field]) to work.

I tried changing the font color to make sure it's not white.
I tried quote marks everywhere you can imagine.
I tried =SUM([QueryName]![Field]), also with and without quotes.

What could I be missing?
:banghead:

Thanks.

 

[vbaInet]

It is without quotes. With quotes indicates it's a string.

You should not include [QueryName]! and if the field is not in the Record Source of your report then it won't work. If you want to pull data from a separate query altogether, use a subreport or (the slower alternative) a DLookup() function.

 

[RainLover]

Sum will not work in the body of a Report.

Place the Sum Function in the Query.

 

[vbaInet]

Just fyi: It's fine in the Detail section but you just won't get the results you expect. The only sections the domain aggregate functions don't work are the Page Header and Footer sections.

 

[RainLover]

Sum will not work in the body of a Report.

I should of said that the "Sum" will not work on a Control that is in the Body of a Report.

 

[vbaInet]

Ah alright, I think you mean on "Calculated" values.

 

[RainLover]

Ah alright, I think you mean on "Calculated" values.

Not exactly

If we have a Field called Apples which is Number and it is placed on a report.

If you were to do as I do, rename that Control to txtApples, then Sum will not work on txtApples. It must operate on the Source, namely Apples.

I believe this to be correct.

EDIT

This may be more applicable on Forms but I think Reports act the same.

 

[vbaInet]

Gotcha! That was why it was mentioned here earlier

You should not include [QueryName]! and if the field is not in the Record Source of your report then it won't work.