Returning value before a null

[cathyc]

I posted a question re a query a few days ago, and have received no replies; to put it in more simply terms:

I have two tables RDOTOWN and CRISPMON which are connected by Project name in a one-to-many relationship

RDOTOWN holds project info and CRISPMON holds monitoring info for each project by YEAR_NO. Some projects have only 1 year returned and others can have anything up to 10 years returned.

I am trying to set up a query to return the current jobs (TOTJOBS_AC), current new jobs (NEWJOBS_AC) and new businesses (TOT_BUSIN2) for each project. This info is held in the CRISPMON table, and some years will have null values for some of the fields.

The formula I require needs to return the value before a null. It also needs to be able to keep the YEAR_NO's in order so that the last figure can be returned.

I hope someone can help.

 

[ColinEssex]

Hi Cathy

Are you saying you need to have a "null to zero" function.? If so, look up NZ in Access help.

(I bet its not though)

Col

 

[cathyc]

I'm afraid not Colin, if I change the null to a zero, a zero will be returned for the last figure, (current job, new job or new business).

I need a formula or code that will allow me to return the last value before a null, whilst also ensuring that the YEAR_NO's are kept in order so the last value is indeed the last value if you catch my drift.

 

[ColinEssex]

Hmmm - maybe you might need an IIf statement. I'll need to think about it, I'm not to good at doing stuff "on the fly"

It'll probably be

IIf(IsNull([FieldName],SomethingHereToGetLastNotNullVal,[FieldName]))

In the meantime if anyone else has an idea?...


Col

 

[Rich]

Why not just exclude the Null values and order descending?

 

[cathyc]

Returning value before null

Thanks Rich

I knew there would be a simple solution, I have fiddled about with the query and got it to return the right results.