Null Value?

  • Thread starter Thread starter Michael and Fayann
  • Start date Start date
M

Michael and Fayann

A new scenario...

I have a report that lists currency and checks. I have them total all
catagories, which works fine. However, if I don't have any checks to
report, everything comes up as an error. How can I eliminate the error when
I don't have any checks to report?

Mike
 
You can normalize your table structures (just a guess since you didn't
provide any information). If this misses the mark, then use the Nz()
function.
 
I don't think normalizing the table is what I need to do. Where do I put
the Nz() function?

Mike
 
Mike, you have not given us much to go on, so it's a bit hard to know how to
tell you where to put it.

If you have a text box in the Report Footer section, and it has a Control
Source of:
=Sum([Amount])
you could use:
=Nz(Sum([Amount]), 0)

If you are reading a value from a subreport, you need to check the HasData
property of the report in the subreport control:
=IIf([MySub].[Report].[HasData], Nz([MySub].[Report].[MyTextBox], 0), 0)
 
From the first posting, I am assuming the columns are being added rather
than rows. This suggests the use of:
Nz([Currency1],0)+Nz([Currency2],0)+Nz([Checks],0)

--
Duane Hookom
MS Access MVP


Allen Browne said:
Mike, you have not given us much to go on, so it's a bit hard to know how to
tell you where to put it.

If you have a text box in the Report Footer section, and it has a Control
Source of:
=Sum([Amount])
you could use:
=Nz(Sum([Amount]), 0)

If you are reading a value from a subreport, you need to check the HasData
property of the report in the subreport control:
=IIf([MySub].[Report].[HasData], Nz([MySub].[Report].[MyTextBox], 0), 0)


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Michael and Fayann said:
I don't think normalizing the table is what I need to do. Where do I put
the Nz() function?

Mike
Click to expand...
Click to expand...
 
OK Here we go.

I developed a table called tblMoneyCount with the following fields in it:
OrganizationID - AutoNumber
Organization - Text (This would be Spanish Club, Yearbook, etc.)
GeneralOrganization - Text (This would be Elementary or High School)
Date - Date
Pennies - Number
Nichels- Number
(All the way down through Hundreds)

I have another table called tblChecks to enter individual checks received.
The following fields exist in it:
CheckID - AutoNumber
OrganizationID - Number
CheckName - Text (This is to enter who wrote the check)
Amount - Currency

I formed a relationship of 1 to many with OrganizationID

I then created a report from the report wizard using information from both
tables. I have a report header, and Organzxiation ID Header, a Detail
section and a Page Header.

I have the report run off of the Organzaition ID. If there are checks
written, a get a valid report. If the organization had NO CHECKS written to
them, when I run the report, every field is black except the calculated
field for summing the checks. In that calculated field it says "Error." and
likewise in the total for all monies.

I hope this is enough information to go on.

Mike

Allen Browne said:
Mike, you have not given us much to go on, so it's a bit hard to know how to
tell you where to put it.

If you have a text box in the Report Footer section, and it has a Control
Source of:
=Sum([Amount])
you could use:
=Nz(Sum([Amount]), 0)

If you are reading a value from a subreport, you need to check the HasData
property of the report in the subreport control:
=IIf([MySub].[Report].[HasData], Nz([MySub].[Report].[MyTextBox], 0), 0)


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Michael and Fayann said:
I don't think normalizing the table is what I need to do. Where do I put
the Nz() function?

Mike
Click to expand...
Click to expand...
 
That's kinda what I thought and looking back at previous postings, my
suspicions are confirmed. Apparently you haven't followed previous
suggestions to re-organize your table structure.

You can use a left or right join by double-clicking the join line in your
query and selecting the option to select all records from one table. Then in
your report, you can do as I suggested in a previous post, using Nz().

Again, these are the types of problems you will encounter when your tables
aren't normalized.

--
Duane Hookom
MS Access MVP


Michael and Fayann said:
OK Here we go.

I developed a table called tblMoneyCount with the following fields in it:
OrganizationID - AutoNumber
Organization - Text (This would be Spanish Club, Yearbook, etc.)
GeneralOrganization - Text (This would be Elementary or High School)
Date - Date
Pennies - Number
Nichels- Number
(All the way down through Hundreds)

I have another table called tblChecks to enter individual checks received.
The following fields exist in it:
CheckID - AutoNumber
OrganizationID - Number
CheckName - Text (This is to enter who wrote the check)
Amount - Currency

I formed a relationship of 1 to many with OrganizationID

I then created a report from the report wizard using information from both
tables. I have a report header, and Organzxiation ID Header, a Detail
section and a Page Header.

I have the report run off of the Organzaition ID. If there are checks
written, a get a valid report. If the organization had NO CHECKS written to
them, when I run the report, every field is black except the calculated
field for summing the checks. In that calculated field it says "Error." and
likewise in the total for all monies.

I hope this is enough information to go on.

Mike

Allen Browne said:
Mike, you have not given us much to go on, so it's a bit hard to know
Click to expand...
how
to
tell you where to put it.

If you have a text box in the Report Footer section, and it has a Control
Source of:
=Sum([Amount])
you could use:
=Nz(Sum([Amount]), 0)

If you are reading a value from a subreport, you need to check the HasData
property of the report in the subreport control:
=IIf([MySub].[Report].[HasData], Nz([MySub].[Report].[MyTextBox], 0), 0)


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Michael and Fayann said:
I don't think normalizing the table is what I need to do. Where do I put
the Nz() function?

Mike

You can normalize your table structures (just a guess since you didn't
provide any information). If this misses the mark, then use the Nz()
function.

--
Duane Hookom
MS Access MVP


A new scenario...

I have a report that lists currency and checks. I have them total all
catagories, which works fine. However, if I don't have any checks to
report, everything comes up as an error. How can I eliminate the error
when
I don't have any checks to report?

Mike
Click to expand...
Click to expand...
Click to expand...
 
Back
Top