Trouble with a report total

[atadbitslow]

I have a report that has an expression that places an * next to a person's name if they are a particular discipline and are in residence during the month of April. I would like to get a total of the names that have an * next to their names but I'm having trouble with the expression to get the total. Help?

Thanks- Excuse my confusion....it's only temporary......

 

[Celeste025]

Is the * in a separate field in your db? If so, you can use the count function like this:

In the footer of your report add an unbound text box. In the control source property of the text box, put this expression:

=Count([FieldName])

 

[atadbitslow]

No, the * is the result of an iif statement in an expression-- Excuse my confusion....it's only temporary......

 

[CosmoKramer]

Is the * always at the beginning of the person's name? If so, try something like this as the control source for your count field:

=DCount("[YourNameField]","YourQueryName","Left([YourNameField],1)='*'")

If the * is at the end just substitute the Left for a Right

=DCount("[YourNameField]","YourQueryName","Right([YourNameField],1)='*'")

 

[atadbitslow]

Thanks, but the * is in its own field, not part of the name-- Excuse my confusion....it's only temporary......

 

[CosmoKramer]

Sorry, you'll have to excuse MY confusion.....

If you can add an expression field to your query that formats the name with the * when needed, instead of the text box on your report, then the previous example will work.

I guess it pays to read the whole post......

 

[atadbitslow]

Thanks, I tried what you suggested-added an expression field to my query that put the * in front of the name and used the left statement etc, but it still didn't work......there's got to be some other reason this is not working! I do totals all the time--this is frustrating!

 

[CosmoKramer]

Can you send me a zipped copy of the db so I can look at it?

sw3540@yahoo.com

 

[CosmoKramer]

Here's what I did:

I added an invisible text box to the detail section and set its' control source to

=IIf([text17]="*",1,0)

and the Running Sum property to Over All. This will accumulate the count of fields that have an "*".

I added a text box to the report footer whose control source is the text box I just added above. It will display the last value from the detail section, which is the accumulated count.

Let me know what you think.....

 

[atadbitslow]

I think you're the best.......if I had just banged my head against the wall a few more times, I might have come up with that answer myself....

Thanks a bunch!