Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Grouping formula?

Grouping formula?

Grouping formula?

Completely stumped on this one.

Table 1 layout:
PO Job Suffix
123 123456 001
456 123456 000
987 123456 010

Table 2 layout:
Job Suffix Due Date
123456 001 12/12/2018
123456 000 12/10/2018
123456 010 11/11/2018

I need to group by job due date where the suffix = "000". Where I am having trouble is there are multiple records with the same job number but all have a different suffix (with different due dates). So my group will display all PO's for all jobs, but be grouped by the due date on suffix "000"

job:12345 suffix 001 due date: 12/10/2018 PO:987654
job:12345 suffix 000 due date: 12/12/2018 PO:456789
job:12345 suffix 005 due date: 11/11/2018 PO:789456

job: 234567 suffix 000 due date: 12/15/2018 PO:684153
job: 234567 suffix 010 due date: 11/15/2018 PO:456789

Any thoughts/advise?

RE: Grouping formula?

So do you even want to see the other PO's? It would be easy to filter those out. But you could write a formula that uses the date for PO's that have a suffix of 000.

If {suffix} = '000' then {due date}

RE: Grouping formula?

You can group on Kray’s formula for Group #1 and then group or sort on job and then due date.


RE: Grouping formula?

Thanks for the replies.
I tried using that formula and I get lots of blanks. I assume because the first record it comes across might not be the 000.
If the top record is the 000 I get the due date, if its not I get a blank.

Yes I want to show all jobs and all po's (order doesn't matter). Only thing that matters is they are grouped by the 000 due date.

RE: Grouping formula?

Try using section suppress->suppress blank section.


RE: Grouping formula?

lbass: unfortunately that only suppresses the group name (that's all that is in the group header), but still prints group 2 (job) and details (all jobs and all po's).
I'm going to start from scratch on this one and see if I did something goofy.

RE: Grouping formula?

You can add a suppression formula to those sections also, using:



RE: Grouping formula?

I was able to put If {suffix} = '000' then {due date} in a sub report to pull the correct date, however it doesn't look like I can group off of that.
Any ideas on how to do that / if possible?

RE: Grouping formula?

What happened when you grouped on kray’s formula and then formatted the group headers and footers for every group to suppress when the group formula = date(0,0,0)? This should have worked. I’m assuming the date field is actually a date datatype.


RE: Grouping formula?

I get an error saying a Boolean is required here.

RE: Grouping formula?

Where in the report are you getting this error? In the group selection area or in the section expert where you are trying to suppress unwanted results?

It might help for trouble shooting purposes if you show the group selection formula and the formula you are using for suppression.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close