Results 1 to 8 of 8

Thread: cut off digits

  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a table called table2 and texts in the field "grade".I want through a code to cut the last digits from the row and paste them in the other field called "price" For for example in the row
    Pento Super Performance III 5W-30 12 x 1 L 3,74
    Pento Super Performance III 5W-30 3 x 5 L 17,60
    to cut the digit 3,74 or 17,60 and paste them in the field price.And after that to delete the letter L. i tried the following:

    Dim StrSQL As String
    StrSQL = "UPDATE table2 SET table2.price = Mid([grade],InStr([grade],'L')+2), table2.grade =

    Left([grade],InStr([grade],'L')-2)"
    CurrentDb.Execute StrSQL
    End Sub


    But i get the error too few parameters
    Can you help me ?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your table doesn't contain a field named price. Before running the code, create a field price of type Currency in the table.

    PS it would be nice if you provided some feedback to the replies you receive. Otherwise people reading your questions don't know whether they have been solved.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794041' date='19-Sep-2009 15:31']Your table doesn't contain a field named price. Before running the code, create a field price of type Currency in the table.

    PS it would be nice if you provided some feedback to the replies you receive. Otherwise people reading your questions don't know whether they have been solved.[/quote]



    Yes! It works now ! Thank you ! I have forgotten to create the field price !! But now it is OK !
    I wonder could i also perfom the following action.At the end of the row you can see now digits like 12 x 1 or 3 x 5
    Is it possible to send the first digits, say 12 or 3 in the newly created field called "pack", while the last ones, like 1

    or 5 to paste on the newly created field "pack" and after that delete the x ?

    Pento Super Performance III 5W-30 12 x 1
    Pento Super Performance III 5W-30 3 x 5

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can't store both values in the same field 'pack' - you should use two different fields, e.g. 'pack' and 'size'.

    You could use code like this:

    To extract the last number (after the 'x'):

    StrSQL = "UPDATE table2 SET pack = Mid(grade,InStr(grade,' x ')+3), grade = Left(grade,InStr(grade,' x ')-1)"
    CurrentDb.Execute StrSQL


    To extract the number before the 'x':

    StrSQL = "UPDATE table2 SET size = Mid(grade,InStrRev(grade,' ')+1), grade = Left(grade,InStrRev(grade,' ')-1)"
    CurrentDb.Execute StrSQL

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794051' date='19-Sep-2009 16:29']You can't store both values in the same field 'pack' - you should use two different fields, e.g. 'pack' and 'size'.

    You could use code like this:

    To extract the last number (after the 'x'):

    StrSQL = "UPDATE table2 SET pack = Mid(grade,InStr(grade,' x ')+3), grade = Left(grade,InStr(grade,' x ')-1)"
    CurrentDb.Execute StrSQL


    To extract the number before the 'x':

    StrSQL = "UPDATE table2 SET size = Mid(grade,InStrRev(grade,' ')+1), grade = Left(grade,InStrRev(grade,' ')-1)"
    CurrentDb.Execute StrSQL
    [/quote]


    It works! thank you !

  6. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='freelance' post='794058' date='19-Sep-2009 16:12']It works! thank you ![/quote]


    As i have written, the code

    StrSQL = "UPDATE table2 SET table2.price = Mid([grade],InStr([grade],'L')+2), table2.grade =

    Left([grade],InStr([grade],'L')-2)"

    works excellent,but only in case there is no sign of the euro at the end of the sentence.However,i receive the
    texts with the euro sign at the end and in that case the code is not working.I have to delete the sign euro by hand and it takes a lot of time.For example:
    Pento Super Performance III 5W-30 12 x 1 L 3,74
    Is there any possiblity to rewrite the code so that to work with the euro sign present ?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You don't have to remove the Euro sign by hand:
    - Open table2 in datasheet view.
    - Click in the grade column.
    - Select Edit | Replace...
    - Enter a space followed by the Euro sign in the Find What box (i.e. " " without the quotes).
    - Leave the Replace With box empty.
    - Make sure that "Any part of field" is selected in the Match dropdown list.
    - Click Replace All.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794323' date='21-Sep-2009 19:34']You don't have to remove the Euro sign by hand:
    - Open table2 in datasheet view.
    - Click in the grade column.
    - Select Edit | Replace...
    - Enter a space followed by the Euro sign in the Find What box (i.e. " " without the quotes).
    - Leave the Replace With box empty.
    - Make sure that "Any part of field" is selected in the Match dropdown list.
    - Click Replace All.[/quote]


    I have done it following your advices and i have sucessfully deleted the euro sign.Thank you very much !

Posting Permissions

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