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

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;


KLMServicesAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use the Nz function to do this:

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

0
 
KLMServicesAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
Jeffrey CoachmanMIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
KLMServicesAuthor Commented:
The data is being exported to excel. No other calculations will be required.
0
 
KLMServicesAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
KLMServicesAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
There are design "issue" with your DB, so the fix to your data will crash your export code.
0
 
KLMServicesAuthor Commented:
Ok thanks for taking a look at it. I will write a macro to make the changes in excel.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.