Replacing 0's with -'s in Detail 1

[Noel2]

I have a report based on a query that I need the report to look at each record and change each field that has a value as 0 to change it's value to "-". I have tried to set up an IIF statement as below as the source of each of the fields and have come away with an error each time:

=IIF([hrs_cmplt]=0,"-",[hrs_cmplt])

I tried to change it at the query level and when I do it works perfectly. But, when I run the report, it tells me that I have an invalid data type.

Maybe this is something for recordsetting? I am not familiar with that but do truly want to learn.

Please let me know your ideas.

Thanks,
Noel

 

[CosmoKramer]

Noel,

Add a new unbound text box to the report and use your IIf statement. That should work.....

 

[Noel2]

Sorry for wasting your time... I guess I tried to make a mountain out of a mole hill. I just don't know why I couldn't change the recordsource myself on the original fields. Does this have to do with the naming conventions? Please let me know. I appreciate the assistance.

 

[CosmoKramer]

It's not a waste of time.....You have no idea how long it took me to understand this.

A bound control is just as the name implies, its bound to the table where its stored. That's why you can alter the data stored within, but not the control source. By using an unbound control, the value exists only for display purposes on the report, you don't actually want to store it. That's why you were getting the "invalid data type" error; trying to store non-numeric data in a numeric type field.....

 

[Noel2]

Thank you so much for the explanation. Sometimes things are just not concrete for me and this helps me to clear up the muddy waters.

Thank you,
Noel

 

[JohnLowell]

You can also format the field to replace the 0 with "-".

Format the text box to be:
#,##0;-#,##0;"-";"Null"

(Positive value; Negative Value; Zero Value; Null Value)

Hope this helps

 

[Noel2]

Thank you! this will help as well.
Noel