Date/Time field is Null 1

[Drepso]

Hi,

I need some assistance with the following:
I have the following fields:

DATE IDENT_NO UNIT_ID LOG_CLEAR

Ident_No is a string and Unit_ID is numeric. Log_Clear is a date-time field.

I would like to run a report which returns those rows where Log_Clear is null.

IsNull ({ident.Log_Clear}) does this for me except I have the following problem:

Records can share the same IDENT_NO but have different Unit_ID and where there is more than one Unit_ID sometimes only one Log_Clear time is recorded, sometimes there may be multiple Unit_ID but the number which record a Log_Clear time varies.
Example of some data:

DATE IDENT_NO UNIT_ID LOG_CLEAR
1/04/04 4456-B 5889 01/04/2004 22:59:59
1/04/04 4456-B 5890
1/04/04 4456-B 5824

I have been asked to run a report which will identify those records that have only one IDENT_NO, one Unit_ID and does not record a Log_Clear time.

Eg:

DATE IDENT_NO UNIT_ID LOG_CLEAR
1/04/04 4492-B 5770



Any help would be appreciated.

Cheers,
Drepso

 

[lbass]

I think you could group on {table.IDENT_NO} and then go to report->edit selection formula->GROUP and enter:

isnull({table.LOG_CLEAR}) and
count({table.IDENT_NO},{table.IDENT_NO}) = 1

-LB

 

[Drepso]

Thanks LB for your reply.

My report is returning records where LOG_CLEAR is null however it is also returning records where more than one UNIT_ID is associated with the IDENT_NO.

Ie. When multiple units are sharing the same IDENT_NO

I tried grouping by UNIT_ID also and
COUNT({TABLE_UNIT.UNIT_ID},{TABLE_UNIT.UNIT_ID})=1

but this returns the same result.

Is there another way?

Thanks again for your assistance.

Cheers
D

 

[lbass]

I tested my group selection formula, and it should work for your requirements. You should group on {table.IDENT_NO), NOT the unit number and use the formula:

isnull({table.LOG_CLEAR}) and
count({table.IDENT_NO},{table.IDENT_NO}) = 1

Substitute the correct table names for "table" in the formula, and the correct field name if different from the ones I used in my example. Be sure to place this in the group selection formula area (report->edit selection formula->GROUP).

This really should work.

-LB

 

[Drepso]

Thanks LB
Deleted something in my select expert which shouldn't have been there, works perfectly now.
thanks again
cheers
d