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

IsNull Or IsNotNull problems

P: n/a
I am working on an access 2000 db right now. Here is the problem I am
having, and please let me know if I am not explaining it fully.
I have several tables, 3 of which I want to pull information from and
create a summary query that will be displayed via .asp pages built using
frontpage. Each table has a field called toolordernumber that are
related. Second table has entries that list number of hours worked on
this project. Third shows billing to customers for this project. What I
would like to do is this. Create a summary that must:
1. List all toolorders from main table.
2. List,if any, total number of hours worked on this project *25
3. List, if any, amounts billed for each of these orders.
I have created queries for tables 2 and 3 using the unmatched query
wizard and adding IsNull Or IsNotNull criteria to each of these so they
will show all of the orders from the main table and leave a blank field
if there is none.
Finally, I need to be able to add the cost of the hours worked (number
worked*25) to the customer billing total.
The problem I run into is that I have created the summary to pull the
correct info from each query, but when I try to add the two fields, if
one is null, I get another blank field. What is a workaround for this?
Example
Main Table
Toolordernumber
1
2
3
Hours table
Toolordernumber hours
1 4
1 3
3 2
Invoice table
Toolordernumber invoicecost
1 100
2 50
1 50

In this case, I would see totals for toolordernumber 1, but neither of
the other two because they don't have a matching field from the other
table. i.e. total=[invoicetable!invoicecost]+[hourstable!hours]
would not work.
Please help.

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
To add a little more to this already long post,

I tried adding this to one of the queries

Iif ([hours]="Null","0")
and that did not do as I had expected, which was to place
a zero in each of the fields that contained nothing, so I could
run the add formula in the last post.

Nov 12 '05 #2

P: n/a
Travis...lookup the Nz Function in the Access help. This will solve your
problem...

Good Luck!
Greg Fierro

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

P: n/a
Greg Fierro wrote:
Travis...lookup the Nz Function in the Access help. This will solve your
problem...

Good Luck!
Greg Fierro

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Being pretty much a newbie to Access, I have spent way to much time on
this problem and I wish I had posted this sooner.
I can't thank you enough for the help.

Travis

Nov 12 '05 #4

P: n/a
Greg Fierro wrote:
Travis...lookup the Nz Function in the Access help. This will solve your
problem...

Good Luck!
Greg Fierro

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

OK so now I got everything working great in access, but it pukes out on
me when trying to connect to the database is frontpage to create the asp
pages, I know this is not a fp group but what the hell I thought I
would try.
Here is the error I am getting
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Nz' in
expression.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)

Here is the sql on the query that I am trying to connect to.

SELECT Results.toolordernumber, Results.sellingprice, Results.complete,
Results.expensed, Results.expensed_previously, CCur(Nz([Total_cost],0))
AS Total_cost_w_hours,
CCur(IIf([Total_cust_invoiceamount]>0,[Total_cust_invoiceamount],"0"))
AS Total_cust_invoice,
CCur(IIf([Total_cost_w_hours]>[sellingprice],[Total_cost_w_hours]-[expensed_previously]-[sellingprice],"0"))
AS Expense_this_month
FROM (((Results LEFT JOIN [invfrovendor Query] ON
Results.toolordernumber=[invfrovendor Query].toolordernumber) LEFT JOIN
[invtocust Query] ON Results.toolordernumber=[invtocust
Query].toolordernumber) LEFT JOIN toolroomhours ON
Results.toolordernumber=toolroomhours.toolordernum ber) LEFT JOIN
qry_invoicefrovendorwtrhours ON
Results.toolordernumber=qry_invoicefrovendorwtrhou rs.toolordernumber
GROUP BY Results.toolordernumber, Results.sellingprice,
Results.complete, Results.expensed, Results.expensed_previously,
CCur(Nz([Total_cost],0)),
CCur(IIf([Total_cust_invoiceamount]>0,[Total_cust_invoiceamount],"0"))
HAVING (((Results.complete)="no"));

From the looks of it, frontpage doesn't like the Nz statement. Any ideas?

Nov 12 '05 #5

P: n/a
Travis wrote:
Greg Fierro wrote:
Travis...lookup the Nz Function in the Access help. This will solve your
problem...

Good Luck!
Greg Fierro

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


OK so now I got everything working great in access, but it pukes out on
me when trying to connect to the database is frontpage to create the asp
pages, I know this is not a fp group but what the hell I thought I
would try.
Here is the error I am getting
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Nz' in
expression.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)

Here is the sql on the query that I am trying to connect to.

SELECT Results.toolordernumber, Results.sellingprice, Results.complete,
Results.expensed, Results.expensed_previously, CCur(Nz([Total_cost],0))
AS Total_cost_w_hours,
CCur(IIf([Total_cust_invoiceamount]>0,[Total_cust_invoiceamount],"0"))
AS Total_cust_invoice,
CCur(IIf([Total_cost_w_hours]>[sellingprice],[Total_cost_w_hours]-[expensed_previously]-[sellingprice],"0"))
AS Expense_this_month
FROM (((Results LEFT JOIN [invfrovendor Query] ON
Results.toolordernumber=[invfrovendor Query].toolordernumber) LEFT JOIN
[invtocust Query] ON Results.toolordernumber=[invtocust
Query].toolordernumber) LEFT JOIN toolroomhours ON
Results.toolordernumber=toolroomhours.toolordernum ber) LEFT JOIN
qry_invoicefrovendorwtrhours ON
Results.toolordernumber=qry_invoicefrovendorwtrhou rs.toolordernumber
GROUP BY Results.toolordernumber, Results.sellingprice,
Results.complete, Results.expensed, Results.expensed_previously,
CCur(Nz([Total_cost],0)),
CCur(IIf([Total_cust_invoiceamount]>0,[Total_cust_invoiceamount],"0"))
HAVING (((Results.complete)="no"));

From the looks of it, frontpage doesn't like the Nz statement. Any ideas?

To follow up once again to myself for anyone interested, it turns out
that .asp does not understand the Nz statement so when I would try to
connect via frontpage, it went belly up. The solution was to remove the
Nz statements and replace with the following example.

If(IsNull(qry_invoicefrovendorwtrhours!Total_cost) ,0,qry_invoicefrovendorwtrhours!Total_cost)

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.