sum of just some records in a query 2

[isitrans]

Help.
I have a report created from a query that shows quotes for a range of quote numbers showing 5 fields:

Quote # (this is an autogenerated number)
Date
Client
Estimate (this is in currency)
approval signed? (this is a check box for yes or no)

In the header, i have a text box showing the sum of the estimates.

I would like to add another text box showing the sum of just the estimates where approval signed = yes

I've tried using :
=DSum("[Estimate]","[ISI Quotes Table]","[Approval signed?] = 'YES'&quot

I get #error instead of the total for those jobs in the range of the report that have approval signed = yes.

any help is appreciated,

thanks
cathi

 

[PaulBricker]

Try this. I believe that even though you see the word yes, the field is actually Boolean. Therefore the value is numeric.

=DSum("[Estimate]","[ISI Quotes Table]","[Approval signed?] = -1&quot

Paul

 

[isitrans]

Hi Paul
thanks so much. changing to -1 worked. however, i still have a small problem.
i'm using "[ISI Quotes Table]", as the source and that's returning a total for all the estimates in the table.
i want to total only those estimates that correspond to whatever quote numbers that i've selected as the range for this report
How do i write it to sum just those estimaes that are approval = yes in this report not in the whole quotes table?

thanks, cathi

 

[PaulBricker]

If the query that underlies the Report just has the Estimate values you want then change "[ISI Quotes Table]" to the "[NameOfYourQuery]" and the DSum will be performed on the Filtered info.

Paul

 

[MichaelRed]

or Me.RecordsetClone

or a seperate query (actually SHOULD run faster than the DSum anyway)
MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[isitrans]

Thanks paul and michael
paul: i tried "[ISIQRange]" with both the "[ and just the [ and just the " and none of these worked. gave me #error

Michael: i tried Me.RecordsetClone but it didn't work. i'm not sure if i used it in the right context or with the proper punctuation marks. but it also gave en #error

help. what am i still doing wrong?

thanks, cathi

 

[PaulBricker]

Post the DSum you used so we can see it. Also, double check the spelling and look for typo's they lurk everywhere.

Paul

 

[isitrans]

HI Paul

here it is (copied and pasted )

=DSum(" [Estimate]","ISIQRange"," [Approval signed?] = -1&quot

This is the one with "" only for the query name

I checked for typos but i think this is all correct.
The query is named ISIQRange (copied and pasted)

thanks again!!! cathi

 

[PaulBricker]

It looks OK. Do you have any Null values in the Estimate field by any chance. This will throw an #Error.

Paul

 

[isitrans]

HI paul
no null values
the table is set to use $0.00 as a default

 

[PaulBricker]

I assume that your Checkbox is one of the Fields in your Query and not just a Control on your report. It adds up the field in the Table OK, but bombs on the Query. Just for the fun of it try
=DSum(" [Estimate]","ISIQRange"," [Approval signed?] = 'Yes'&quot

See if that makes a difference.

Paul

 

[isitrans]

hi
i tried it but still got an #error
the checkbox is a field from the query but prints on the report.
could it be something with updating
or might i have to name the [Estimates] more specifically\?

thanks, i really appreciate all the time and thought you're giving me
regards, cathi

 

[PaulBricker]

Well, we still have some questions left so the next one is where are you displaying this box in the report. It should work in the Report Header or Footer. It shouldn't work in the Detail section.
As for naming Estimates, that should be the Field name you are using in the Query. DSum looks for field Estimates in Query ISIQRange where [Approval signed?] = -1

I have the exact same thing in a report except the Names and it runs fine.
If you still can't get it, if you could email me a small sample I'd be happy to look at it. The other option is to use one of MichaelRed's suggestions. Using a seperate query would be similar to what you are trying now. You would create a query that totals Estimates. To do this you would create a new query and add two fields, Estimates and ApprovalSigned? Turn on the Totals and set the Total line for Estimates to Sum and set the Total line for ApprovalSigned? to Where and on the Criteria line put
= -1
This should return only the Sum of Estimates where the ApprovalSigned = Yes. Then in the Report you would use the DLookUp Function.
DLookUp("SumofEstimates","QueryNameHere&quot. That should do it also.

It's your decision. I'm happy to help with anything you need. Just let me know.

Paul

 

[MichaelRed]

You can do a generic query against "Me.RecordsetClone", just like any (other) table (or query). It is (of course) just the records which are available to the form, which solves one of your issues. So, in effect it is just another 'copy' of the recordsource of the form. You query can be any valid select query, is you want an aggregate, (SUM) just set it up.

[b[PaulBricker[/b]'s
=DSum(" [Estimate]","ISIQRange"," [Approval signed?] = 'Yes'&quot

(rather loosly) translates to

"Select Sum([ISIQRange]) as MySum from Estimate where [Approval Signed?] = Yes;"

which can be instantiated in your form and used for the calculation.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[isitrans]

Thank you both for your help
i wasn't available to work on it yesterday, but i will try your suggestions today and see what it happens.
regards, cathi