Control source calc error 2

[cimoli]

I am getting #size! in a form field. In the form field control source, I have,
=IIf(Nz([BowlingHistory])>0,Nz([BowlingHistory]),0)

BowlingHistory is a number. The number is typically 0 to 800 but can be any number.

I thought my formula in the form control source would place a zero whenever the BowlingHistory figure were 0
for the beginner who had not bowled yet.

The #Size! error shows on the form field. I would like it to look nice and just show 0 .

Can you help? Thanks.

 

[dhookom]

Why not just use:
=Nz([BowlingHistory],0)

If as you suggest the values are 0-800 you should be able to use:
=BowlingHistory

IMO never use NZ() without 2 arguments.


Duane
Hook'D on Access
MS Access MVP

 

[cimoli]

Thanks Duane. I tried both of your methods. I last tried the =[bowlingHistory]. (the brackets came on automatically).
Now I get an error message in the field as #Error

I just put in a "0" or zero default value in the table for the bowlingHistory field but it did not change anything.
It is a long integer if that matters?
I still see #Error on the form when i am on any record. All records say #Error.

 

[dhookom]

What happens if you remove the =?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I last tried the =[bowlingHistory]. (the brackets came on automatically).
Now I get an error message in the field as #Error

To me that would suggest that BowlingHistory is not a field in the control source of the form. Any chance it is a misspelling?

Easy check. Go into vba in any method of the form. Type in Me.bow... and see if intellisense comes up and show this as an option.

 

[cimoli]

Duane - You are correct. In my earlier message, I said that I added a default in the table for the default value to be 0 or zero.
Just now, I put the control source field back to plain BowlingHistory. Bingo. It works good now.
If a person had no history, I get a 0 or zero instead of the dreaded #Error things.

Thanks MajP too. You made me look back at the table and hence figure out that the earlier Zero solved it.

Thanks guys.

 

[dhookom]

cimoli,

If your control was named BowlingHistory and the control source was anything other than BowlingHistory, you will get an error. By default, the control will have the same name as the bound field. This can be an issue if you change the control source to anything beginning with "=".

Duane
Hook'D on Access
MS Access MVP