how to split a row into two if the date extends into the next month

[greekpatriott]

is it possible to split a row of a table or query into two rows? This is a mathematical sort of question.
for example:
[startdate][enddate] [value][Duration]
18/01/04 04/02/04 100 18


To be changed to this or something similar:
[startdate][enddate] [value] [Duration]
18/01/04 31/01/04 77.8 14
30/02/04 04/02/04 22.2 4

Cheers. I think it is a difficult question.

 

[greekpatriott]

sorry 30/02/04 should be 01/02/04

 

[greekpatriott]

for example if I set another field as [endofstartdate]: DateSerial(Year([control start]),1+Month([control start]),0)
and another field as [Datedifference]: [control end]-[endofstartdate]

if the [datedifference] is negative then it would activate a module/code to split the cell into two somehow. Maybe this is helpfull. Cheers

 

[greekpatriott]

Sorry the previous threat should be corrected to this.
for example if I set another field as [endofstartdate]: DateSerial(Year([startdate]),1+Month([startdate]),0)
and another field as [Datedifference]: [endofstartdate]-[
enddate]
if the [datedifference] is negative then it would activate a module/code to split the row into two somehow. Maybe this is helpfull. Cheers

 

[greekpatriott]

maybe the calculations can be performed when running a macro which opens an update query or an append query.