sum unbound field in continuous form

[beginner_access]

Hi,

I have a continuous form with a number of unbound fields and bound fields. However, I would like to sum an unbound fields (Text28). Text28 is an unbound calculated field based on another calculated unbound field (Text22). I would like to sum Text28 in the footer or somewhere in the form.

Here are the codes for my field:

Text28 = iff([Text22>0], [Text22], [Text20]  --- This works

Text29 =sum(iff([Text22>0], [Text22], [Text20]) --- this is not working

and it did not work.

any advice will be appreciated.
Thanks

 

[Galaxiom]

You are trying to Sum controls but the Sum function applies to fields.

Fields are in the form's recordset.

You need to derive the field in the RecordSource query rather than using a calculated control. Then you will be able to Sum it.

 

[beginner_access]

You are trying to Sum controls but the Sum function applies to fields.

Fields are in the form's recordset.

You need to derive the field in the RecordSource query rather than using a calculated control. Then you will be able to Sum it.

Sorry I am not sure how to do this as I am fairly new to Access. Would you be able to help me with this?

 

[Galaxiom]

Move the IIF to the query.

Use something like this in the field cell of the query designer:

field28:IIF([field22]>0,[field22],[field20])

(Obviously you would change the names to the fieldnames that apply to your table.)

Then you can sum [field28] on the form.