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

Getting NZ equivalent in an .adp View

P: n/a
CFW
I have a view (Access .adp fronte end and SQL Server back) that
retrieves a currency field from a table that is sometimes a NULL
value. That view is the RowSource for a List box that displays the
results. I want the List Box to show that field as 0 instead of
leaving it blank if the underlying table field is NULL. I can't use
the IIf or NZ function in a view. How can I do this?! TIA!
BTW - I MOSTLY need it because I SUM that List Box Column and display
it in a text box on the form. Obviously, without the 0 for the NULLS,
the SUM is wrong. Thanks again!
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
With ADPs, your queries are actually running on the SQL Server server, so
they must comply with SQL Server's rules.

SQL Server's equivalent of Nz([Field], 0) is IsNull([Field], 0)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"CFW" <ch***@thewilkfamily.com> wrote in message
news:aa********************************@4ax.com...
I have a view (Access .adp fronte end and SQL Server back) that
retrieves a currency field from a table that is sometimes a NULL
value. That view is the RowSource for a List box that displays the
results. I want the List Box to show that field as 0 instead of
leaving it blank if the underlying table field is NULL. I can't use
the IIf or NZ function in a view. How can I do this?! TIA!
BTW - I MOSTLY need it because I SUM that List Box Column and display
it in a text box on the form. Obviously, without the 0 for the NULLS,
the SUM is wrong. Thanks again!

Nov 12 '05 #2

P: n/a
CFW
THANK YOU! I am TRYING to learn the differences!

On Sun, 08 Feb 2004 15:48:36 GMT, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:
With ADPs, your queries are actually running on the SQL Server server, so
they must comply with SQL Server's rules.

SQL Server's equivalent of Nz([Field], 0) is IsNull([Field], 0)


Nov 12 '05 #3

P: n/a
On Sun, 08 Feb 2004 15:31:35 GMT in comp.databases.ms-access, CFW
<ch***@thewilkfamily.com> wrote:
I have a view (Access .adp fronte end and SQL Server back) that
retrieves a currency field from a table that is sometimes a NULL
value. That view is the RowSource for a List box that displays the
results. I want the List Box to show that field as 0 instead of
leaving it blank if the underlying table field is NULL. I can't use
the IIf or NZ function in a view. How can I do this?! TIA!
BTW - I MOSTLY need it because I SUM that List Box Column and display
it in a text box on the form. Obviously, without the 0 for the NULLS,
the SUM is wrong. Thanks again!


Actually, the sum is correct to produce null, null being an unknown or
undefined value, to assume they are 0 is incorrect.

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.