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