IIF statement in Report

Question

I am trying to create an IIf statement on a report but am having some trouble. The report is based on a qury that provides me with customers in a specific statusd. The report is grouped by current status. I have a field that counts the number of customers in the current status field. I also have a field for the current status that breaks each one into a separate group. The problem I have is that if there are no members in a group the count works fine listing "0" but the status is left blank. I want it to still state what that status is for that section so that the person reading the report knows that there are "0" customers for the section. is there some kind of way that I can make the default value for the status group to state :In Process if it is "0". I may be going about this the wrong way all together I just simply do not know.

Answer 1

Try not to use pronouns in your descriptions.  When you say "it", I can't tell if you are talking about the form or the report.

Your use of the word "group" is what's throwing me off.  Reports have a very specific meaning for a group specified in the Sorting and Grouping window where you can tell Access that you want the report to provide group header and footer sections.  I have the impression that that is not what you are talking about and suspect that you are using the Report Header section when you say "group it by putting it in the status header", I am confused about jow the report is actually set up.  Part of this confusion might be removed if you would post a Copy/Paste of the report's record source query's SQL view.

Answer 2

Oh, I think I see what you are saying. Pardon me I know it seems quite simple but I am a novice at Access. It is listed in the group header. I input all of the information in a form that has a combo box. I simply choose the status from there for each of the customers. The form is based off of the same query as well.

Answer 3

OK, but there wll be no status group header if there are no records for that status.  Or did you mean to say the Report Header instead of a group header?  If so, I think you will need to modify how the report's record source query get's its parameter.

It sounds like you sre modifying the query to enter the criteria for the status field??  It's a lot easier/cleaner to use a form with a text or combo box to enter the criteria and a command button to run the report.  This way the report can get to the parameter the same way the query picks it up by refering to the form control (eg. Forms!theform.thecontrol).  Then you could use the status text box in the report header can use an expression like  =Forms!theform.thecontrol  to display the status instead of trying to get the status from a detail record that doesn't exist.

Answer 4

Hello thank you for the repy. I was able to group it by putting it in the status header. So when there is no customers in the status the field is just blank. The reports record source is based on a query and in the criteria section I have the following listed "In Process". This part works ok it is just when there are no records to display for the "In Process" section I still need it to state in process so the people reading the report know what section they are looking at.

Answer 5

Need to see the report's record source query.

How did you get a group in the report for a status that has no customers?  And, if you really have a group for a status with no customers, what does the group header(?) display for the status?  Is this the field that's blank?  If you do have a group for the peoblwm status (with a count of 0), I would think the group header text box could be bound to the same field that you used in sorting and grouping.