By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,816 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Sum trouble

P: n/a
SJH
I have a report that I am working with and after some changes were made to
the database to keep more accurate information we are now struggling with
trying to utilize some of the old information.

The database is used for request for quote (RFQ) information and previously
any job that was accepted, the quoted information was changed to the
accepted dollar amount but to do this the quantities and part prices had to
be adjusted to get the calculated field to show the correct value. We made
a change and added a field for the accepted value so the quoted dollar
amount could be left alone. The accepted field is a currency field and the
dollar amount is entered directly, no calculations are performed to
determine this value.

Here's where I have the problem. On my reports, I changed everything where
we were previously showing accepted dollars to the new ACCEPTED field,
obviously the sums are working great to this point. Now they want to go
back and pull data from before this change was made. I have since added a
macro to show the QUOTED value if the ACCEPTED value is null. With that I
was happy, but now I for the life of me cannot figure out how to get a sum
for the column with these two values. All records are based on an entered
date range.

I have tried the following, but with no luck and unfortunately my experience
with this stuff is limited to trial and error,

=Sum(IIf([ACCEPTED] Is Null, 0, [QUOTED]))

Any helpful suggestions are welcome.

Thanks.
SJH
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
SJH wrote:
I have a report that I am working with and after some changes were made to
the database to keep more accurate information we are now struggling with
trying to utilize some of the old information.

The database is used for request for quote (RFQ) information and previously
any job that was accepted, the quoted information was changed to the
accepted dollar amount but to do this the quantities and part prices had to
be adjusted to get the calculated field to show the correct value. We made
a change and added a field for the accepted value so the quoted dollar
amount could be left alone. The accepted field is a currency field and the
dollar amount is entered directly, no calculations are performed to
determine this value.

Here's where I have the problem. On my reports, I changed everything where
we were previously showing accepted dollars to the new ACCEPTED field,
obviously the sums are working great to this point. Now they want to go
back and pull data from before this change was made. I have since added a
macro to show the QUOTED value if the ACCEPTED value is null. With that I
was happy, but now I for the life of me cannot figure out how to get a sum
for the column with these two values. All records are based on an entered
date range.

I have tried the following, but with no luck and unfortunately my experience
with this stuff is limited to trial and error,

=Sum(IIf([ACCEPTED] Is Null, 0, [QUOTED]))

Any helpful suggestions are welcome.

Thanks.
SJH

See if
=Sum(IIf(IsNull([ACCEPTED]), 0, [QUOTED]))
works

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.