By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Another Nz() Problem in A2K

P: n/a
I was on a deadline yesterday with a report created in Access 2000.
I was adding a PastDue amount to an Invoice record and needed to
alter the Invoice report to have a PastDue field. I wanted the
label for that field to include the date the date the PastDue
amount was calculated. I originally had this as the controlsource:

="Past Due (" & Nz([PastDueDate],[CreatedDate]) & ")"

CreatedDate is useful here because invoices without PastDue amounts
lack a PastDue date (I could get around this for new invoices by
simply setting the default value of PastDueDate to be Date() so
that its value would be the same as CreatedDate, but I would have
to run a query to back-populate old data).

I tried all sorts of things, cutting out various elements of the
expression. Referring directly to either of the fields without the
Nz() worked just fine. Replacing PastDueDate with Null worked fine,
but replacing CreatedDate with Null still failed. My first thought
was that the problem was a reference ambiguity on a field name, but
I checked this thoroughly and there were no conflicts. Lacking time
to track it down, I replaced it with the equivalent IIf():

IIf(IsNull([PastDueDate]),[CreatedDate],[PastDueDate])
And that worked. So, it was definitely a problem with the Nz()
function, which seems to work just fine everywhere else in the
application.

Any ideas what I should look at?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
CDB
NZ() defaults to 0 for a date field, which is a date, as you know.

Try NZ(PastDueDate],[CreatedDate],"") to force a null to an empty string.

I always specify the return value for Null, as a discipline. On a report, it
can even be an exception message, eg NZ(~,"Date Missing"). Alternatively, I
use the Format =ve;-ve;0;Null to give full messages/data.

Clive

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.90.. .
I was on a deadline yesterday with a report created in Access 2000.
I was adding a PastDue amount to an Invoice record and needed to
alter the Invoice report to have a PastDue field. I wanted the
label for that field to include the date the date the PastDue
amount was calculated. I originally had this as the controlsource:

="Past Due (" & Nz([PastDueDate],[CreatedDate]) & ")"

CreatedDate is useful here because invoices without PastDue amounts
lack a PastDue date (I could get around this for new invoices by
simply setting the default value of PastDueDate to be Date() so
that its value would be the same as CreatedDate, but I would have
to run a query to back-populate old data).

I tried all sorts of things, cutting out various elements of the
expression. Referring directly to either of the fields without the
Nz() worked just fine. Replacing PastDueDate with Null worked fine,
but replacing CreatedDate with Null still failed. My first thought
was that the problem was a reference ambiguity on a field name, but
I checked this thoroughly and there were no conflicts. Lacking time
to track it down, I replaced it with the equivalent IIf():

IIf(IsNull([PastDueDate]),[CreatedDate],[PastDueDate])
And that worked. So, it was definitely a problem with the Nz()
function, which seems to work just fine everywhere else in the
application.

Any ideas what I should look at?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 12 '05 #2

P: n/a
al***@delete.wave.co.nz (CDB) wrote in
<bm**********@news.wave.co.nz>:
NZ() defaults to 0 for a date field, which is a date, as you know.

Try NZ(PastDueDate],[CreatedDate],"") to force a null to an empty
string.
Er, Nz() takes only two arguments, not three.

And in a query, the Nz() that doesn't work in the report returns
exactly what I'd expect.

And CreatedDate is never Null, as it's got a default value of
Date() (which is why I am using it).
I always specify the return value for Null, as a discipline. On a
report, it can even be an exception message, eg NZ(~,"Date
Missing"). Alternatively, I use the Format =ve;-ve;0;Null to give
full messages/data.


While this is a good thing to do, it's simply irrelevant to my
problem, as I've specified both arguments for Nz(), and the second
argument is never Null. It can't possibly be the cause of the
problem.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.