Solved

Help with query criteria and isnull

Posted on 2014-12-01
5
162 Views
Last Modified: 2014-12-01
What is wrong with:

Qty On Hand: IIf(IsNull([SumOfQTY_ORDERED]),0,[[SumOfQTY_ORDERED])
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 40473673
remove the bracket [ after 0,

Qty On Hand: IIf(IsNull([SumOfQTY_ORDERED]),0,[SumOfQTY_ORDERED])
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40473698
Or alternatively, use Nz([SumOfQTY_ORDERED],"")

But don't give me points for this answer please - Rey was here before me.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40473707
I don't think you need ANY of the hard brackets, as you are not using a field with a space or other "illegal" character. It might clear it all up.
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 40473744
If this is SQL, you should use:

    IIf([SumOfQTY_ORDERED] Is Null,0,[SumOfQTY_ORDERED])

If VBA, then:

    Nz([SumOfQTY_ORDERED], 0)

/gustav
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40473982
No points please.

As indicated, removing all of the [ and ] brackets should solve your problem, but I agree with Phillip, I would recommend using the NZ function

Qty On Hand: NZ([SumOfQTY_ORDERED],0)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question