Convert Date Range into Months - Data Split

Hi All

I'm having issues with trying automate splitting of data into months.

I have set up a data entry form that allows the user to fill in a StartDate, EndDate and Electricity consumption into unbound textboxes. The user then clicks a save button which writes this data to a table.

What I want to be able to do is if the user enters a record that covers several months split the data into individual months and save multiple records to the table.

Here is an example:

Input:
StartDate, EndDate, Electricity
23/01/2010, 24/06/2010, 1300

Output:
StartDate, EndDate, Electricity
23/01/2010, 31/01/2010, 76
01/02/2010, 28/02,2010, 238
01/03/2010, 31/03/2010, 263.5
01/04/2010, 30/04/2010, 255
01/05/2010, 31/05/2010, 263.5
01/06/2010, 24/06/2010, 204

I want the above to be written to the table when the the save button is clicked.

I also need this to be universal eg it will need to cater for data entries that are over multiple years ie 21/03/2008 to 10/05/2010.

Any help would be much appreciated

Cheers

You should not save calculated values. The value can always be gotten when you perform the operation in a query, so don't save it.

Look into the DateDiff() function.