Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    23

    Unanswered: Remove any letters in a column -use Update Query? VBA?

    I have a column named "Material" in my table and I want to remove any letters that are found in the “Material” so that it would looks like in table 2.

    I can use Edit\Replace with...Match any part of Field, but I must use a query to do the job of removing the Letters at end of the values because I want to use RunMacro to open all my other saved queries in sequence.

    Is there a way to replace the letters by using standard query? Update query ? or using a VBA code ?
    Would someone be nice and help me with this. Thanks a lot in advance.

    [original table 1]
    Material
    1009102
    67730W
    0100-09106B
    0020-09107
    0100-09107W
    32000456612
    5100-99088S
    0100-09109
    0100-09110A
    0100-09113W
    50414709000W


    [desired final table 2]
    Material
    1009102
    67730
    0100-09106
    0020-09107
    0100-09107
    32000456612
    5100-99088
    0100-09109
    0100-09110
    0100-09113
    50414709000

  2. #2
    Join Date
    Jun 2004
    Posts
    92
    Use this as SQL in update query. Just change the table to the correct name.

    UPDATE yourtable SET yourtable.material = IIf(IsNumeric(Right([material],1)),[material],Left([material],Len([material])-1));

    Keep in mind that this will only work if the letter you are looking for is the last character in the string.
    Last edited by sionus; 09-04-04 at 23:25.

  3. #3
    Join Date
    Jul 2004
    Posts
    23
    I typed this into the SQL view of the QBE but seeing this error message, "characters found after end of SQL statement".
    What did I do wrong ? please help and thanks.

    SELECT MyTable.Material
    FROM MyTable;
    UPDATE MyTable SET MyTable.material = IIf(IsNumeric(Right([material],1)),[material],Left([material],Len([material])-1));

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Obviously more than what is required but never the less, I have taken the liberty to create three functions that will do what you require with a lot of flexability should you ever need it. You (or others) may find these functions usefull for other purposes as well:

    The RemoveAlphaFromField fuction will carry out the task you require but, it will remove ALL alpha characters from the field data unless you specify in the IgnoreChars parameter any characters you basically want to ignore (leave in the data).

    The other two functions, IsThereAlpha and GetRidOfAlpha are required by the RemoveAlphaFromField function but can run indepentantly on their own wherever desired.

    The source code provided is well documented (to well) and therefore makes it all look large and scary. The source is actually quite small if you get rid of all the commenting. Just copy and paste it into a database code module.

    The documentation within the source code makes it all self explanitory.

    Hope this helps......someone

    Attached is the source code....

    Attached Files Attached Files
    Last edited by CyberLynx; 09-05-04 at 04:34.

  5. #5
    Join Date
    Jun 2004
    Posts
    6
    This was also answered for you yesterday here

    Bob

  6. #6
    Join Date
    Jun 2004
    Posts
    92
    Quote Originally Posted by alienscript
    I typed this into the SQL view of the QBE but seeing this error message, "characters found after end of SQL statement".
    What did I do wrong ? please help and thanks.

    SELECT MyTable.Material
    FROM MyTable;
    UPDATE MyTable SET MyTable.material = IIf(IsNumeric(Right([material],1)),[material],Left([material],Len([material])-1));
    What you did wrong was to add the select and from statements to the SQL I gave you. The only text that should be in there is what I provided.

    That and you probably didn't change "Mytable" to your table name.

  7. #7
    Join Date
    Jul 2004
    Posts
    23
    Really Thanks so much to you helpful people here.

    Thanks Sionus, Cyberlynx and raskew.

Posting Permissions

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