Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2014

    Unanswered: Query too Complex so need to convert to a Module and have no idea how to make it work


    I worked out a way to get an address to divide up using the Left, Mid and Instr function to split it from a comma separate string into separate parts. Howver due to the nature of addresses they are varying lengths and some have more lines than others so I had to build up lots of expressions in a query to do this.
    After a while I noticed than when it went onto a report, I had lots of empty rows for shorter address when printed on paper.
    As such I decided to try and reverse the address so that it would be built backwards and split up accordingly.

    Address Line 1 - Joe Bloggs Inc
    Address Line 2 - 123 ABC Road
    Address Line 3 - Def Town
    Address Line 4 - Ghi County
    Address Line 5 - JKL Country
    Postcode - MN56 7OP

    but that where there was a line less it would give me
    Address Line 1 -
    Address Line 2 - Joe Bloggs Inc
    Address Line 3 - 123 ABC Road
    Address Line 4 - Def Town
    Address Line 5 - Ghi County
    Postcode - MN56 7OP

    As such as the trick with the Left, Mid and Instr function cannot be reversed and used as a Right, Mid and Instr to peel sections from the Right I reversed the String and then used Left, Mid and Instr to give me the parts. I also had to add in IIf clauses to remove errors when the searched for " ," didn't appear in the string.
    As such I have a sequence of code that looks like this
    [JobNumber] – reference line that is the connecting factor of all information in database pulled from central table
    [FA] – Client’s Address - calculated field pulled from central table joining client name and client address together to form one string in the same comma separated format.
    RA: StrReverse([FA])
    Postcode: StrReverse(Left([RA],InStr([RA]," ,")-1))
    Expr1: Mid([RA],InStr([RA]," ,")+2)
    AD8: StrReverse(Left([Expr1],InStr([Expr1]," ,")-1))
    Expr2: Mid([Expr1],InStr([Expr1]," ,")+2)
    AD7: StrReverse(Left([Expr2],InStr([Expr2]," ,")-1))
    Expr3: IIf(InStr([Expr2]," ,")=0,Null,Mid([Expr2],InStr([Expr2]," ,")+2))
    AD6: IIf(IsNull([Expr3]),Null,IIf(InStr([Expr3]," ,")=0,StrReverse([Expr3]),StrReverse(Left([Expr3],InStr([Expr3]," ,")-1))))
    Expr4: IIf(InStr([Expr3]," ,")=0,Null,Mid([Expr3],InStr([Expr3]," ,")+2))
    AD5: IIf(IsNull([Expr4]),Null,IIf(InStr([Expr4]," ,")=0,StrReverse([Expr4]),StrReverse(Left([Expr4],InStr([Expr4]," ,")-1))))
    Expr5: IIf(InStr([Expr4]," ,")=0,Null,Mid([Expr4],InStr([Expr4]," ,")+2))
    AD4: IIf(IsNull([Expr5]),Null,IIf(InStr([Expr5]," ,")=0,StrReverse([Expr5]),StrReverse(Left([Expr5],InStr([Expr5]," ,")-1))))
    Expr6: IIf(InStr([Expr5]," ,")=0,Null,Mid([Expr5],InStr([Expr5]," ,")+2))
    AD3: IIf(IsNull([Expr6]),Null,IIf(InStr([Expr6]," ,")=0,StrReverse([Expr6]),StrReverse(Left([Expr6],InStr([Expr6]," ,")-1))))
    Expr7: IIf(InStr([Expr6]," ,")=0,Null,Mid([Expr6],InStr([Expr6]," ,")+2))
    AD2: IIf(IsNull([Expr7]),Null,IIf(InStr([Expr7]," ,")=0,StrReverse([Expr7]),StrReverse(Left([Expr7],InStr([Expr7]," ,")-1))))
    Expr8: IIf(InStr([Expr7]," ,")=0,Null,Mid([Expr7],InStr([Expr7]," ,")+2))
    AD1: IIf(IsNull([Expr8]),Null,IIf(InStr([Expr8]," ,")=0,StrReverse([Expr8]),StrReverse(Left([Expr8],InStr([Expr8]," ,")-1))))

    When I run this with the expressions up to AD4 entered it works perfectly. However, when I try and run this with all of the entries it tells me the query is too complex.
    Can anyone assist with writing a module that will do all of this and just give me the output lines Postcode and AD8-AD1 in a query or table from the source data?

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    Sounds like the original data is comma delimited? I'd consider a function that used Split() and returned however many components there were. Might help to see what the original string looks like.

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    There is a function on this site that will do what I think you want. Do a search for getelement
    Last edited by Missinglinq; 10-31-14 at 23:45.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2014
    pbaldy - I cannot show the exact strings as they are our clients data and therefore private information but I did include a typical example using the fake Joe Bloggs data in my original statement. Using a random London address off of the internet as an example would be "Waterstones Trafalgar Square, The Grand Building, Trafalgar Square, London, WC2N 5EJ" but could also be "John Lewis, Bluewater, Greenhithe, Kent, DA9 9SA" or "Next, Unit 1B, County Oak Retail Park, London Road, Crawley, West Sussex, RH11 7XN"

    I have looked at the split function but because of the number of potential spaces it doesn't seem to be something I can use and hasn't worked when I tried it originally as it didn't seem to be able to use the commas to split it with the extra spaces involved but if someone can advise on this that would be helpful.

    healdem - I have read this post
    How would I program this in to work either directly into a report or into a query so that it outputs as a number of fields within the query. I have not done any real programming so all of the work I have done on my database has been done through Expressions in Queries rather than coding it in using modules or similar?

  5. #5
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    its a VBA function
    it can be used as suggested in a query, a form or report

    if you 'must'have a specific format (ie tack on a postcode of the last line, then I'd suggest doing that in the forms DETAIL on format event. to do that use unbound controls, called say tbaddr1...tbaddr5, for the address lines, and then allocate lines as required
    tbaddr1 = getlement(mycolumn,1)
    tbaddr2 = getlement(mycolumn,2)
    ,..and so on
    Last edited by healdem; 11-03-14 at 08:29.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2014
    Thanks for that Healdem.
    How do I control where the output is placed.
    I would need to input 8 lines so tbaddr1 through 8 and would be looking at the address from the following table. [Job Numbers Delta]![FA]
    If I was to output the lines to a query how do I tell it to do this?
    Sorry for what probably seems like a simple question but as I said above I have not sued VBA before and when I have looked at it I have used it to pull ideas to enter as basic calculations in expressions as stepped logic.

Posting Permissions

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