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]

 

[Tom123456]

Hi Joe,

I have a few more queries regarding this topic - I'll add them here but let me know if a new topic would be more suitable.

Firstly I've added more columns to my data set and on occasion the fields may be empty - however when the module runs to split them out certain fields fail and returns a run time error 94 - invalid use of null for some of these fields - I want the process to run even if some of the fields are blank.

It seems to accept some fields being blank but not others but i cannot work out why - is there anyway to always duplicate the field even if it's blank?

in one case Both fields are empty in source 'data' table both formatted as short text cols & defined as a string in the VBA but it only errors out if the rows are blank on the 2nd col (runs ok on first col being blank)

Cheers,
Tom

 

[Joe4]

Check the field properties in the table, to see if they are required fields and allow duplicates and zero length. You may need to adjust some of those properties to accommodate this.

 

[Tom123456]

Hi Joe,

Both the fields in the 'data' table & 'dest' table are not required fields and allow zero-length & duplicates.

Is there anything else you'd recommend checking?

Cheers,
Tom

 

[Joe4]

Without seeing the table and data, it is really hard to say. I would typically look at everything and try to work through it, using different debugging techniques (like adding lots of Message Boxes to my code to see what the SQL code looks like, etc).

 

[Tom123456]

Is it possible to attach the database here or email you the access database so that you can access a copy? It's a small file as data table contains just 1 row that i am testing with for now.

 

[Joe4]

No, you cannot upload files to this site. You would need to add them to a file sharing site, and then provide a link here to it.
Note that I do not have the ability to download internet files from my current location (corporate security policy), but I do at home. So I could probably look at it tonight or this weekend.

 

[Tom123456]

I've uploaded to this site

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

Let me know if you have issues downloading or need me to do anything to help you with troubleshooting.

 

[Joe4]

If there is a data file, be sure to upload a sample data file too.

 

[Tom123456]

I should of added the file contains 1 row in the data table and when you run the macro 'preprocess' to run the VBA it will produce the error message - will this work ?

 

[Joe4]

Unfortunately, I did not get a chance to look at this over the weekend. We had family in town, and they were staying in my computer room!
Hope to take a look at it tonight.