Grouping on separate date fields

[SueBK]

I have a table with four date fields - data requested [DReq], data received [DRec], search requested [SReq], search received [SRec]. (To aid understanding - we request data from a department internally to accompany a search request to an external department). I want to create a report to send to the external department to say "this is what we've requested from you [DReq]; this is what we've received from you [DRec]; and this is what we're about to send to you [SRec] & [SReq]."

I'd like, therefore, to group my report by the date fields, but I've only ever grouped reports on the results in a single field, not across multiple fields. The grouping I'm aiming for is:

[SRec] is not null - done and complete search
[SReq] is not null but [SRec] is null - searches requested but not received
[DRec] is not null but [SReq] is null - searches about to be sent through
[DReq] is not null but [DRec] is null - searches we will be sending through as soon as we receive the data internally.

Can I group within a single report, or do I have to create a subreport with a separate query for each subset of data?

 

[vbaInet]

It's more appropriate to be criteria than grouping. Why don't you create one report, pass the apropriate criteria to it, print it, close the the report, move to the next criteria and repeat the same process for all four?

Anyway, to answer your question you can use something like the Switch() function to create the field you will use to group on. Start from the bottom criteria and move up.

Switch([DReq] Is Not Null [COLOR=Red]AND[/COLOR] [DRec] Is Null, [COLOR=Blue]4[/COLOR], [DRec] Is Not null [COLOR=Red]AND[/COLOR] [SReq] Is Null, [COLOR=Blue]3[/COLOR] ...etc)

Notice what I've done there? 4 will the group ID for the last criteria because you want it at the bottom, 3 will be the group ID for the third criteria and so on, complete the rest.

You will also need to put Not Is Null under this new field because if none of the criteria is met, Switch will return Null and you don't want those records.

More about Switch() here:
http://www.techonthenet.com/access/functions/advanced/switch.php

 

[SueBK]

I created a field in my query that works on iifs, casading through the dates - so if the first date is null, give me a text response "pending", otherwise, if the 2nd date is null etc etc. I have grouped on this field.