Solved

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

Posted on 2011-03-23
11
283 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
  • 5
  • 5
11 Comments
 
LVL 84
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 250 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

820 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