HOw do I declare this function? i'm trying to create the following

[Brook]

HI all,

i'm trying to declare this function to create the following calculation
within a report:

=sum(nulltozero(amount))

Thanks in advance..

Brook


Public Function NullToZero( TheValue as Variant)

'This function converts Null to Zero

'It also converts Non Existing Data to Zero

On Error Goto NullToZero_Err



If ISNull(TheValue) then

NullToZero = 0

Else

NullToZero = TheValue

End if

Exit Function



NullToZero_Err:

'This function would only generate an error

'if the data in TheValue doesn't exist at all.

NullToZero = 0

Exit Function

End Function

 

[Ken Snell \(MVP\)]

Any particular reason you want to duplicate an already existing VBA
function, which is called Nz, which does what you seek?

=Sum(Nz([Amount],0))

 

[Brook]

Hello,

Well, this is what i'm trying to accomplish:

I have my report that has my ProjectHrs & Billing amount

2 subreports:
Supplies Cost
Payments Made


Within my main report, I have my totals for Project Billing, Total Supplies
and Total Due

however if there are no payments or no supplies these totals are showing
#error

and i'm not geting my final total due
Here are my calculations in my main form


Project Hr
Total:=Sum([montot]+[tuestot]+[wedstot]+[thurstot]+[fridtot]+[sattot]+[suntot])
Supplies Total:=qrysuppliessubform.Report!sumsupplies
Payments Total:=(qrypaymentsubform.Report!sumpayments)
Balance Due:=[totalhrs]+[totalsupplies]-[totalpayments]


hope this is clear...thanks,

Brook

Any particular reason you want to duplicate an already existing VBA
function, which is called Nz, which does what you seek?

=Sum(Nz([Amount],0))

--

Ken Snell
<MS ACCESS MVP>

HI all,

i'm trying to declare this function to create the following calculation
within a report:

=sum(nulltozero(amount))

Thanks in advance..

Brook


Public Function NullToZero( TheValue as Variant)

'This function converts Null to Zero

'It also converts Non Existing Data to Zero

On Error Goto NullToZero_Err



If ISNull(TheValue) then

NullToZero = 0

Else

NullToZero = TheValue

End if

Exit Function



NullToZero_Err:

'This function would only generate an error

'if the data in TheValue doesn't exist at all.

NullToZero = 0

Exit Function

End Function

 

[Ken Snell \(MVP\)]

Ok, so why not just the Nz function, which already exists in ACCESS, to do
what you want? No need to declare a public function or write your own
function's code.
--

Ken Snell
<MS ACCESS MVP>

Hello,

Well, this is what i'm trying to accomplish:

I have my report that has my ProjectHrs & Billing amount

2 subreports:
Supplies Cost
Payments Made


Within my main report, I have my totals for Project Billing, Total
Supplies
and Total Due

however if there are no payments or no supplies these totals are showing
#error

and i'm not geting my final total due
Here are my calculations in my main form


Project Hr
Total:=Sum([montot]+[tuestot]+[wedstot]+[thurstot]+[fridtot]+[sattot]+[suntot])
Supplies Total:=qrysuppliessubform.Report!sumsupplies
Payments Total:=(qrypaymentsubform.Report!sumpayments)
Balance Due:=[totalhrs]+[totalsupplies]-[totalpayments]


hope this is clear...thanks,

Brook

Any particular reason you want to duplicate an already existing VBA
function, which is called Nz, which does what you seek?

=Sum(Nz([Amount],0))

--

Ken Snell
<MS ACCESS MVP>

HI all,

i'm trying to declare this function to create the following calculation
within a report:

=sum(nulltozero(amount))

Thanks in advance..

Brook


Public Function NullToZero( TheValue as Variant)

'This function converts Null to Zero

'It also converts Non Existing Data to Zero

On Error Goto NullToZero_Err



If ISNull(TheValue) then

NullToZero = 0

Else

NullToZero = TheValue

End if

Exit Function



NullToZero_Err:

'This function would only generate an error

'if the data in TheValue doesn't exist at all.

NullToZero = 0

Exit Function

End Function

 

[Brook]

Hello, I understand that now...

I tried to add the NZ function on the subreport sum, but am still getting
the #error on my main form.

Any suggestions...

Brook

Ok, so why not just the Nz function, which already exists in ACCESS, to do
what you want? No need to declare a public function or write your own
function's code.
--

Ken Snell
<MS ACCESS MVP>

Hello,

Well, this is what i'm trying to accomplish:

I have my report that has my ProjectHrs & Billing amount

2 subreports:
Supplies Cost
Payments Made


Within my main report, I have my totals for Project Billing, Total
Supplies
and Total Due

however if there are no payments or no supplies these totals are showing
#error

and i'm not geting my final total due
Here are my calculations in my main form


Project Hr
Total:=Sum([montot]+[tuestot]+[wedstot]+[thurstot]+[fridtot]+[sattot]+[suntot])
Supplies Total:=qrysuppliessubform.Report!sumsupplies
Payments Total:=(qrypaymentsubform.Report!sumpayments)
Balance Due:=[totalhrs]+[totalsupplies]-[totalpayments]


hope this is clear...thanks,

Brook

Any particular reason you want to duplicate an already existing VBA
function, which is called Nz, which does what you seek?

=Sum(Nz([Amount],0))

--

Ken Snell
<MS ACCESS MVP>

HI all,

i'm trying to declare this function to create the following calculation
within a report:

=sum(nulltozero(amount))

Thanks in advance..

Brook


Public Function NullToZero( TheValue as Variant)

'This function converts Null to Zero

'It also converts Non Existing Data to Zero

On Error Goto NullToZero_Err



If ISNull(TheValue) then

NullToZero = 0

Else

NullToZero = TheValue

End if

Exit Function



NullToZero_Err:

'This function would only generate an error

'if the data in TheValue doesn't exist at all.

NullToZero = 0

Exit Function

End Function

 

[Ken Snell \(MVP\)]

Use the HasData property of the subreport to test if there is a value to
use:

Supplies
Total:=IIf(qrysuppliessubform.Report.HasData=True,qrysuppliessubform.Report!sumsupplies,0)
Payments
Total:=IIf(qrypaymentsubform.Report.HasData=True,qrypaymentsubform.Report!sumpayments,0)


--

Ken Snell
<MS ACCESS MVP>

Hello, I understand that now...

I tried to add the NZ function on the subreport sum, but am still getting
the #error on my main form.

Any suggestions...

Brook

Ok, so why not just the Nz function, which already exists in ACCESS, to
do
what you want? No need to declare a public function or write your own
function's code.
--

Ken Snell
<MS ACCESS MVP>

Hello,

Well, this is what i'm trying to accomplish:

I have my report that has my ProjectHrs & Billing amount

2 subreports:
Supplies Cost
Payments Made


Within my main report, I have my totals for Project Billing, Total
Supplies
and Total Due

however if there are no payments or no supplies these totals are
showing
#error

and i'm not geting my final total due
Here are my calculations in my main form


Project Hr
Total:=Sum([montot]+[tuestot]+[wedstot]+[thurstot]+[fridtot]+[sattot]+[suntot])
Supplies Total:=qrysuppliessubform.Report!sumsupplies
Payments Total:=(qrypaymentsubform.Report!sumpayments)
Balance Due:=[totalhrs]+[totalsupplies]-[totalpayments]


hope this is clear...thanks,

Brook



:

Any particular reason you want to duplicate an already existing VBA
function, which is called Nz, which does what you seek?

=Sum(Nz([Amount],0))

--

Ken Snell
<MS ACCESS MVP>

HI all,

i'm trying to declare this function to create the following
calculation
within a report:

=sum(nulltozero(amount))

Thanks in advance..

Brook


Public Function NullToZero( TheValue as Variant)

'This function converts Null to Zero

'It also converts Non Existing Data to Zero

On Error Goto NullToZero_Err



If ISNull(TheValue) then

NullToZero = 0

Else

NullToZero = TheValue

End if

Exit Function



NullToZero_Err:

'This function would only generate an error

'if the data in TheValue doesn't exist at all.

NullToZero = 0

Exit Function

End Function

 

[Brook]

Hello Ken,

Thank you so much.... that worked perfectly!

Have a wonderful day!

Brook

Use the HasData property of the subreport to test if there is a value to
use:

Supplies
Total:=IIf(qrysuppliessubform.Report.HasData=True,qrysuppliessubform.Report!sumsupplies,0)
Payments
Total:=IIf(qrypaymentsubform.Report.HasData=True,qrypaymentsubform.Report!sumpayments,0)


--

Ken Snell
<MS ACCESS MVP>

Hello, I understand that now...

I tried to add the NZ function on the subreport sum, but am still getting
the #error on my main form.

Any suggestions...

Brook

Ok, so why not just the Nz function, which already exists in ACCESS, to
do
what you want? No need to declare a public function or write your own
function's code.
--

Ken Snell
<MS ACCESS MVP>


Hello,

Well, this is what i'm trying to accomplish:

I have my report that has my ProjectHrs & Billing amount

2 subreports:
Supplies Cost
Payments Made


Within my main report, I have my totals for Project Billing, Total
Supplies
and Total Due

however if there are no payments or no supplies these totals are
showing
#error

and i'm not geting my final total due
Here are my calculations in my main form


Project Hr
Total:=Sum([montot]+[tuestot]+[wedstot]+[thurstot]+[fridtot]+[sattot]+[suntot])
Supplies Total:=qrysuppliessubform.Report!sumsupplies
Payments Total:=(qrypaymentsubform.Report!sumpayments)
Balance Due:=[totalhrs]+[totalsupplies]-[totalpayments]


hope this is clear...thanks,

Brook



:

Any particular reason you want to duplicate an already existing VBA
function, which is called Nz, which does what you seek?

=Sum(Nz([Amount],0))

--

Ken Snell
<MS ACCESS MVP>

HI all,

i'm trying to declare this function to create the following
calculation
within a report:

=sum(nulltozero(amount))

Thanks in advance..

Brook


Public Function NullToZero( TheValue as Variant)

'This function converts Null to Zero

'It also converts Non Existing Data to Zero

On Error Goto NullToZero_Err



If ISNull(TheValue) then

NullToZero = 0

Else

NullToZero = TheValue

End if

Exit Function



NullToZero_Err:

'This function would only generate an error

'if the data in TheValue doesn't exist at all.

NullToZero = 0

Exit Function

End Function

 

[Ken Snell \(MVP\)]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Hello Ken,

Thank you so much.... that worked perfectly!

Have a wonderful day!

Brook

Use the HasData property of the subreport to test if there is a value to
use:

Supplies
Total:=IIf(qrysuppliessubform.Report.HasData=True,qrysuppliessubform.Report!sumsupplies,0)
Payments
Total:=IIf(qrypaymentsubform.Report.HasData=True,qrypaymentsubform.Report!sumpayments,0)


--

Ken Snell
<MS ACCESS MVP>

Hello, I understand that now...

I tried to add the NZ function on the subreport sum, but am still
getting
the #error on my main form.

Any suggestions...

Brook

:

Ok, so why not just the Nz function, which already exists in ACCESS,
to
do
what you want? No need to declare a public function or write your own
function's code.
--

Ken Snell
<MS ACCESS MVP>


Hello,

Well, this is what i'm trying to accomplish:

I have my report that has my ProjectHrs & Billing amount

2 subreports:
Supplies Cost
Payments Made


Within my main report, I have my totals for Project Billing, Total
Supplies
and Total Due

however if there are no payments or no supplies these totals are
showing
#error

and i'm not geting my final total due
Here are my calculations in my main form


Project Hr
Total:=Sum([montot]+[tuestot]+[wedstot]+[thurstot]+[fridtot]+[sattot]+[suntot])
Supplies Total:=qrysuppliessubform.Report!sumsupplies
Payments Total:=(qrypaymentsubform.Report!sumpayments)
Balance Due:=[totalhrs]+[totalsupplies]-[totalpayments]


hope this is clear...thanks,

Brook



:

Any particular reason you want to duplicate an already existing VBA
function, which is called Nz, which does what you seek?

=Sum(Nz([Amount],0))

--

Ken Snell
<MS ACCESS MVP>

HI all,

i'm trying to declare this function to create the following
calculation
within a report:

=sum(nulltozero(amount))

Thanks in advance..

Brook


Public Function NullToZero( TheValue as Variant)

'This function converts Null to Zero

'It also converts Non Existing Data to Zero

On Error Goto NullToZero_Err



If ISNull(TheValue) then

NullToZero = 0

Else

NullToZero = TheValue

End if

Exit Function



NullToZero_Err:

'This function would only generate an error

'if the data in TheValue doesn't exist at all.

NullToZero = 0

Exit Function

End Function