Splitting rows in a table based on date fields.

[Tom123456]

Hey,

I have a list of bookings that have a start & end date and a amount.

I need to split the table so that each month has it's own line and % amount

For example before it would look like this.

Booking	Start Date	End Date
Booking 1	01/08/2017	30/11/2017
Booking 1	01/10/2017	30/11/2017
Booking 2	11/08/2017	30/09/2017
Booking 2	11/08/2017	30/09/2017
Booking 3	07/08/2017	31/08/2017

<tbody>
</tbody>

but i need to work out how to split it to become like so

Booking	Start Date	End Date
Booking 1	01/08/2017	31/08/2017
Booking 1	01/09/2017	30/09/2017
Booking 2	11/08/2017	31/08/2017
Booking 2	01/09/2017	30/09/2017
Booking 2	11/08/2017	31/08/2017
Booking 2	01/09/2017	30/09/2017
Booking 3	07/08/2017	31/08/2017

<tbody>
</tbody>

I've added columns that calculates the number of days/month in each row and the amount per day but don't know where to start with using this information to split like this- if anyone can point me in the right direction it would be a great help! Cheers, Tom

<tbody>
</tbody>

 

[Joe4]

I've uploaded to this site

https://sabercathost.com/dpPG/Preprocess_Main_v3.accdb

Hmm... it looks like this site requires me to install some software on machine. I am sorry, but I am not willing to install new software on my computer just to download a file.

Many people use sites which do not require the users to do that, like "dropbox". If you put it on one of those sites, I would be willing to download it.

 

[Tom123456]

I've uploaded it to Dropbox - let me know if it works.

https://www.dropbox.com/s/3ypy8k74mco9lwp/Preprocess Main v3.accdb?dl=0

 

[Joe4]

The good news is that I was able to download your database. The bad news is that I am unable to do much with it, as it looks like it is Access 2010 and I am using Access 2007. One of things I cannot do is open/access the Data table. Unfortunately, I don't know that I am going to be able to help any further.

 

[stumac]

if you wrap the values you are trying to assign to your variable in the NZ function this should avoid this error.

i.e.

NumOfInstalments = [COLOR=#B22222]Nz([/COLOR]rst![Number of Installment Invoices][COLOR=#B22222])[/COLOR]

I would probably do this for all of the values if they are likely to be null sometimes.