Getting rid of zeros

[ceb39]

I have developed a report which is displaying the data I want. In that report are two fields which are number fields. Some times these fields contain a six or seven digit numbers, some times they are blank, and at times they contain a zero in one or both of these fields in the table.



What I would like is to print a report, but if one or both of these fields do contain a zero, not print it, just leave it blank on the report.

Have tried various IF THEN ELSE statements and others in both the report and query to get rid of the zeros and replace them with a blank, so far nothing seems to work.

This can not be all tha difficult to program, but I have gone through all the Access books I and come up with blank.

Anyone have any ideas.


Chuck

[RuralGuy]

Try a control source of:
=IIF Val(Nz([YourField],0)) > 0 Then [YourField], "")

[ceb39]

RG

Have inserted the IIF function you described in the report and query, but have not been able to make it work. I have gone through the books I have trying to get a good explanation of the NZ function, but have found nothing really good although the Access VBA 2002 book I have sort of explains it.

Should the > sign be replaced with a equal sign, since I am only looking for zeros in the that particular field?

Thanks

Chuck

[RuralGuy]

It looks like I left out an open paren and a comma:
=IIF( Val(Nz([YourField],0)) > 0 , [YourField], "")
...see if this works. You need to use the names of YourField of course.

[ceb39]

Have inserted the new IIF code into the query, but get an error message which states "The expression is typed incrrectly, or it is too complex to be evaluated." I cut and pasted your code under the field in the query and in a criteria row, then cut and pasted the name of the field to replace the "Your Field" text.

Have also gone into the SQL code and inserted the following below the four lines of code which drive the query to see if it would work.

UPDATE tblResearchData.rdMicroFicheNumber
SET tblResearchData.rdMicroFicheNumber = ""
WHERE (( tblResearchData.rdMicroFicheNumber) = 0) ;

But this is coming back with a syntax error.

Will continue to work on this, as there has to be a way to get rid of those zeros and replace them with NULL.

Thanks a bunch

Chuck

[RuralGuy]

OK, now you have me real confused. The code I posted was to be the ControlSource of a TextBox on a report. You have posted an action query which can *not* be the RecordSource of a report.

[ceb39]

Posted your code into the Record Source of the box which is supposed to display a Microfiche Number. Changed the two fields to rdMicroFicheNumber and ran the report. The following was displayed in the report under the Micro Fiche column, "#Error" for each entry where a zero was being displayed prior to this change.

In the Format area for this field I changed Can Grow to Yes, just in case there was more to the error message, there was no difference with the report output, still seeing just "#Error" only for each entry in the report.

When I am in the Design View of the report (after running the report) there is a little box to the left of the box for rdMicroFicheNumber with an explanation icon it it. Clicked on this and it provide some information on errors, but nothing which made sense to me. When I clicked on the icon and then clicked Help With This Error a long explanation about Circular Regerences was displayed. Copied and pasted what I saw below.

Circular reference

The Control Source property of the selected control is an expression that includes a reference to the control. Circular reference will cause the expression to fail.
You can do one or more of the following:

• To edit the control’s Name property in the property sheet, click Edit Control Name on the Error Checking Options menu.

Note If Name AutoCorrect is turned on, changing the name of the control will automatically update the control’s Control Source property. The circular reference error will still exist, so to remove the error, edit the control’s Control Source property to reverse the automatic update.

• To edit the Control Source property of the control, click Edit the Control’s Control Source Property on the Error Checking Options menu.
• To ignore this error and hide the Error Checking Options button, click Ignore Error on the Error Checking Options menu.
• To turn off error checking for circular-reference errors, click Error Checking Options on the Error Checking Options menu. On the Error Checking tab, under Form/Report Design Rules, clear the Invalid control properties check box. Note You can also access the Error Checking tab by clicking Options on the Tools menu, and then clicking the Error Checking tab.

I don't not have the smarts to determine if there is a circular reference going here or not.

Chuck

[ceb39]

Just a thought. The field for the Microfiche Number data is a numerica field. Can you actually place a NULL value into a field that has already been declared a numeric field and which contains a numeric value such as zero?

Chuck

[RuralGuy]

Change the name of the TextBox to txtMicroFicheNumber and see what happens.

[RuralGuy]

Since it is a numeric field you can just use:
=IIF( [rdMicroFicheNumber] > 0 , [rdMicroFicheNumber], "")
We are not changing the field, only changing the control that displays the field.

[ceb39]

So for the answer delay, but busy over the weekend.

Will give your suggestions a try and see what happens.

Thanks

Chuck

[ceb39]

Put in the following code for the Control Source

=IIF( [rdMicroFicheNumber] > 0 , [rdMicroFicheNumber], "")

and then changed the Name

from rdMicroFicheNumber

to txtMicroFicheNumber

Still received the

#Error message when the report printed out under the Micro Fiche Number column.

Thanks

Chuck

[RuralGuy]

I think you need to post a sample of your db that displays this symptom. We are going around in circles here.

[ceb39]

I have sort of developed a work around to this problem, although it is not the best solution.

On my form I have a SAVE RECORD button. I have written some code so when the SAVE button is clicked the Micro Film field and Book fields are checked. If there is a value in either field then I set Micro Fiche to "". It is working and does prove you can place "" into the numberic field of Micro Fiche Number. Now to determine how to do it when the report is run so all the zeros do not shown.

Thanks

Chuck

[RuralGuy]

Can't you post a db with just the report and the query it runs from?