Consulting

Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 21 to 40 of 49

Thread: VBA to split lines based on fields in table

  1. #21
    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
    Attached Files Attached Files

  2. #22

  3. #23
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Your new Desttable has a problem, installment 6 is in the wrong order.

  4. #24
    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
    Attached Files Attached Files

  5. #25
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    The zip contains a link and not the database.

  6. #26

    .

    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.
    Attached Files Attached Files

  7. #27
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    So does this version contain what you need?

  8. #28
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    That does not contain the latest VBA code that I posted, that has the original Field by Field version.

  9. #29
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.
    Attached Files Attached Files

  10. #30
    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
    Attached Files Attached Files

  11. #31
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    This version works ok.
    Attached Files Attached Files

  12. #32
    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

  13. #33
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  14. #34
    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.

  15. #35
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  16. #36
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Try this one.
    Attached Files Attached Files

  17. #37
    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!

  18. #38
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    You're welcome.

  19. #39
    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

  20. #40
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    What is the data type of the2 fields in question, they should be Currency, or at least Number to 2 decimal places?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •