Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: Incrementing complex number

    Hello again..

    Todays headbanger.

    I have job numbers
    10000/a
    10000/b
    10001/a
    10002/a

    used to reference a record. I would like to create a new number starting with the next highest number followed by /a

    I have tried using the Dmax function in VBA which works fine if i am only using numbers ie 10000 increments to 10001 etc. I can't get it working on complex numbers.

    This is what i think i need to do....

    Strip off all the /a or /b from all the records in my table called Jobs. Find the highest number. increment it by 1 (using Dmax) , then append /a to the result. Sound straight forward but i'm struggling quite a bit.

    Any pointers?
    thanks
    marcus

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Create a structure, or even better: a class.
    Have a nice day!

  3. #3
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    If it's always going to be just a "/a" or "/b" at the end you could use something like:

    Left(CompNumber, Len(CompNumber) - 2). I generally use recordsets vs DMax so I'm not sure on the syntax but, I would think:

    Dim StrippedNumber as Integer
    Dim NewCompNumber as String

    StrippedNumber = DMax(Left(CompNumber, Len(CompNumber) - 2), CompNumberTable)

    StrippedNumber = StrippedNumber + 1

    NewCompNumber = CStr(StrippedNumber) &"/a"


    Sam, hth

    Edit: D'oh! Beaten to the punch...that's what I get for opening all the threads before I start reading them.
    Good, fast, cheap...Pick 2.

  4. #4
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks for taking a look.

    I'm really not familiar with classes.

    I tried to put the code Sam sent but it's throwing up an error

    I've only just tried it so it may need tweeking. I'll have another stab at it

    It doesn't seem to be pulling the highest number. It's trying to pull 10000/a but i have other jobs with higher numbers.
    thanks
    Marcus
    Last edited by marcusmacman; 10-27-10 at 11:01.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...and the error is?
    it does help if you include any error messages, its difficult, if not impossible, to diagnose probelms when you say things like 'its throwing up an error'
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    ok apologies

    Run-time error 2428

    You entered an invalid argument in a domain aggregate function

  7. #7
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Ok, it seems DMax doesn't allow use of functions within it...so personally I'd use a recordset.

    Would look something like this:

    Code:
    Dim db as DAO.Database
    Dim rs as DAO.Recordset
    Dim strSQL as String
    Dim NewNum as Integer
    Dim NewCompNum as String
    
    strSQL = "SELECT DISTINCT Left(ComplexField,Len(ComplexField) - 2) AS CompNum " _
    &"FROM tblTable " _
    &"ORDER BY Left(ComplexField, Len(ComplexField) - 2);"
    
    Set db = CurrentDb
    Set rs = Db.OpenRecordset(strSQL)
    
    if not rs.bof and not rs.eof then
    rs.movelast
    NewNum = CInt(rs!CompNum)
    endif
    
    rs.Close
    db.Close
    
    Set rs = Nothing
    Set db = Nothing
    
    NewNum = NewNum + 1
    
    NewCompNum = NewNum &"/a"
    I would assume it wouldn't matter if you used ADO or DAO, but, I just tested something like this using DAO and it worked well.

    Sam, hth
    Good, fast, cheap...Pick 2.

  8. #8
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    ok that's pretty impressive. It's just taken me 1 hour just to get the new value into the table!!!

    I'll probably go back at some point to try and understand the code a bit better as i'm not that familiar with VBA.

    thanks again for your help sam
    regards
    marcus

Posting Permissions

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