Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    20

    Unanswered: Looping in Access

    Hello All,
    I have a table that comes in this format:
    PartID Location No of Parts
    A 01010201000000 2
    B 02020203020400 3
    Location: first 2 numbers is the row, and next 2 is the box where the part is: ex: 01= row, 02= box. 00 means null.
    I'm trying to write a query that will separate these fields so that I have a table that reads:
    PartID Row Box
    A 01 01
    A 01 02
    B 02 02 etc
    Can anyone help with this? Please!!! I think I need to write some kind of loop but it does not seem to be working for me.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59

    Looping in Access

    You could create a loop construct using VBA to iterate through your rows in the table. But taking a step out, what are you trying to achieve? Is the intention to split out on data capture the shelf / box element or are you decoding for report / display purposes. If so you could achieive this using a fragment of VBA attached to the text box that contains the original value. Depending on how you wanted to force the split it could be on "lost focus", "before update", "current" events.

    You can split a source varaible into component parts using left$(<var>,<nochars>) to isolate the first <nochars> characters,
    mid$(<var>,<start>,<nochars>) to isolate the first <nochars> starting from <start>. The <nochars> is optional, if not specified its the remainder of the variable
    There is also right$(<var>,<nochars>) to isolate the first <nochars> form the right of <var>.
    eg if strVar contained "63MA0009" you could split this into "63","MA" & "0009"
    with
    strPrefix=left$(strVar,2)
    strElement=mid$(strVar,3,2)
    strSuffix=mid$(strVar,4)

    Note you would need to do some range checking to ensure strVar was long enough. Or is you were really nasty set all 3 variables (strPrefix,StrElement, StrSuffix to null), turn off error checking befiore the call to the Left$ (eg "on error resume next", remembering to reset after the last mid$ by using "on error goto 0) and let the process run. OK I did say it was nasty.

    If you already have the elements in your table you could split htem directly using a make table query or an update query (having created the required columns first!)

    "SELECT Left$([unitcode],2) AS strPrefix, Mid$([unitcode],3,2) AS strElement, Mid$([unitcode],4) AS strSuffix, tblUnits.UnitCode
    FROM tblUnits;" - this will acheive the same effect as the VBA above. Its not the most elegant solution but it works.

    unitcode is a column in an exsiting table here called tblUnits you would need to replace the column, table and alias names as required locally

    HTH, if it didn't perhaps you could provide a little more detail as top what you are trying to do, ie is it a one off fix, or a permanent solution you are looking for
    Matthew
    Last edited by healdem; 11-22-04 at 11:38.

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by Dbgirl
    Hello All,
    I have a table that comes in this format:
    PartID Location No of Parts
    A 01010201000000 2
    B 02020203020400 3
    Location: first 2 numbers is the row, and next 2 is the box where the part is: ex: 01= row, 02= box. 00 means null.
    I'm trying to write a query that will separate these fields so that I have a table that reads:
    PartID Row Box
    A 01 01
    A 01 02
    B 02 02 etc
    Can anyone help with this? Please!!! I think I need to write some kind of loop but it does not seem to be working for me.
    Hi Dbgirl,
    Just to check something out here, how is the Location placed into the database? Seems a LocationTable would be nice as you could set up all the possible Rows, Boxes, etc. then have that selected from a ComboBox. You can then have it Concatenated for display purposes in another location of your choosing. That way, if you ever wanted to find out what all you had in Row "03" you could easily do a query on that field. At least that would be how I would do it as I had something like that in one of the Inventory Databases I designed and it worked well.
    Being as you have it setup this way already healdem has given you one possible solution. That above would be something to keep in mind when designing your Tables going forward, just to keep from having too much work at a later date.

    hope you have been helped though and
    have a nice one,
    BUD

Posting Permissions

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