Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    6

    Arrow Unanswered: Combining sring and number

    Hi!
    Can u help me how to combine string and number?
    Ex.
    CommodityType ProductNo ItemCode
    MED 0001 --> MED-0001

    AND HOW TO INCREMENT EACH PRODUCT NUMBER BASED ON ITS COMMODITY TYPE?

    RecordNo ComID ItemCode Nomenclature Description Brand
    1 AGR AGR-0001 Dog Food For Canine Dogs Alpo
    2 AGR AGR-0002 Hog Feeds Pre-Starter Purina
    3 AGR AGR-0003 Molasses For Horse Generic
    4 ITS ITS-0001 Hard Disk 40 GB Seagate
    5 ITS ITS-0002 Memory Module 512 MB Generic
    6 AGR AGR-0004 Dewormer For Piglets B-Meg
    7 MED MED-0001 Paracetamol Tablet, 250 mg Decolgen


    pls?
    thanx

  2. #2
    Join Date
    Feb 2005
    Posts
    333
    I've done something similar but it got pretty ugly. I'm going to paste in the entire routine so that someone else can tell me how to clean things up.

    To make this work I had to place a list box on the main form to hold the result of the query (Max Category Junior/Max Category Senior) that found the maximum value of a particular category. I hid this so that it was not visible to the end user.

    This my be confusing wihtout seeing the resr of the forms.code but it may be helpful.


    Code:
    Private Sub Category_AfterUpdate()
    On Error GoTo Err_AfterUpdate
        
        Me.Refresh
        'This rowsource is for the hidden list box.  Because the source is derived from a
        'make-table query i had to set it to nothing or I would get errors beacuse I was
        'trying to delete a table that was being used as a rowsource.
        Me.hiddenCatNum.RowSource = " "
        
        'turn off warnings that will occur with the make-table query
        DoCmd.SetWarnings (False)
        
        'I don't really like this but I had to use two different queries to make this work
        If Me.Division = "Junior" Then
            DoCmd.OpenQuery "Max Category Junior"
        Else
            DoCmd.OpenQuery "Max Category Senior"
        End If
        
        'Make the rowsource of the hidden list box the result of the make-table query.
        Me.hiddenCatNum.RowSource = "SELECT TOP 1 [maxCatNum].[Max] FROM maxCatNum;"
        'turn warnings back on
        DoCmd.SetWarnings (True)
        
        ' select the only value in the hidden list box
        [Forms]![Registration Form]![hiddenCatNum].Selected(0) = True
        'store this value in a variable
        temp = [Forms]![Registration Form]!hiddenCatNum.Column(0)
        
        'increment the value and send to Category Number text box
        Me.txtCategoryNumber = temp + 1
        'I really don'nt know why I had to do this.  If I did not force an undo the
        'Registration Form would not release the focus from the Category combo box
        Me.Undo
        
        'Add a base number if no records exist in the selected category
        If IsNull(temp) Or temp = 0 Then
            If Me.Division = "Junior" Then
                Me.txtCategoryNumber = Me.Category + 1
            Else
                Me.txtCategoryNumber = Me.Category + 2001
            End If
        Else
            'increment the value and send to Category Number text box (again)
            Me.txtCategoryNumber = temp + 1
        End If
        
    Exit_AfterUpdate:
        Exit Sub
    
    Err_AfterUpdate:
        MsgBox Err.Description
        Resume Exit_AfterUpdate
                 
    End Sub

  3. #3
    Join Date
    Jun 2004
    Posts
    92
    Quote Originally Posted by nicklaus
    Hi!
    Can u help me how to combine string and number?
    Ex.
    CommodityType ProductNo ItemCode
    MED 0001 --> MED-0001
    To combine (concatenate) do the following:

    This is assuming in your table you have the columns CommodityType, ProductNo. You don't need the Itemcode column as it will be created in a query.

    Create a query based on this table and add the columns above. In the next blank column for Itemcode change the field value to:
    Code:
    Itemcode : [CommodityType] & "-" & [ProductNo]
    This will show correctly, so just set your forms to use this query instead of directly accessing table.

    Your going to have to explain more what you mean by "AND HOW TO INCREMENT EACH PRODUCT NUMBER BASED ON ITS COMMODITY TYPE?"

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Assuming your data is coming from a table called tblData, try this Query. [But based on yout table it looks like you have (or should have) at least two other tables (one for ComIDs, one for Brands), so this data you have shown is probably based on a query.]

    SELECT ComID, Description, RecordNo, val(DCount("[ComID]","tblData","ComID = '" & ComID & "' and RecordNo <= " & RecordNo)) AS RunningCount, (ComID & "-" & RunningCount) AS myItemCode
    FROM tblData;


    This query will increment myItemCode by one for each record with the same ComID. It also increments it based on the order of RecordNo. So if you delete a record, the count will change.

    I suggest using this to calculate for all existing items. Then use a similiar query that uses DMax when adding new records to calculated the next increment of the number after ComID. But if you happen to delete the record that was assigned the last number, a query will only use that number again.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  5. #5
    Join Date
    Feb 2005
    Posts
    6

    thanks.... leading zeros before number????

    Hey!
    thanks for the codes...
    It did work!
    thanks again...

    as for adding up leading zeros to the item code? How do i do that?
    from HDW-1 TO HDW-0001

    HDW-0001 HERE IS THE RESULT OF THE QUERY CONCATENATING HDW AND 1
    THANKS AGAIN....

    GOD BLESS U ALL!!!

    nicklaus...

Posting Permissions

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