Field w/No matching Record is not "Null" nor ?evaluat-able?

[5lights]

I have two tables linked appropriately to return all records from the Primary(Table A)& just matching from Secondary(Table B). I gives me exactly the records I need.
But, now I need to evaluate based on the field(singular) that I am pulling from Table B. since its niether NULL nor valued, I cant use a formula to do anything.....Nor can I get EXISTS to be recognized in a formula.....suggestions?

 

[synapsevampire]

Need to evaluate what from this field?

I suggest that you use concrete examples rather than descriptions.

Show example data and the expected output, and include basioc environment information, such as your CR version and the database used.

-k

 

[5lights]

CRv11

Table A(ie Sked) has DATE, TIME, NAME, POSITION, etc
Table B(ie Clock)has DATE, TIME, NAME, etc

FROM "SKED" LEFT OUTER JOIN "CLOCK" ON ("SKED"."DATE"="CLOCK"."DATE") AND ("SKED"."NAME"="CLOCK"."NAME")

Now I want to evaluate person for "OnTime", "Late", or "Not ClockedIn"

I can tell 'OnTime' & 'Late', but since no record in CLOCK when "Not ClockedIn" ....I cant put in a formula like:
If SKED.TIME >= CLOCK.TIME then 'OnTime'
else If SKED.TIME < CLOCK.TIME then 'Late'
lse if IsNull(CLOCK.TIME) then 'NoCheckIn'
else 'NoCheckIn'

 

[synapsevampire]

I think that's the formula to be used, because the join is a LEFT OUTER you will get a row with null from the clock table for those dates with no check in time.

That's what a left outer does, it states return all rows from the ked table, and any rows that match in the clock table.

Unless you're placing some where clause on the clock table. If so, also reverse the logic in the selection criteria to allow for nulls as well, then you should always get a row from the sked table.

-k