asked on
A very complicated VBA challenge
I really hope someone can help me with this one.
On a form I have a start date and an end date. I also have a field named txtDailyInvAmount on the form and I need to do the following:
Write records to a table named tblBuyerInvoiceDetails.
If the start date is 1/1/2016 and the end date is 3/31/2016 then I need 3 records written to the table, one for each 1st of the month following the start date. The first record needs to be written to have a field named InvoiceAmount be equal to the number of days elapsed between 1/1/2016 and 1/31/2016, in other words, the entire month of January (31 days) times another field value I have on the form named txtDailyInvAmount and have a field in the table being written to be 2/1/2016 (the 1st of the month following the start date.
Then I also need another record written to the table with the number of days in February (29 this year) times the txtDailyInvAmount and have a field in the table being written to be 3/1/2016 (the 1st of the month following the previous date of 2/1/2016.
And then since the end date is 3/31/2016, I need a 3rd record written to the table with the number of days in March (31 days) times the txtDailyInvAmount and have a field in the table being written to be 4/1/2016 (the 1st of the month following the previous date of 3/1/2016.
The value of txtDailyInvAmount in the following example is $52.5556 (4 decimals intentionaly)
Using the above example here is an snapshot of what I need in the table:
![Example-1.bmp]()
But if for example the start date was 1/15/2016 and the end date was 3/25/2016 then the result would have to be:
![Example-2.bmp]()
Is this possible? Somehow the function has to loop and create the records in the table.
On a form I have a start date and an end date. I also have a field named txtDailyInvAmount on the form and I need to do the following:
Write records to a table named tblBuyerInvoiceDetails.
If the start date is 1/1/2016 and the end date is 3/31/2016 then I need 3 records written to the table, one for each 1st of the month following the start date. The first record needs to be written to have a field named InvoiceAmount be equal to the number of days elapsed between 1/1/2016 and 1/31/2016, in other words, the entire month of January (31 days) times another field value I have on the form named txtDailyInvAmount and have a field in the table being written to be 2/1/2016 (the 1st of the month following the start date.
Then I also need another record written to the table with the number of days in February (29 this year) times the txtDailyInvAmount and have a field in the table being written to be 3/1/2016 (the 1st of the month following the previous date of 2/1/2016.
And then since the end date is 3/31/2016, I need a 3rd record written to the table with the number of days in March (31 days) times the txtDailyInvAmount and have a field in the table being written to be 4/1/2016 (the 1st of the month following the previous date of 3/1/2016.
The value of txtDailyInvAmount in the following example is $52.5556 (4 decimals intentionaly)
Using the above example here is an snapshot of what I need in the table:
But if for example the start date was 1/15/2016 and the end date was 3/25/2016 then the result would have to be:
Is this possible? Somehow the function has to loop and create the records in the table.
Microsoft AccessVBA
Last Comment
Gustav Brock