Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: Help solving a complex problem

    I have a table called Control_Id.

    In the table, I have three fields.

    Control_ID
    BegBates
    EndBates

    An example record would be as follows:

    Control_ID =00000001
    BegBates = DB 000001
    EndBates = DB 000007

    Somehow, I need to make new records for this one record. I really could care less about the Control_ID field, since the data is useless.

    I need the end results to be as follows, pulling all the BegBates and EndBates into the table for this record.
    DB 000001
    DB 000002
    DB 000003
    DB 000004
    DB 000005
    DB 000006
    DB 000007

    Please help!

    Thanks!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    does this do it?

    in DAO:

    dim dabs as dao.database
    dim reci as dao.recordset
    dim reco as dao.recordset
    dim loup as integer
    dim intMin as integer
    dim intMax as integer
    dim strPart as string

    set dabs = currentdb
    set reci = dabs.openrecordset("SELECT * FROM yourInputTable")
    set reco = dabs.openrecordset ("SELECT * FROM yourOutputTable")
    with reci
    do while not .eof

    strPart = left$(!BegBates, 3)
    intMin = cint(right$(!BegBates, 6))
    intMax = cint(right$(!EndBates, 6))

    for loup = intMin to intMax
    strPart = strPart & format$(loup, "000000")
    'i forget the syntax for the formatting string - check it out in help

    reco.addnew
    !aField = strPart 'aField should be the name of the destination field
    reco.update
    next

    .movenext

    loop
    end with
    set reci = nothing
    set reco = nothing
    set dabs = nothing

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2004
    Posts
    2

    Error

    I get an overflow error on this part...

    strPart = left$(!BegBates, 3)
    intMin = cint(right$(!BegBates, 6))
    intMax = cint(right$(!EndBates, 6))

    Thanks!

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    silly me!
    you have six digits ...they don't sit happily in an int (32,xxx max)

    dim intMin as long
    dim intMax as long

    should fix the overflow (and possibly expose other errors in my code)

    izy

    LATER: but intMin is no longer an "int" so maybe you should change it's name to lngMin (ditto lngMax etc) so you dont get lost when you review the code 5 years from now.
    Last edited by izyrider; 03-23-04 at 16:02.
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry - that answer was incomplete:

    dim dabs as dao.database
    dim reci as dao.recordset
    dim reco as dao.recordset
    dim loup as long
    dim lngMin as long
    dim lngMax as long
    dim strPart as string

    set dabs = currentdb
    set reci = dabs.openrecordset("SELECT * FROM yourInputTable")
    set reco = dabs.openrecordset ("SELECT * FROM yourOutputTable")
    with reci
    do while not .eof

    strPart = left$(!BegBates, 3)
    lngMin = clng(right$(!BegBates, 6))
    lngNax= clng(right$(!EndBates, 6))

    for loup = lngMin to lngMax
    strPart = strPart & format$(loup, "000000")
    'i forget the syntax for the formatting string - check it out in help

    reco.addnew
    !aField = strPart 'aField should be the name of the destination field
    reco.update
    next

    .movenext

    loop
    end with
    set reci = nothing
    set reco = nothing
    set dabs = nothing
    currently using SS 2008R2

Posting Permissions

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