?
Solved

How to make query display "---" instead of zero (0)

Posted on 2011-03-23
11
Medium Priority
?
286 Views
Last Modified: 2012-05-11
Hi,

I have a query that works great for me except that it returns a zero when record is null just for MSR column. How can I make it so that the all null values return "---" in that field and all the other fields in the query. If that can't be done, I can settle for just a blank field. I added this to my first query but then no records were return after the change.
TRANSFORM Nz(Sum(tblAgentSummary.F4), "---") AS SumOfF4

I have 2 queries, one uses the other to generate the records. Not sure if I need to make a change on both for this to work. I have include both SQL statements from the query.

TRANSFORM Sum(tblAgentSummary.F4) AS SumOfF4
SELECT tblAgents.[Agent Name], tblAgents.Position
FROM tblAgents INNER JOIN tblAgentSummary ON tblAgents.[AgentID#] = tblAgentSummary.F3
WHERE (((tblAgentSummary.callDate) Between [forms]![frmAgentSummaryExport]![txtBusinessDate] And [forms]![frmAgentSummaryExport]![txtEndDate]) AND ((tblAgents.Position)=[PosParam1] Or (tblAgents.Position)=[PosParam2]) AND ((tblAgents.Department)=[forms]![frmAgentSummaryExport]![cboDepartment]))
GROUP BY tblAgents.[Agent Name], tblAgents.Position
ORDER BY tblAgents.Position
PIVOT tblAgentSummary.F1 In ("Salo","Other","GiftCard","Lend","Mort","Web");

SELECT qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Position, qryAgentSummary_Crosstab.Salo, (NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.Web
FROM qryAgentSummary_Crosstab;


0
Comment
Question by:KLMServices
[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
  • 5
11 Comments
 
LVL 85
ID: 35199590
You can use the Nz function to do this:

Nz((NZ(qryAgentSummary_Crosstab!GiftCard))+(NZ(qryAgentSummary_Crosstab!Other)), "----") AS MSR

0
 

Author Comment

by:KLMServices
ID: 35199757
Thanks, I made that change before and it still returns zero values. It only returns zero for this column, but blank for all the other columns in the query. Must be something else I am overlooking.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35200295
Try something like this:

IIF([SomeExpression]=0,"---",[SomeExpression])

Here "Someexpression could be anything you like:
strCustID
Forms!YourForm!YourControl

So try something like this perhaps:

IIF([qryAgentSummary_Crosstab!Other]=0,"----",[qryAgentSummary_Crosstab!Other])
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35200369
Basically I think you need to wrap the function as close to the actual zero value as possible.

For example if a control can be zero, and you reference that control in another formula, it would look like this:
me.txtRatio=me.txtVa1+IIF(txtVal2=0,"---",txtVal2)
...Not this:
me.txtRatio=IIF(me.txtVa1+txtVal2=0,"---",txtVal2)

I may not have a good grasp on your objects or their names, ...but I think you get the idea...

Also remember that if you change a zero to a string "---", you will not be able to use it in future calculations, like you could if you let it as zero...

So I would just use this for display only.

Let me know if I am misunderstanding something....

JeffCoachman
0
 

Author Comment

by:KLMServices
ID: 35201348
The data is being exported to excel. No other calculations will be required.
0
 

Author Comment

by:KLMServices
ID: 35206793
Hi, I tried plugin in IIF([qryAgentSummary_Crosstab!Other]=0,"----",[qryAgentSummary_Crosstab!Other])   into my sql without luck. Nothing seemed to change. Still display (0) in the MSR column, and tried doing the same for the Web column, but no changes occured.

SELECT qryAgentSummary_Crosstab.[Agent Name], qryAgentSummary_Crosstab.Position, qryAgentSummary_Crosstab.Salo, IIF([qryAgentSummary_Crosstab!Other]=0,"----",[qryAgentSummary_Crosstab!GiftCard]) +IIF([qryAgentSummary_Crosstab!Other]=0,"----",[qryAgentSummary_Crosstab!Other]) AS MSR, qryAgentSummary_Crosstab.Lend, qryAgentSummary_Crosstab.Mort, qryAgentSummary_Crosstab.WebIIF([qryAgentSummary_Crosstab.Web]=0,"----",[qryAgentSummary_Crosstab.Web])
FROM qryAgentSummary_Crosstab;
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35207869
Then please post a sample of this Database...

Without any context, (for me at least), it is not clear what you should be doing...

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Delete any objects that do not relate directly to the issue.
5. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
6. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
7. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
8. Compile the code. (From the database window, click: Debug-->Compile)
9. Run the compact/Repair utility.
10. Remove any Passwords and/or security.
11. Post explicit steps to replicate the issue.
12. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see the issue.

JeffCoachman
0
 

Author Comment

by:KLMServices
ID: 35208356
I attached sample dba and excel file. you can use 03/16/2011 for test dates along with All for the drop down. Just trying to get it to export "---" for all blank or (0) cells.

Thanks for all the help on this.
TestDBA.accdb
Temp.xlsx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35210528
There are design "issue" with your DB, so the fix to your data will crash your export code.
0
 

Author Comment

by:KLMServices
ID: 35211313
Ok thanks for taking a look at it. I will write a macro to make the changes in excel.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1000 total points
ID: 35211922
You can do it in Access, you just have to tweak a few things.

But if you have an Excel solution, then roll with it.

BTW, ...why not just Export the Crosstab query directly to Excel, without all the Excel manipulations in VBA?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

741 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