473,320 Members | 1,887 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Getting NZ equivalent in an .adp View

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
3 2078
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

303
by: mike420 | last post by:
In the context of LATEX, some Pythonista asked what the big successes of Lisp were. I think there were at least three *big* successes. a. orbitz.com web site uses Lisp for algorithms, etc. b....
14
by: John | last post by:
Is there an equivalent of COM on Linux that I can get through Python. My need is to have some sort of language independent component framework. I can think of CORBA but I have to have a server...
6
by: melanieab | last post by:
Hi, Easy question. It seems to me that I'm following the examples correctly, but apparently I'm not. I'm trying to retrieve the data from a row called "row" and a column called "File". This is...
4
by: pcnerd | last post by:
I've been playing with "classic" VB since version 3. I have VB6 Learning Edition. Recently, I wanted to try VB.NET. I got a beginner's book with a CD with the software & installed it. There are...
4
by: WayneM | last post by:
In VB6 there was a Last Position in the popup menu that took you back in the IDE to the last line that you were working on. Is there an equivalent in VS2005? Thanks for info in advanced, ...
1
by: Tarun | last post by:
In asp.net 2.0 Gridview is equivlent to Datagrid in asp.net1.1.. with more facialities. Datagrid Item Command is Equivalent to RowCommand in Grid View. But from the grid view command how can i get...
3
by: Joe Reiss | last post by:
Hi All, Using vs 2005, c#, ms sql 2005 asp.net web site. Using httphandler to generate dynamic image from sql server database. Going according to instructions from msn: I'm using is...
9
by: Academia | last post by:
ContextMenu has a Popup event but MainMenu does not. I need Popup with MainMenu. Do you know anyway to simulate it? Thanks in advance
185
by: jacob navia | last post by:
Hi We are rewriting the libc for the 64 bit version of lcc-win and we have added a new field in the FILE structure: char *FileName; fopen() will save the file name and an accessor function will...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.