want #type! to show 0

[tareyj8569]

Hey all,

is there a way to say =Field1*field2 if error show 0
Hope below might help visualize

Field 1	Field 2	=Field1*field2
100%	20	20
80%	10	8
#Error (because of no data) <this is fine	20	#type!

Under that 3rd field I try and add the 3 up but when I do I get #type!

they each have their own textbox just wanted to try and simplify on here all with same names.

I tried =Sum(if(field1*field2,0)) but that did not fly

Thanks for your time and help.

[June7]

Need to deal with the #Error. What is this calc and why is there an error?

If() is not a valid function. There is an IIf() function but regardless, that won't work in your expression either.

[tareyj8569]

oops I meant IIf but yea.

The error I have is field1 that is pulling from a Subform but sometimes there is no data in the Subform so it throws an #Error

Field 1
=[ABC_Subform].[Form]![Text101]

Field 2 is an unbound box with a number Ex: Text102 and is 20 in the field

3rd field calculates the 2
=[Field1]*[Field2]

If there is data in field 1 because subform has data field 3 works just fine
but if there is no data in subform it throws up the #Error in field 3

[June7]

Still don't understand your 'fields'. Are you pulling values from 3 subforms? Are these really 3 sets of UNBOUND textboxes - not 1 set of BOUND textboxes and 3 records?

[tareyj8569]

Yes sorry 3 unbound fields

First field is getting % from subform
2nd field is an unbound number
3rd field is first field*2nd field

so if field 1 is 100% and field 2 is 8 than field 3 would be 8


Field 1
Unbound =[ABC_Subform].[Form]![Text101]

Field 2
Unbound = 8

Field 3
Unbound =[Field1]*[field2] This would equal 8

[June7]

You mean unbound CONTROLS (textboxes)?

Aggregate functions cannot reference controls, only fields. So the Sum() won't work.

I still don't understand the 3 rows of data.

If you want to provide db for analysis, follow instructions at bottom of my post.

[tareyj8569]

Sorry need to get my naming convention down better.

3 Unbound Controls which are textboxes

the 1st textbox on mainform
is pulling from a subform field that is being calculated in a textbox but sometimes that subform has no data so there is nothing to pull

Subform textbox which is calculating a field on subform
=Sum(IIf([Pass or Fail]="Pass",1,0))/Sum(IIf([Pass or Fail],1,0))

[June7]

I am still lost. Why does your example show 9 pieces of data? If there are only 3 UNBOUND controls, how can there be 3 rows of data with different values? I think you better provide db.

[tareyj8569]

OH

I should not have shown all 9 fields just the bottom line with the error. I included the other 2 which use the same calculation as I provided to show how it would look if it worked right.

There is actually many subforms and so on but I wanted to include how it should look if it worked right.

=Sum(IIf([Pass or Fail]="Pass",1,0))/Sum(IIf([Pass or Fail],1,0)) is used in all 3 subforms to get my total which then the percentages textboxes each pull from a different subform

The first 2 rows there is data in the first 2 subforms so it shows like below
but the 3rd row there is no data so the 3rd subform does not show

Percentages	Number	=Field1*field2
[Textbox1]=100% Textbox1,2,3 pull from subform1	[Textbox2]=20	[Textbox3]=20
[[Textbox4]80% textbox4,5,6 pull from subform2	[Textbox5]=10	[Textbox6]=8
#Error (because of no data) <this is fine textbox7,8,9 pull from subform3	[Textbox8]=20	[Textbox9]#type!

The above is 9 textboxes all unbound

I apologize again I am not the best at describing my issue so I try to include probably too much.

If you still need a DB, It will be a few for I have to recreate that section because this one here wont show unless on local network.

[June7]

Bing: Access subform no record

https://social.msdn.microsoft.com/Fo...orum=accessdev

[tareyj8569]

June,
Again you have solved one of my problems, there was some VBA code I got through the link you provided.
Thank you for your time that you spent helping me with this issue.