Hi all, I'm Rummy. Running Access 2002. I'll admit right now, I'm not too fancy with SQL and definitely not so with VBA, so mainly work through the simple GUIs of Access. I've come across a rather large/annoying sticking point however;
I'm working on a database(not made/designed by me, though I'm in the process of redesigning its underworkings) and I've just decided to deal with some null values on a report, effectively equivalent to an IIF/IsNull test. That worked fine, no problem.
Decided I would do the same for another report, only this time when I do it returns constant #Errors for the field I've put the =Nz() function in.
The queries return the data fine, and the reports work fine without the =Nz() function. On BOTH reports I'm working on a calculated field(calculated in my queries), and as said all works fine. Until I put the =Nz() in my second report.
The curious thing though; if I change the report's data/query source to the other query, it still doesn't work. So it's not at a query level from what I can see, but also...I can't determine it being on the report level either?!
Tried remaking from scratch, tried editing the first to be exactly like the second, there's SOMETHING that just won't let me use =Nz() in my second report. Tbh I could probably get around what I want to eventually achieve by CSng() in the main queries, and then error checking(which will result from the null) and then replace the error with something via an IIf statement, but still...I'd like to know why the hell I'm having this problem.
Does anyone know of anything that would cause an =Nz() function to return constant #Errors on a report, even if it works on the same query's fields on another report?
(sorry if this all sounds quite confusing, will do my best to provide more info/help but also my database contains confidential information that makes it a little trickier to post what it shows unless I go through and make up lots of made up info)
I'm working on a database(not made/designed by me, though I'm in the process of redesigning its underworkings) and I've just decided to deal with some null values on a report, effectively equivalent to an IIF/IsNull test. That worked fine, no problem.
Decided I would do the same for another report, only this time when I do it returns constant #Errors for the field I've put the =Nz() function in.
The queries return the data fine, and the reports work fine without the =Nz() function. On BOTH reports I'm working on a calculated field(calculated in my queries), and as said all works fine. Until I put the =Nz() in my second report.
The curious thing though; if I change the report's data/query source to the other query, it still doesn't work. So it's not at a query level from what I can see, but also...I can't determine it being on the report level either?!
Tried remaking from scratch, tried editing the first to be exactly like the second, there's SOMETHING that just won't let me use =Nz() in my second report. Tbh I could probably get around what I want to eventually achieve by CSng() in the main queries, and then error checking(which will result from the null) and then replace the error with something via an IIf statement, but still...I'd like to know why the hell I'm having this problem.
Does anyone know of anything that would cause an =Nz() function to return constant #Errors on a report, even if it works on the same query's fields on another report?
(sorry if this all sounds quite confusing, will do my best to provide more info/help but also my database contains confidential information that makes it a little trickier to post what it shows unless I go through and make up lots of made up info)
Last edited:
