Hi OBP,
Thanks for looking into this - attached is the database with the new columns i added.
The structure of the input & output tables will always be identical if this makes the VBA any easier to implement.
Cheers,
Tom
Hi OBP,
Thanks for looking into this - attached is the database with the new columns i added.
The structure of the input & output tables will always be identical if this makes the VBA any easier to implement.
Cheers,
Tom
OBP Example.zip test
Your new Desttable has a problem, installment 6 is in the wrong order.
Hi OBP,
Thanks - I've fixed the column order but it's still returning errors (attached) when i run the module and i can't work out how to amend to fix.
I tried adding in the new columns in the VBA then adding to the 34/35 values by the number of columns added but it didn't work - sorry if i'm missing something obvious, don't use VBA like this often.
Cheers,
Tom
The zip contains a link and not the database.
Sorry - this should be it.
Edit: Just seen this example is missing the following columns
Installment Total
Installment Amount
Installment Posting Date
where the new data should be inputted but is in my orignal where i'm also getting errors.
So does this version contain what you need?
That does not contain the latest VBA code that I posted, that has the original Field by Field version.
OK, the reason it wsn't working is because you changed the names of the Installment and installment date fields.
Ther attached version is now working, I have added Macro1 into this database to run the VBA code.
Hi OBP,
Thanks for updating however the attached version doesn't split the installment posting date & amount into the new field as it did in your previous example.
In the attached I've run your new macro which splits the rows but it needs to to insert the date/amount values into the 'installment posting date' & 'installment amount' fields - how can this be changed?
Thanks again for your help
Cheers,
Tom
This version works ok.
Hi OBP,
That version isnt putting it in the right column sorry - if you look at the access db in post 30 it should put the installment date & value for that row in the 'Installment Posting Date' & 'Installment amount' field
Tom
Well if the tables you provided actually had those fields in it I might be able to put them in there.
It is why the original ones you sent me did not work, because those fields were not in place.
Have you added them in the V1.zip database?
ps I see that you have, but that is a totally different table to the ones I have, not only are those 2 fields missing from the originals, but the Number of installments is in a completely different place.
Sorry for the confusion - the file i originally sent was incorrect, the file in post 30 is the correct version and does include those columns that need to be populated as well as the new columns i need that caused your original solution to error out.
OK, I will look at the Code change required to work with the V1 version.
ps you have also lost the currency type in both tables for the later installment amounts in the V1 version.
Try this one.
Looks like it's working thanks!
I'll append 'output' into another table after this module has ran for any new columns i want to add etc!
Thanks once more for all your help and patience!
You're welcome.
Hi OBP,
Hope your well - I've been using this VBA as part of my project but i get an error if the installment text field is not in GBP ie a value of £7500 works but $7500 doesn't. I've had a look at the VBA as well as a few google search but cant work out why it would treat it differently in this case
Do you know how i could go about fixing this or any links to references that would help me solve this issue?
Thanks,
Tom
access error.png
What is the data type of the2 fields in question, they should be Currency, or at least Number to 2 decimal places?