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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Booking[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]Booking 1[/TD]
[TD]01/08/2017[/TD]
[TD]30/11/2017[/TD]
[/TR]
[TR]
[TD]Booking 1[/TD]
[TD]01/10/2017[/TD]
[TD]30/11/2017[/TD]
[/TR]
[TR]
[TD]Booking 2[/TD]
[TD]11/08/2017[/TD]
[TD]30/09/2017[/TD]
[/TR]
[TR]
[TD]Booking 2[/TD]
[TD]11/08/2017[/TD]
[TD]30/09/2017[/TD]
[/TR]
[TR]
[TD]Booking 3[/TD]
[TD]07/08/2017[/TD]
[TD]31/08/2017[/TD]
[/TR]
</tbody>[/TABLE]










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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Booking[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]Booking 1[/TD]
[TD]01/08/2017[/TD]
[TD]31/08/2017[/TD]
[/TR]
[TR]
[TD]Booking 1[/TD]
[TD]01/09/2017[/TD]
[TD]30/09/2017[/TD]
[/TR]
[TR]
[TD]Booking 2[/TD]
[TD]11/08/2017[/TD]
[TD]31/08/2017[/TD]
[/TR]
[TR]
[TD]Booking 2[/TD]
[TD]01/09/2017[/TD]
[TD]30/09/2017[/TD]
[/TR]
[TR]
[TD]Booking 2[/TD]
[TD]11/08/2017[/TD]
[TD]31/08/2017[/TD]
[/TR]
[TR]
[TD]Booking 2[/TD]
[TD]01/09/2017[/TD]
[TD]30/09/2017[/TD]
[/TR]
[TR]
[TD]Booking 3[/TD]
[TD]07/08/2017[/TD]
[TD]31/08/2017[/TD]
[/TR]
</tbody>[/TABLE]













[TABLE="width: 416"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD]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[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[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.