Solved

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

Posted on 2011-03-23
11
276 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now