Getting rid of zeros

[ceb39]

Give me you email address and I will send you the .mdb file, it is only about .23MBs, and I can point out the table, query, and report I am working with.

Thanks

[RuralGuy]

Rural Guy at Wild Blue dot Net should do it.

[RuralGuy]

Got the file. Which report are we talking about?

[ceb39]

Table is tbResearchData

Query is qryTest#02

Report is Sources By First Alpha Character. I have been inputtin S here as that report display some records where the problem is seen.

The Form I have been using is frmDataEntry.

Need anyother info let me know.

Thanks

Chuck

[RuralGuy]

For those following the thread, we resolved this off line. The solution I suggested works as advertised.

[thhui]

The circular reference may happen if you are not so careful to deal with the default setting of Access.

If you use the access report's wizard to create a report, then the report's control name would be the same as that of the corresponding field name.
Hence, make sure that the fieldcontrol's name is not the same as that of any variables used in the formula of that fieldcontrol.
Otherwise, circular reference would be created if they are the same.

Solution:-
If the report's fieldcontrol's formula is not that of datasource's field name. Rename it to another unique name first.

For example,
If [score] is the original field name and the report fieldcontrol's name.
You want to change its formula to
=iif([score]=0, "", [score]).
Then, you change the fieldcontrol's name first to score_f.
Press OK.
Then change the field's controlsource to
=iif([score]=0, "", [score])

Now, it would be OK and no circular reference would occur.

[ceb39]

Thank you for the Access lesson. Will print this out and keep it handy, plus will review this with other reports to see if this problem exists there.

Thank you for all your help.

Chuck

[RuralGuy]

You are most welcome Chuck.

[ceb39]

Finally got back to the Access report I have been buggin you about.

The report you modified, "Films/Fiche To Be Reviewed" works fine. It has a column in that report titled Microfiche Number, the same as the other report, the one sorted by an alpha character.

The name of the MicroFiche field in properties dialog box shows it to be titled "tstrdMicroFicheNumber". Thought the "tst" should be "txt". Changed it and the report ran okay with "txt" in front. Changed it back to "tst" and it ran fine that way.

The Control Source in the Films/Fiche report is

=IIf([txtrdMicroFicheNumber]>0,[txtrdMicroFicheNumber],"")

I copied and pasted that Control Name into the Control Source property for the report sorted on the alpha character. Ran it and an error was produced. Then I remembered that the name was different so changed the name to "tstrdMicroFicheNumber" also. Ran the report again and that damn #Error appeared where there should be no MicroFiche Numbers.

I have opened both reports side by side and looked at the properties of each. For the life of me I can see why one report runs correctly and the other does not. Both are looking at the same field, "rdMicroFicheNumber". Only difference is each report comes from a different query to get the data to populate the report.

If you still have the file the report title is "Sources By First Alpha Character."

Chuck

[RuralGuy]

I'm a bit lost Chuck. I looked at the "Sources By First Alpha Character" report and the txtrdMicroFicheNumber control has a ControlSource of =IIf([rdMicroFicheNumber]>0,[rdMicroFicheNumber],"") and it supresses the "0" as expected.

[ceb39]

Well I am not certain what I was doing, but went back and copied and pasted the code you had for the "Films/Fiche" report into the Alpha report and it now works fine.

In both reports I now have the same Name "MicroFicheNumber" and Control Source "=IIf([rdMicroFicheNumber]>0,[rdMicroFicheNumber],"")".

I tried the Name "txtrdMicroFicheNumber" for both reports and it appeared it did not matter which name I used.

If I would have used a name such as rdMicroFicheNumber (which is the name of this field in the table) could that possible cause a problem later on, like in the circular reference you discussed earlier?

Will now go through the rest of the reports and implement this code where ever Micro Fiche Numbers appear.

Again thanks for all your help. Learning more about the ins and outs of Access from you than any book I have read.

Chuck

[RuralGuy]

I tried the Name "txtrdMicroFicheNumber" for both reports and it appeared it did not matter which name I used.

If I would have used a name such as rdMicroFicheNumber (which is the name of this field in the table) could that possible cause a problem later on, like in the circular reference you discussed earlier?

AFAIK, nothing is referencing the Control Name so you can name it anything you want. IF you name it the same as the field it displays (the default name) then YES, Access can get confused. Glad I could help.