Return Date Range That Date Belongs In. 1

[MikeAuz1979]

Hi,

(This has so made my head hurt!)
Using Access 97 I have a table that has results like:

TRANSACTION_DATE READ_FROM_DATE READ_TO_DATE
3 Apr 2006 1 Mar 2006 31 Mar 2006
6 Apr 2006
6 Apr 2006
1 May 2006 1 Apr 2006 30 Apr 2006
{So there's some records that have a Transaction Date but no Read_From or Read_To Date}

What i'm trying to do is populate the READ_FROM and READ_TO dates for the records that have none.

So What I'm trying to acheive is:
TRANSACTION_DATE READ_FROM_DATE READ_TO_DATE
3 Apr 2006 1 Mar 2006 31 Mar 2006
6 Apr 2006 1 Apr 2006 30 Apr 2006
6 Apr 2006 1 Apr 2006 30 Apr 2006
1 May 2006 1 Apr 2006 30 Apr 2006

The background behind this is records with READ_FROM/To dates are monthly invoice amounts and the ones without READ_FROM/To are adjustments that are applied to the relevant invoice. (This is coming from a billing system so unfortunately I can't change the way the data is populated.)

I've tried to populate the READ_FROM date using the following SubQuery:

(Select [READ_FROM_DATE] from tblTable Where [TRANSACTION_DATE] BETWEEN [READ_FROM_DATE] and [READ_TO_DATE])

But it returns blank, I think because it's looking at that records READ_FROM and READ_TO date (Which is blank) rather than looking through all the READ_FROM/READ_TOs in the table.

Thanks Heaps for any help!
Mike

 

[Remou]

How about:

[tt]Iff([Read_From_Date] Is Null,DateSerial(Year([TRANSACTION_DATE]),Month([TRANSACTION_DATE]),1),[Read_From_Date])

Iff([Read_To_Date] Is Null,DateSerial(Year([TRANSACTION_DATE]),Month([TRANSACTION_DATE])+1,0),[Read_To_Date])[/tt]

 

[MikeAuz1979]

Thanks Remou! I hadn't even realised that the Read_from and Read_to dates were month start and month end. Love your work!