Feb 23 2004, 12:52 PM
here comes the stupid newbie again. Alright in a report: I have this
but weather the Field is empty or not, it always gives me "Full" in the report. I tried using NULL too, same thing.
Using "Something" instead of NULL ("Something" representing an existing value) gives me the "Empty" so that is working, just not what I want.
Could anyone help me out? If some Original Field is empty I want to put out something different than if it was full.
In that report I have some values I want to add up together. They all come from one field (let's name it Value).
So what I tried is to put a =SUM([Value]) into the footer to get a result but all i get is an #Error in the report.
Please help me out guys!
Feb 23 2004, 01:13 PM
Both of your problems may be caused by null values. Instead of using the fields from the table "raw", it is wise to use nz(). For instance sum(nz([Value])).
(I'm assuming in your first point it's just a typo and your code says iif, not if)
Feb 23 2004, 01:25 PM
Sorry to tell ya, but the first problem is not caused by a typo. I checked twice now and as I said:
Instead of looking for a NULL Value and looking for an existant Value "something" and using the same formula, "Empty" is returned, so the formula works the way it should just the NULL thingy doesn't.
the nz() trick does not work either, I tried and I still get the error. The numbers to be added up together are numbers without comma, plain 1234 dirt
Feb 23 2004, 01:30 PM
To test for null in the IIF (surely it should be IIF, not IF) use isnull().
For the sum(), which footer is it placed in? If it's the page footer move it to the report footer or a group footer. What's the name of the text box- make usre it is not named the same as a filed in the data.
Feb 23 2004, 01:44 PM
You don't have how you are testing for null but it should look like this:
As for your second question, you are probably trying to put the sum in the page footer. You must use either a group footer or the report footer.
Feb 23 2004, 02:12 PM
Wow thanks a lot guys!! Your seriously rock! Fast answers and pure quality. The first problem is solved the IsNull did the trick.
Alright here comes the lame question: I guess you're right. I put the Sum into the page footer which is apperantly wrong. Alright when I put into the report footer it asks me (when previewing the report) where to get the data from. I think I am doing something wrong here.
What I did is just putting the =Sum(nv([myfield]) into the report footer and then it asks me. Sorry fot all the dumb questions it's embarassing
Feb 23 2004, 02:18 PM
There is no function called nv() perhaps you meant nz()?. Also, [myfield] should be replaced with your actual field name whatever it is.
Feb 23 2004, 06:06 PM
Sorry Jerry, I meant nz as previously stated.
Still the porblem persists that when just putting the Field (as stated above) =SUm(nz([anyfield])) into the report footer instead of the page footer a message window appears that I am supposed to enter a Parameter for "anyfield".
Sorry just beginning
Feb 23 2004, 07:19 PM
Do you have a field named "anyfield" in your underlying recordset? I find it hard to believe that you would name a field that. Replace this placeholder with the actual name of the field you wish to get the sum of.
Feb 24 2004, 06:28 AM
anyfield and myfield were just place holders for the actual names!
Well I solved the problem now, it was a field naming error. I named one field "Something/Anotherthing" which changed the fomrmula automatically to =sum(nz([Something]/[Anotherthing])) that messed up the formula.
I changed the field name and everything is fine.
Thanks for all your suppord guys ur just great!
Feb 24 2004, 07:43 AM
Avoid using special characters in your field names. This includes spaces.
Glad you got it resolved, you're welcome.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here