Conditionat display a most recent record

[Former Member]

Hello,

I'm a bit stuck with an urgent report and wondered if anyone could supply an idea.

I need to insert the most recent record with a jobNo<>u201Du201D and Qty <>0 into a subreport and display it . So in this date set as below, the fifth line should be inserted to subreport .

Thank you in advance!

Lin

PartNo Date JobNo Qty Balance

1111 9/10/11 0 5

1111 9/09/11 10 90

1111 9/07/11 9 2

1111 9/04/11 0 9

1111 9/03/11 123 1 110

1111 9/01/11 234 3 220

u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026

[Former Member]

Thank you for any input

[Former Member]

How are you determining the most recent record?

If you have a formula that you can use, you can also use that formula to supress the section with the subreport in it.

Not sure why you want the subreport either. Group of record number, (Or what ever you are using to know what

is the most recent record) you can insert a summary, based on max, record number, (Assuming that the maximum

would be the most recent)

If I have completly missed it, please post again with more detail if posible.

[Former Member]

Thank you very much.

I descending sort the date field on detail section in main report so the last record (line 1) is the most recent record. But I do not want to display the line 1, 2, 3, and 4 because they are either do not have jobNo or Qty is 0. Actually, I want the fifth line to be displayed (I insert it to a subrepot). The reason I want to caret a subreport because I have over hundreds different PartNo showing on main report, but I only need to see the most recent record that has a jobNo and Qty is not 0 for each PartNo. I hope I make my post a little clear. I will be on vacation this coming Wednesday so your assistance is greatly appreciated. Thank You indeed!

PartNo, Date, JobNo, Qty, Balance

1111, 9/10/11, Null , 0, 5

1111, 9/09/11, Null , 10, 90

1111, 9/07/11, Null, 9, 2

1111, 9/04/11, 121, 0, 9

1111, 9/03/11, 123, 1, 11

1111, 9/01/11, 124, 3, 20

u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026u2026

[Former Member]

Hi

To see most recent record with Qty and job number you don't have to create a sub report. Try the following :

--Create a group on Part Number

--Filter the records which are having blank or zero using record selection like :

Not ( isnull(jobNo) ) and Qtry <>0 (QTY not equal to zero)

--Now go in Section expart and go in Suppress condition and write a condition to display only maximum record like :

if Maximum(date) then false else true

Thanks,

Sastry

Edited by: Sastry Duvvuri on Oct 3, 2011 10:10 PM

[Former Member]

Well quick and dirty, you can add this as a select statement

> 0.00 and > 0.00

by doing this you will only get two lines on your report (from your example)

Job No. 123 and 124.

will that help ?

[Former Member]

Hi Sastry,

Thank you for your prompt replay.

Actually, I have skip the second saturation in my previous post due to simplify my explanations thatu2019s why I confused you all.

There are some records within a PartNo have no JobNo at all, for that saturation as below I just want to display the most recent record with Qty id not 0. That means that I can not Filter out nay Null or Zero records.

I guess I may have to use loop /While method to implement but I do not know how to it this moment.

Thank you if anyone can instract me a little bit.

Lin

PartNo, Date, JobNo, Qty, Balance

2222, 09/10/11, Null, 1,1

2222,09/06/11,Null,2,0

2222,09/04/11,Null,0,1

[Former Member]

Hi

For this you don't have to write any loop, just try the filter condition like

Create a formula :

@formula

if not(isnull(Jobid)) and Qty > 0 Then true

else if isnull(jobid) and Qty > 0 then true

else false

Place this in your detail and go in record selection or section expart and suppress all records with ' False' this will give you only records which 1. both jobid and qty are not blank 2. jobid blank and qty not blank.

Thanks,

Sastry

[Former Member]

Hi, Sastry and All,

Thank you so much for your help but this still doesnu2019t solve my problem. With your solution, I can get the result as below. But how could I only display the most recent record for each PartNo ? Thanks a lot.

Lin

PartNo, Date, JobNo, Qty, Balance

1111, 9/09/11, Null , 10, 90

1111, 9/07/11, Null, 9, 2

1111, 9/03/11, 123, 1, 11

1111, 9/01/11, 124, 3, 20

2222, 09/10/11, Null, 1,1

2222,09/06/11,Null,2,0

[Former Member]

Hi

To get recent record, go into the section expart and give the following formula: I hope you have created a group on Part No.

if date = Maxmimum(date,Part No) then false else true

this will suppress all records except the maximum date i.e. recent record.

Thanks,

Sastry

[Former Member]

Hi, Sastry,

I really appreciate you for your consistent assistance.

Thank you very much!

Lin

[Former Member]

Thanks

Edited by: xtan57 on Oct 3, 2011 8:37 PM