On my subform I have a field in the footer that totals the value of a
field. On the main form I have referenced this field. I'm using this
field in a calculation on my main form. The problem occurs when there
are no records in the subform. The field on the main form that
references the subform total field returns #Error. How can I make
this field show the total when there are records and 0 when there are
no records???
Thank you,
Chris Vettese 9 3136
A stab in the dark here, but try NZ function.
i.e.
=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)
Don't know if it will work, but give it a shot.
Regards,
Fraser. ch**********@yahoo.com (chris vettese) wrote: On my subform I have a field in the footer that totals the value of a field. On the main form I have referenced this field. I'm using this field in a calculation on my main form. The problem occurs when there are no records in the subform. The field on the main form that references the subform total field returns #Error. How can I make this field show the total when there are records and 0 when there are no records???
Thank you, Chris Vettese
Thanks for the response, unfortunatley it did not work. Any other ideas?
Regards,
Chris
"4Fraza" <fr***@clear.net.nz> wrote in message news:<40********@clear.net.nz>... A stab in the dark here, but try NZ function.
i.e. =nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)
Don't know if it will work, but give it a shot.
Regards,
Fraser.
ch**********@yahoo.com (chris vettese) wrote:On my subform I have a field in the footer that totals the value of a field. On the main form I have referenced this field. I'm using this field in a calculation on my main form. The problem occurs when there are no records in the subform. The field on the main form that references the subform total field returns #Error. How can I make this field show the total when there are records and 0 when there are no records???
Thank you, Chris Vettese
On 30 Jan 2004 11:59:42 -0800, chris vettese wrote: =nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)
I believe the refference to the field may be incorrect. Try
=nz(forms![PARENTFORMNAME]![chldFormObjectName].Form![FieldReferenced],0)
--
Mike Storr
veraccess.com
This did not work either, is there a VB solution?
Chris
Mike Storr <st******@sympatico.ca> wrote in message news:<9k*****************************@40tude.net>. .. On 30 Jan 2004 11:59:42 -0800, chris vettese wrote:
=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)
I believe the refference to the field may be incorrect. Try
=nz(forms![PARENTFORMNAME]![chldFormObjectName].Form![FieldReferenced],0)
chris vettese wrote: =nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)
=nz(forms![PARENTFORMNAME]![chldFormObjectName].Form![FieldReferenced],0)
I understand why Nz doesn't help you out--if there are no records, there
is no subform control present to refer to. No amount of Null checking
will help.
Is it possible to lift the calculation out of the subform? Into the main
form control, that is.
--
Bas Cost Budde http://www.heuveltop.nl/BasCB
I can not bring the calculation to the main form. There has to be a
way to do this. What about HasData? Is there a way I can use this
in a form?
Chris
Bas Cost Budde <ba*@heuveltop.org> wrote in message news:<bv**********@news2.solcon.nl>... chris vettese wrote:
=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)=nz(forms![PARENTFORMNAME]![chldFormObjectName].Form![FieldReferenced],0)
I understand why Nz doesn't help you out--if there are no records, there is no subform control present to refer to. No amount of Null checking will help.
Is it possible to lift the calculation out of the subform? Into the main form control, that is.
On 4 Feb 2004 13:23:25 -0800, ch**********@yahoo.com (chris vettese) wrote: I can not bring the calculation to the main form. There has to be a way to do this. What about HasData? Is there a way I can use this in a form?
Chris
Bas Cost Budde <ba*@heuveltop.org> wrote in message news:<bv**********@news2.solcon.nl>... chris vettese wrote:
>>>=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)
>>=nz(forms![PARENTFORMNAME]![chldFormObjectName].Form![FieldReferenced],0)
I understand why Nz doesn't help you out--if there are no records, there is no subform control present to refer to. No amount of Null checking will help.
Is it possible to lift the calculation out of the subform? Into the main form control, that is.
Create a custom function that first checks for the existance of records in the subform. If records exist return the total else return 0.
Something like -
Function fGetTotal()
With Me.frmNameOfSubform.Form.RecordsetClone
IF .RecordCount<>0 Then
fGetTotal=Me.frmNameOfSubform.Form!NameOfTotalsCon trol
Else
fGetTotal=0
End IF
End With
Exit Function
Set the ControlSource of the control on your main form to =fGetTotal()
Wayne Gillespie
Gosford NSW Australia
chris vettese wrote: I can not bring the calculation to the main form. There has to be a way to do this. What about HasData? Is there a way I can use this in a form?
No, it applies only during Print or Preview.
Maybe you can formulate the condition where there will be no records in
the subform, and wrap the formula on the main form control in such a test?
What is your goal? Maybe it helps if I can think myself through the
structure.
--
Bas Cost Budde http://www.heuveltop.nl/BasCB
THANK YOU very much for everyone who posted!!!
Wayne's post provided me with the solution I needed.
Again, I appreciate everyone's time and effort.
Best Regards,
Chris vettese
Wayne Gillespie <be*****@NObestfitsoftwareSPAM.com.au> wrote in message news:<e1********************************@4ax.com>. .. On 4 Feb 2004 13:23:25 -0800, ch**********@yahoo.com (chris vettese) wrote:
I can not bring the calculation to the main form. There has to be a way to do this. What about HasData? Is there a way I can use this in a form?
Chris
Bas Cost Budde <ba*@heuveltop.org> wrote in message news:<bv**********@news2.solcon.nl>... chris vettese wrote:
>>>=nz(forms![PARENTFORMNAME]![chldFormObjectName]![FieldReferenced],0)>=nz(forms![PARENTFORMNAME]![chldFormObjectName].Form![FieldReferenced],0)
I understand why Nz doesn't help you out--if there are no records, there is no subform control present to refer to. No amount of Null checking will help.
Is it possible to lift the calculation out of the subform? Into the main form control, that is.
Create a custom function that first checks for the existance of records in the subform. If records exist return the total else return 0.
Something like -
Function fGetTotal()
With Me.frmNameOfSubform.Form.RecordsetClone IF .RecordCount<>0 Then fGetTotal=Me.frmNameOfSubform.Form!NameOfTotalsCon trol Else fGetTotal=0 End IF End With
Exit Function
Set the ControlSource of the control on your main form to =fGetTotal()
Wayne Gillespie Gosford NSW Australia This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Lyn |
last post by:
Hi,
I am working on a genealogy form. The only table (so far) lists everybody
in the family, one record per person. Each record has an autonum ID.
The parent form (frmMainForm) displays the...
|
by: CSDunn |
last post by:
Hello,
In an Access 2003 Project, I have a main form called
‘frmRCAddendumInputMain' that contains a subform called
‘frmRCAddendumInputSub'. The main form opens upon clicking a button
from...
|
by: Dave Boyd |
last post by:
Hi,
I have two very similar forms each with a subform. The main form gets
a few fields from the user and passes this back to a query that the
subform is bound to. The requery is done when the...
|
by: tdmailbox |
last post by:
I have a form with a child form. In the child form there is a list of
names that can grow quite large. On the parent form I want to display
the first name from the child form. I set up a test...
|
by: gavo |
last post by:
Hello everyone! Using A2K
i have a form(a) with a subform(b) and within the subform there is a
continuous subform(c).
in the subform (b) there is a command button used to call a public...
|
by: Spencer Lee |
last post by:
Hi all,
I really need some helps.
I created a main form("category"), which contains a subform1, "Part".
Inside this subform also has contains a subform2, "Inventory".
The second level of...
|
by: Ecohouse |
last post by:
I have a main form with two subforms. The first subform has the child
link to the main form identity key.
subform1 - Master Field: SK
Child Field: TrainingMasterSK
The second subform has a...
|
by: meLady |
last post by:
Hello,
I have a form called feedback with a subform called feedback Options.
I did some counting calculation in master form "feedback" at the bottom page.
the calculation is about count the...
|
by: Bob Alston |
last post by:
I have a system where many subforms are used. Often the size of the
subform had to be larger than could be displayed without scrolling.
I set the height of the subform to the typical height...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |