Solved

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

Posted on 2011-03-23
11
284 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 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

738 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