null

[Guest]

I have this in an unbound field
=Sum(Nz([Credit Card],0))

If the fields that are null are sumed up and there is nothing there then I
want by result to be 0.00. Right now it dosent show anything.
Can someone help me?
Thanks
Chey

 

[Guest]

Hi Chey,
Assuming the control [Credit Card] it already a summed value...

=IIf(IsNull([Credit Card]),0,[Credit Card])
and format your control as a 2 decimal place number/currency/fixed

 

[Guest]

It says I have invalid syntax. Am I missing an ) somewhere?

Try =Sum(NZ([Credit Card],0)00.0) or take a peek at this link

http://www.techonthenet.com/access/functions/advanced/nz.php

I have this in an unbound field
=Sum(Nz([Credit Card],0))

If the fields that are null are sumed up and there is nothing there then I
want by result to be 0.00. Right now it dosent show anything.
Can someone help me?
Thanks
Chey

 

[Al Campagna]

Chey,
Have you tried formatting the calculated field for #.00;-#.00;0.00;0.00
Usually though the standard currency dispolay for a 0 value would be .00 (you're
call...)

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

I cant see that you are but the NZ function allows you to return a value
other than Null, the syntax is

Nz ( variant, [ value_if_null ] )

So Variable = Sum(NZ[Control],0.00) should give you 0.00 if null. But, I
have just noticed I missed out a comma just before the 0.00, try that but
also take a look at the link I posted, it explains the NZ function better
than I can.

HTH, Mike

It says I have invalid syntax. Am I missing an ) somewhere?

Try =Sum(NZ([Credit Card],0)00.0) or take a peek at this link

http://www.techonthenet.com/access/functions/advanced/nz.php

I have this in an unbound field
=Sum(Nz([Credit Card],0))

If the fields that are null are sumed up and there is nothing there then I
want by result to be 0.00. Right now it dosent show anything.
Can someone help me?
Thanks
Chey

 

[Guest]

Try =Sum(NZ([Credit Card],0)00.0) or take a peek at this link

http://www.techonthenet.com/access/functions/advanced/nz.php

 

[Guest]

After reading your link I wonder if Credit Card was generated this way
Credit Card: IIf([CTA]=-1,Null,[Credit_Card_Amount])
then in the footer I put
=Sum(NZ([Credit Card],0)00.0)
then =Sum(NZ([Credit Card],0),00.0)
it still did not work.
I still get an error message
any other suggestions?
Thanks
Chey

I cant see that you are but the NZ function allows you to return a value
other than Null, the syntax is

Nz ( variant, [ value_if_null ] )

So Variable = Sum(NZ[Control],0.00) should give you 0.00 if null. But, I
have just noticed I missed out a comma just before the 0.00, try that but
also take a look at the link I posted, it explains the NZ function better
than I can.

HTH, Mike

It says I have invalid syntax. Am I missing an ) somewhere?

Try =Sum(NZ([Credit Card],0)00.0) or take a peek at this link

http://www.techonthenet.com/access/functions/advanced/nz.php

:

I have this in an unbound field
=Sum(Nz([Credit Card],0))

If the fields that are null are sumed up and there is nothing there then I
want by result to be 0.00. Right now it dosent show anything.
Can someone help me?
Thanks
Chey

 

[Guest]

Hi Chey

I'm sorry, your original syntax is correct and not the version I offered,

=Sum(Nz([Credit Card],0)) If you set the format of the unbound control to
Fixed or Currency, it will return 0.00 in the unbound control box but only if
the control being summed is 0.00. It will remain blank if it is Null (A new
record) I have not found a way around this yet and I appear to be stumped as
well. I wonder if you can test the value of Credit Card and if null me.Credit
Card = 0.00 may do it but you would have to do that within the vba for the
form. I will set up a similar form tomorrow and see if I can get a work
around.

After reading your link I wonder if Credit Card was generated this way
Credit Card: IIf([CTA]=-1,Null,[Credit_Card_Amount])
then in the footer I put
=Sum(NZ([Credit Card],0)00.0)
then =Sum(NZ([Credit Card],0),00.0)
it still did not work.
I still get an error message
any other suggestions?
Thanks
Chey

I cant see that you are but the NZ function allows you to return a value
other than Null, the syntax is

Nz ( variant, [ value_if_null ] )

So Variable = Sum(NZ[Control],0.00) should give you 0.00 if null. But, I
have just noticed I missed out a comma just before the 0.00, try that but
also take a look at the link I posted, it explains the NZ function better
than I can.

HTH, Mike

It says I have invalid syntax. Am I missing an ) somewhere?

:

Try =Sum(NZ([Credit Card],0)00.0) or take a peek at this link

http://www.techonthenet.com/access/functions/advanced/nz.php

:

I have this in an unbound field
=Sum(Nz([Credit Card],0))

If the fields that are null are sumed up and there is nothing there then I
want by result to be 0.00. Right now it dosent show anything.
Can someone help me?
Thanks
Chey

 

[Guest]

Chey, what are you trying to sum? I have set up a table, query and form with
the Credit_Card field.

=Sum(NZ([Credit_Card],0)) Sums the value in the table which is displayed on
the form and returns 0.00 if Format is set to fixed. However, 0.00 is only
ever visible with an empty table, as soon as you enter a value then that
value becomes the minimum dispalyed. Entering a further record only adds to
the orginal and gives you the total sum?

As Credit_Card is a field in a table (or even if its another unbound control
on the form) it has to be enclosed in square brackets. The reason for the
under score in the naming convention is because VBA falls over sometimes if
you have gaps in table names (No Gaps is a good habit to adopt).

Perhaps you could let me know what you are trying to do?

Hi Chey

I'm sorry, your original syntax is correct and not the version I offered,

=Sum(Nz([Credit Card],0)) If you set the format of the unbound control to
Fixed or Currency, it will return 0.00 in the unbound control box but only if
the control being summed is 0.00. It will remain blank if it is Null (A new
record) I have not found a way around this yet and I appear to be stumped as
well. I wonder if you can test the value of Credit Card and if null me.Credit
Card = 0.00 may do it but you would have to do that within the vba for the
form. I will set up a similar form tomorrow and see if I can get a work
around.

After reading your link I wonder if Credit Card was generated this way
Credit Card: IIf([CTA]=-1,Null,[Credit_Card_Amount])
then in the footer I put
=Sum(NZ([Credit Card],0)00.0)
then =Sum(NZ([Credit Card],0),00.0)
it still did not work.
I still get an error message
any other suggestions?
Thanks
Chey

I cant see that you are but the NZ function allows you to return a value
other than Null, the syntax is

Nz ( variant, [ value_if_null ] )

So Variable = Sum(NZ[Control],0.00) should give you 0.00 if null. But, I
have just noticed I missed out a comma just before the 0.00, try that but
also take a look at the link I posted, it explains the NZ function better
than I can.

HTH, Mike

:

It says I have invalid syntax. Am I missing an ) somewhere?

:

Try =Sum(NZ([Credit Card],0)00.0) or take a peek at this link

http://www.techonthenet.com/access/functions/advanced/nz.php

:

I have this in an unbound field
=Sum(Nz([Credit Card],0))

If the fields that are null are sumed up and there is nothing there then I
want by result to be 0.00. Right now it dosent show anything.
Can someone help me?
Thanks
Chey