P
Penny
How would I fill in data that comes back in a query blank
with a 0.00?
Thanks
Penny
with a 0.00?
Thanks
Penny
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
Ken Snell
Probably you could use the Nz function. But to help you further, can you
give us a bit more info about the query's SQL statement and about how you
want to use the query's records' data?
give us a bit more info about the query's SQL statement and about how you
want to use the query's records' data?
G
Guest
Ken,
I'm using this data in multiple currency text boxes in a
report and if one field is blank in the report then it
will not add all the columns up to give me a grand total.
Below is my SQL statement for the query.
Thanks
Penny
SELECT qrySumOfAgencyFeesRE.[SumOfAgency Fee],
qrySumOfOtherFeesRE.[Grand Total],
qrySumOfSyndicationFeesRE.[SumOfIncremental fee income],
qrySumOfSyndicationFeesRE.[Real Estate Market],
tblHierarchyInformation.[Line of Business],
tblHierarchyInformation.Division, tblHierarchyInformation.
[Division Head], qrySumOfDerivativeFeesRE.SumOfNPV
FROM ((((tblHierarchyInformation LEFT JOIN tblCustomer ON
tblHierarchyInformation.[Lending Unit] = tblCustomer.
[Lending Unit]) INNER JOIN qrySumOfSyndicationFeesRE ON
tblHierarchyInformation.[Real Estate Market] =
qrySumOfSyndicationFeesRE.[Real Estate Market]) INNER JOIN
qrySumOfDerivativeFeesRE ON qrySumOfSyndicationFeesRE.
[Real Estate Market] = qrySumOfDerivativeFeesRE.[Real
Estate Market]) LEFT JOIN qrySumOfAgencyFeesRE ON
qrySumOfSyndicationFeesRE.[Real Estate Market] =
qrySumOfAgencyFeesRE.[Real Estate Market]) LEFT JOIN
qrySumOfOtherFeesRE ON qrySumOfSyndicationFeesRE.[Real
Estate Market] = qrySumOfOtherFeesRE.[Real Estate Market]
GROUP BY qrySumOfAgencyFeesRE.[SumOfAgency Fee],
qrySumOfOtherFeesRE.[Grand Total],
qrySumOfSyndicationFeesRE.[SumOfIncremental fee income],
qrySumOfSyndicationFeesRE.[Real Estate Market],
tblHierarchyInformation.[Line of Business],
tblHierarchyInformation.Division, tblHierarchyInformation.
[Division Head], qrySumOfDerivativeFeesRE.SumOfNPV;
I'm using this data in multiple currency text boxes in a
report and if one field is blank in the report then it
will not add all the columns up to give me a grand total.
Below is my SQL statement for the query.
Thanks
Penny
SELECT qrySumOfAgencyFeesRE.[SumOfAgency Fee],
qrySumOfOtherFeesRE.[Grand Total],
qrySumOfSyndicationFeesRE.[SumOfIncremental fee income],
qrySumOfSyndicationFeesRE.[Real Estate Market],
tblHierarchyInformation.[Line of Business],
tblHierarchyInformation.Division, tblHierarchyInformation.
[Division Head], qrySumOfDerivativeFeesRE.SumOfNPV
FROM ((((tblHierarchyInformation LEFT JOIN tblCustomer ON
tblHierarchyInformation.[Lending Unit] = tblCustomer.
[Lending Unit]) INNER JOIN qrySumOfSyndicationFeesRE ON
tblHierarchyInformation.[Real Estate Market] =
qrySumOfSyndicationFeesRE.[Real Estate Market]) INNER JOIN
qrySumOfDerivativeFeesRE ON qrySumOfSyndicationFeesRE.
[Real Estate Market] = qrySumOfDerivativeFeesRE.[Real
Estate Market]) LEFT JOIN qrySumOfAgencyFeesRE ON
qrySumOfSyndicationFeesRE.[Real Estate Market] =
qrySumOfAgencyFeesRE.[Real Estate Market]) LEFT JOIN
qrySumOfOtherFeesRE ON qrySumOfSyndicationFeesRE.[Real
Estate Market] = qrySumOfOtherFeesRE.[Real Estate Market]
GROUP BY qrySumOfAgencyFeesRE.[SumOfAgency Fee],
qrySumOfOtherFeesRE.[Grand Total],
qrySumOfSyndicationFeesRE.[SumOfIncremental fee income],
qrySumOfSyndicationFeesRE.[Real Estate Market],
tblHierarchyInformation.[Line of Business],
tblHierarchyInformation.Division, tblHierarchyInformation.
[Division Head], qrySumOfDerivativeFeesRE.SumOfNPV;
P
Penny
-----Original Message-----
Ken,
I'm using this data in multiple currency text boxes in a
report and if one field is blank in the report then it
will not add all the columns up to give me a grand total.
Below is my SQL statement for the query.
Thanks
Penny
SELECT qrySumOfAgencyFeesRE.[SumOfAgency Fee],
qrySumOfOtherFeesRE.[Grand Total],
qrySumOfSyndicationFeesRE.[SumOfIncremental fee income],
qrySumOfSyndicationFeesRE.[Real Estate Market],
tblHierarchyInformation.[Line of Business],
tblHierarchyInformation.Division, tblHierarchyInformation.
[Division Head], qrySumOfDerivativeFeesRE.SumOfNPV
FROM ((((tblHierarchyInformation LEFT JOIN tblCustomer ON
tblHierarchyInformation.[Lending Unit] = tblCustomer.
[Lending Unit]) INNER JOIN qrySumOfSyndicationFeesRE ON
tblHierarchyInformation.[Real Estate Market] =
qrySumOfSyndicationFeesRE.[Real Estate Market]) INNER JOIN
qrySumOfDerivativeFeesRE ON qrySumOfSyndicationFeesRE.
[Real Estate Market] = qrySumOfDerivativeFeesRE.[Real
Estate Market]) LEFT JOIN qrySumOfAgencyFeesRE ON
qrySumOfSyndicationFeesRE.[Real Estate Market] =
qrySumOfAgencyFeesRE.[Real Estate Market]) LEFT JOIN
qrySumOfOtherFeesRE ON qrySumOfSyndicationFeesRE.[Real
Estate Market] = qrySumOfOtherFeesRE.[Real Estate Market]
GROUP BY qrySumOfAgencyFeesRE.[SumOfAgency Fee],
qrySumOfOtherFeesRE.[Grand Total],
qrySumOfSyndicationFeesRE.[SumOfIncremental fee income],
qrySumOfSyndicationFeesRE.[Real Estate Market],
tblHierarchyInformation.[Line of Business],
tblHierarchyInformation.Division, tblHierarchyInformation.
[Division Head], qrySumOfDerivativeFeesRE.SumOfNPV;
.-----Original Message-----
Probably you could use the Nz function. But to help you further, can you
give us a bit more info about the query's SQL statement and about how you
want to use the query's records' data?
--
Ken Snell
<MS ACCESS MVP>
.
K
Ken Snell
How are you summing the values? In a textbox in the report footer?
Assuming that you're using as the textbox's control source an expression
similar to this:
=Sum([FieldName])
Change it to this:
=Sum(Nz([FieldName], 0))
--
Ken Snell
<MS ACCESS MVP>
Assuming that you're using as the textbox's control source an expression
similar to this:
=Sum([FieldName])
Change it to this:
=Sum(Nz([FieldName], 0))
--
Ken Snell
<MS ACCESS MVP>
Ken,
I'm using this data in multiple currency text boxes in a
report and if one field is blank in the report then it
will not add all the columns up to give me a grand total.
Below is my SQL statement for the query.
Thanks
Penny
SELECT qrySumOfAgencyFeesRE.[SumOfAgency Fee],
qrySumOfOtherFeesRE.[Grand Total],
qrySumOfSyndicationFeesRE.[SumOfIncremental fee income],
qrySumOfSyndicationFeesRE.[Real Estate Market],
tblHierarchyInformation.[Line of Business],
tblHierarchyInformation.Division, tblHierarchyInformation.
[Division Head], qrySumOfDerivativeFeesRE.SumOfNPV
FROM ((((tblHierarchyInformation LEFT JOIN tblCustomer ON
tblHierarchyInformation.[Lending Unit] = tblCustomer.
[Lending Unit]) INNER JOIN qrySumOfSyndicationFeesRE ON
tblHierarchyInformation.[Real Estate Market] =
qrySumOfSyndicationFeesRE.[Real Estate Market]) INNER JOIN
qrySumOfDerivativeFeesRE ON qrySumOfSyndicationFeesRE.
[Real Estate Market] = qrySumOfDerivativeFeesRE.[Real
Estate Market]) LEFT JOIN qrySumOfAgencyFeesRE ON
qrySumOfSyndicationFeesRE.[Real Estate Market] =
qrySumOfAgencyFeesRE.[Real Estate Market]) LEFT JOIN
qrySumOfOtherFeesRE ON qrySumOfSyndicationFeesRE.[Real
Estate Market] = qrySumOfOtherFeesRE.[Real Estate Market]
GROUP BY qrySumOfAgencyFeesRE.[SumOfAgency Fee],
qrySumOfOtherFeesRE.[Grand Total],
qrySumOfSyndicationFeesRE.[SumOfIncremental fee income],
qrySumOfSyndicationFeesRE.[Real Estate Market],
tblHierarchyInformation.[Line of Business],
tblHierarchyInformation.Division, tblHierarchyInformation.
[Division Head], qrySumOfDerivativeFeesRE.SumOfNPV;
-----Original Message-----
Probably you could use the Nz function. But to help you further, can you
give us a bit more info about the query's SQL statement and about how you
want to use the query's records' data?
--
Ken Snell
<MS ACCESS MVP>
.