| Author |
Message |
JWaldmann
Newbie
Joined: 01 Dec 2016
Online Status: Offline
Posts: 13
|
   Topic: For Loop or Not For Loop
Posted: 13 Apr 2018 at 8:52am |
|
Database with clients who have dates associated with activities.
Client Date
1 1/1/9999
2 5/5/2017
2 1/1/9999
3 5/5/2016
3 10/5/2017
I need to exclude all records for clients who have any record with a date of 1/1/9999. I have tried to grab Max date to no avail. (My actual data has 9999 as the year.)
So, with the data above I would only return Client #3.
If anyone could help I will call you a hero!
Edited by JWaldmann - 13 Apr 2018 at 10:16am
|
 IP Logged |
|
|
Valert16
Groupie
Joined: 21 Mar 2016
Location: Spain
Online Status: Offline
Posts: 57
|
Posted: 15 Apr 2018 at 11:06pm |
|
Group on Client.
Create a group summary of the Date Field. Use the Maximum function.
Any group that has a max = 01/01/9999 must be excluded, so add a group select condition like
Maximum ({Table.DateField}, {Table.Client}) <> DateValue(9999, 01, 01)
Hide group header and footer if needed.
|
|
IP Logged |
|
JWaldmann
Newbie
Joined: 01 Dec 2016
Online Status: Offline
Posts: 13
|
Posted: 16 Apr 2018 at 2:08am |
|
Thank you, so much, for the reply. I think it is great that there is a place with people so un-selfish with their knowledge that I can ask a question and have it answered!
I did as you suggested on my first attempt; however, if you notice that client #2 has a 9999 date AND a 2017 date. My report does exclude the Client 2 record with the 9999 date, but I still show the 2017 date record. I need to remove ALL clients that have EVER had a 9999 date.
I hope this makes sense.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Apr 2018 at 2:57am |
|
Did you do a group select criteria?
|
|
IP Logged |
|
JWaldmann
Newbie
Joined: 01 Dec 2016
Online Status: Offline
Posts: 13
|
Posted: 16 Apr 2018 at 2:58am |
|
Just last Friday, late. I am playing with that this morning. I seemed to have to put the statement in the Group header and footer.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Apr 2018 at 3:17am |
|
It has to be in the select expert.
The is a Record Selection or Group Selection option inside the select expert. You will be doing a group selection.
|
|
IP Logged |
|
JWaldmann
Newbie
Joined: 01 Dec 2016
Online Status: Offline
Posts: 13
|
Posted: 16 Apr 2018 at 7:20am |
|
This worked great! Thank you!!
Also, as promised; you are a hero!
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Apr 2018 at 7:49am |
|
Please be careful of any other summary formulas you are doing in this report.
The summary values are going to be executed before the group select (hence the ability for the group select to happen). If you need to do other sums or counts you have to use running totals or variable formulas to exclude the values you are excluding via the group condition.
Good luck :)
|
|
IP Logged |
|
|
|
