Equation returns #Size!

Question

I am using the equation below as the Control Source for a txtbox, to let the user know how many records are left to be reviewed. When the last record has been reviewed the equation returns #Size!. Is there any way t get it to return 0?

=Nz([frmNoShowListSub]![txtTotalCount],0)

Answer 1

Ok . .

this is actually a very frustrating issue.

I finally got a zero by using a dcount on the underlying query rather than counting the records on the form.

In my test form I have a subform that list records based on criteria in fields on the main form

In the subform I saved the underlying query as a named query . . qfs_LIST

In the main form I placed a control CNT_RECS with control source of

=DCount("[JPO_KEY]","qfs_LIST")

when this calcs the total it displays 0 if there are no records in the subform dataset.

the trick is to trigger a requery CNT_RECS only when the record count changes

I'm still looking for better suggestions . . .

cheers

PaulG

Answer 2

OK im stumped

I cant create the #size error . . but i also cant get a 0 (zero) to show in the total field when count an empty dataset

Any other clues . . ????

cheers

PaulG

Answer 3

hi

how do you calc the  - txtTotalCount . .  try using the IsError option in the calc

something like . . and i havent tested this

=IIf(IsError(Count([SomeField]),0,Count([SomeField])

If this does work OK then your original formula. wont need the NZ test

cheers

PaulG

Answer 4

Thanks but it returns an error  The expression you entered has a function containing the wrong number of arguments

Answer 5

Hey Ordnance1 -

I can't find any information whatsoever about the #Size error or what might cause it. This seems to be another Microsoft undocumented feature that no one at Microsoft will ever see or fix (or add to any documentation).

To work around it, you can try:

=IIf(IsError(Nz([frmNoShowListSub]![txtTotalCount],0), 0 , Nz([frmNoShowListSub]![txtTotalCount],0))

When it finds your error, it will display a 0.

Hope that helps.