Blank Data

[Penny]

How would I fill in data that comes back in a query blank
with a 0.00?

Thanks
Penny

 

[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?

 

[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;

 

[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>




.

.

 

[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>

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>




.