Need a $0.00 value where a query returns no records to my form. 1

[Tekfly]

I have a form and subform each based on its own query. The results of the two queries (e.g. "Total A" and "Total B&quotmust be added together in control "TotalC". I've created a macro that calculates the total with "SetValue","[TotalC]",argument"[TotalA]+[TotalB]".

This works fine when the both the form and subform returns actual values, but obviously gives me a #Error message when there are no underlying records entered for either the subform or main form.

My attempts at conditional expressions in the macro to solve this (e.g. If "IsNull([TotalA]) then SetValue ....0 .." and If "[TotalA]=" "" then...bla..bla..) doesn't work, presumably because even to evaluate a null value, there must be at least a record to evaluate!

I know diddly squat about VB and need to use some sort of conditional expression for a macro to return a $0.00 value if there are no records to base the Sum calculation on.
Can one use "Nz" in the control source line of a control's property box to force a $0.00 value. If so, how(I've tried Access Help and failed), and if not...what now?

 

[thornmastr]

You should be able to do this in the query from the design grid as, RealTotA:iif(nz(totalA)=0,0,totalA)

or

RealTotA:iif(len(str(totalA)) = 0,0,totalA)
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Tekfly]

Thanks Robert

No joy though. All totals are calculated in the FORM based on records supplied by the queries. i.e. I have no "Totals" fields in the query itself because records are drawn together through two separate queries from separate tables to one form with a subform.

Is there no way I can use nz in the form controls or maybe some expression I can use in my macro (which sets the Sum value for the form), that uses a condition which effectively i.d. a situation where my subform "has-no-value". e.g. if the subform(or a particular control therein)has no value i.e. no ENTRY(not "null&quot. Then the macro can go "SetValue" "SubTotalX" "0"...etc.

I know I'm looking for a miracle without writing VB code, but I'm sure it can be done.

Thanks so far.
KC

 

[67peshawar294]

tekfly,
Create another txtbox, in the control source;
=iif(isnull(totalc]),0,[totalc])
In the format section;
$#,#.00
The original [totalc]textbox set the visible property to "no"
jp

 

[thornmastr]

I have not used macros in years and try to discourage people from using them. There is no real error control or debug capabilities, etc. But, I think I solved your problem using just the form. Assume a form having three controls, the first mapped to value1 of some record and the second mapped to another value of that record and you simply want to add the first two values and display the results in the third control called totvalue.

The kicker here is that totvalue is not bound to a table. It is there simply to display a total. Your tables should never reflect any totals of anything. Rule of thumb: What can be computed is not saved…..EVER. So now that I have ingrained that philosophy into you brain forever. Simply make the control source of your total field the following. it will work. Guaranteed.

=nz([value1])+nz([value2])

and yes you do need the equals sign at the beginning of this string. if you are bewildered, don’t understand it, don’t believe it, post your email address, tell me your version of Access, and I will mail you the demo to prove it.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com