I have a report based on a simple parameter query. A control button on a form runs the query which asks for the fiscal period to be entered and then displays the report. One of the criteria for grouping in the report is DEPT. Many of the records have no DEPT values. The report keeps giving two groups of DEPT for no value in that field. I want to see the records for no value entered in the DEPT field but cannot understand why it breaks them into two groups. At first I thought that maybe a user had enter a space in one or more of those records in that field, but I haven't found that to be the case. Is there something about grouping on a field that has some empty fields in records that can cause this?

You are probably seeing two groups because for some records the DEPT field is null and in other records it is a zero-length string. There is a difference between the two. The way to get around this is to convert the null values to a zero-length string by using the nz() function. Then group by this new calculated field.

Thanks for the guidance. I'm pretty new at this. Could I put the code in the control source box? Something like:

Nz(DEPT)

Thanks,
fish3s

No, in the query where you do the grouping, you need to group by nz(DEPT,"").

Sorry I'm so dense about this. In the query it won't allow entering anything in the Group By line in the DEPT field other than the operators that are in the pull-down box. When I put it in the criteria line, the query returns no records, when in fact there should be many.

Thanks for helping,
fish3s

What is the SQL you have for the query right now? Are you doing the grouping in the report by using the sorting and grouping toolbar item? Then where you are currently grouping by DEPT, just replace that with nz(DEPT,"") so that the grouping is done by this expression rather than what you have right now.

The grouping is being done in the report but the grouping toolbar only allows what is in the pull-down which is the fields of the underlying table which is the source for the parameter query which asks for the FISCAL PERIOD.

The report is grouping on FISCAL PERIOD, DEPT, and then PROBLEM CODE in that order and then sorts on PROBLEM CODE.

Don't click on the drop-down arrow. Just type in the expression I have given you in place of DEPT.

It doesn't allow me to in the Group, Sort, and Total toolbar in the report.

OK, maybe this is a new feature in Access 2007. I have only Access 2003 on my computer here and I can type in expressions rather than choosing something from the dropdown. I will get someone who has Access 2007 to help you out. Sorry for the delay.

How did you type it? in the Grouping and Sorting dialog, you need to type it in the form of an expression:

=Nz(Dept,"")

I do have A2007 (its at home and I am at work) and I vaguely remember doing this just yesterday ...

Thanks for jumping in, Brent.

Just to expand a bit as I am now at home and can verify ... in the Grouping & Sorting dialog ... er um "Frame" at the bottom of the screen ... when you display the field list you will see the word "Expression" at the bottom of the pop up window that displays the field list ... click on that word and you will see the expression builder pop up ... it is there that you will type in"

=Nz(Dept, "")

... Or ... you can just add a calculated column to your Query/SQL that is the recordsource for your form, then you will be able to select the calculated column's alias in the field listing

Success! I hadn't noticed the word "Expression" at the bottom of the pop up window that displays the field list. It didn't like the syntax at first, but after some experimenting it worked. It took it when I added brackets around the first expression "Dept"

=Nz([Dept],"")

The report now only shows one group of records with no Dept specified that equals the total of the two groups displayed previously.

Thanks so much for your help Balaji and Brent!

fish3s

You are welcome. Good luck with your project.