Trouble with data Control Sources for added Text Boxes

[Dor100]

The Help section indicates that you should be able to use SQL statements as the control source for text boxes that you add to your reports (I'm in Acc 97). I have tried many times and it won't work. Is there some special punctuation/syntax needed (I'm already pretty experienced writing SQL statements outside reports)? Also, when I simply try another way, such as a Count function in a text box in a group footer, I need to get a distinct count of, for instance "1", instead of "2" when the report's underlying query lists an entity 2 times but you have it appear grouped as only one in your report. Hope this was clear, and help would be appreciated.

If I could just use SQL statements in text boxes, I could accomplish almost anything here. Is it too good to be true and the Help section is bluffing?

 

[CosmoKramer]

Can you post some examples of what you're trying to do??

 

[Dor100]

Here's an example of the control for a text box in the report footer related to a bunch of yes/no boxes in the body of the report, in which I would rather say something like "select count(field) from table where field = -1;". I have instead gotten the necessary result by a round-about calculation with "=Sum([field])*-1" because I couldn't get an SQL statement to work.

In another text box in the report footer, I have used "=Count([field])", but it returns the number of occurrences in the report's underlying query (2) instead of the number of occurrences appearing grouped in the report (1). What I need is the 1.

Thanks for whatever you can do, and please let me know if more info is needed.

 

[CosmoKramer]

For you first example you also could have used the Abs function and returned the absolute value:

=Abs(Sum([field]))

For your second example, you should be able to put the "=Count([field])" statement in the group footer.....

 

[Dor100]

Thanks. I tried the Count in the group footer, but it's giving the same result.

Do you know if SQL statements really are supposed to work in report text boxes? Perhaps some special punc. marks are needed?

 

[CosmoKramer]

Then try this:

=Count(*)

 

[Dor100]

I just tried it and it's giving the same. Thanks for trying.