automatic value in the field of a report

[WaltLukeIII]

I have a report and sometimes I will have no value in a field so I want to automatically have it show up on my report as $0.00 and am having trouble doing this. I would like to avoid as much coding as possible. Thanks

Walt III

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[scriverb]

If this is a currency or numeric(double) field type to start with, and the preset format is $0.00 then put the following in the Control Source property:

=IIF(IsNull(me![ControlName]), 0, me![ControlName])
This will replace the Null value with a 0 value and display it as $0.00. You normal value will not be affected by this.

Let me know if you control field is of a different data type.

Bob Scriver

 

[WaltLukeIII]

my VB is a little rusty but here is what I put in

=IIF(IsNull(me![1]),0,me![1])

The Computer then changes it to

=IIF(IsNull([me]![1],0,[me]![1])

it then tells me "The Microsoft Jet database engine does not recognize 'me' as a valid field name or expression.

and 1 is the name of the field, and I am running access 97.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[scriverb]

ACCESS is not recognizing the report control object me![1]. This is supposed to be the name of the text control object in your Detail Section. "1" is that the name of your control? Right click on the control in question and look at the properties. What is the Name? That is the name to put inside the square brackets. me![ControlName]

Another Function that would work just as well and is a little snorter is the following NZ(Me![ControlName]. This function is called NullToZero. If the Control value is Null it displays a Zero(0). It can be modified to display something other than Zero also. NZ(me![ControlName], "Zero&quot

Let me know if you have more problems.

Bob Scriver

 

[WaltLukeIII]

I am still getting the error

The Microsoft Jet database engine does not recognize 'me' as a valid field name or expression.

if I put an '=' sign at the beginning of the expression. You were correct and the control name I was using was wrong
I put in

NZ(me![Text8])

If I do not put the '=' sign in the front it adds a bunch of brackets and parenthesis and tells me that there is one missing. I go to look at it in the 'control source' of the 'properties' window and it looks like above.

If I do use the '=' sign then it adds brackets around me like this.

=NZ([me]![Text8])

I really appreciate your help.


Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[sko]

Couldn't you set the control's format property?

$#,##0.00;($#,##0.00);;$0.00

 

[WaltLukeIII]

I don't have a clue what part of that format made it work but it worked. Thankyou very much. However this did not fix the problem I am trying to correct. I have another field that gives a total based upon two fields from the query if the one field is 0 then the total field is blank. It is based upon a crosstab query, using months and catagories to subtotal and then to total on.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[sko]

Assuming your total is an unbound textbox on your report and not a column in your query.

Control source for unbound totals textbox on your report:

=Nz([field1]+Nz([field2])