Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2006
    Posts
    60

    Unanswered: Adding Find/Replace into code

    I have the data as seen in my attached picture on a form. i want to, if possible, for when a button is pressed to replace all the "DELXX--" with nothing (ie. "") and then remove the numbers from the first box and put them in the second box. i would really like to get the first part working if possible, the second is not as important.
    Attached Thumbnails Attached Thumbnails current.JPG  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is this a one off task... ie to clear up your current data, or something that you see as an ongoing requirement?

    the engine to drive the process is in any event going to be a SQL update query

    update <mytable> set <mysecondcolumn>= mid$(<myfirstcolumn>,8), <myfirstcolumn>= left$(<myfirstcolumn>,7) where left$(<myfirstcolumn>,7) = "DELXX--"

    it may be smarter at first to do it as two separate queries, at least untill you are happy its working correctly.. update the second column first, then set the first column.. test it with a copy of the data BEFORE doing it to live data

    having proved the query works then you could put it behind a command button, pulling a parameter instead of DELXX--.. that way round you can run the query again for different prefixes, not just DELXX--

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2006
    Posts
    60
    it is a frequent task and it will always be DELXX--. i will give this a go, cheers.

  4. #4
    Join Date
    Feb 2006
    Posts
    60
    i gave it a quick go and got a syntax error. i have no idea about this sql stuff so all i did was put this code in a command button which looked like this

    update [T-Transmittal_Info] set [Doc Number]= mid$([Doc Prefix],8) [Doc Prefix]= left$([Doc Prefix],7) where left$([Doc Prefix],7) = "DELXX--"

    [T-Transmittal_Info] is my table
    [Doc Prefix] is my first column
    [Doc Number] is my second column

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In Access you have to use the RunSQL command to... run SQL commands!
    Code:
    Dim SQL As String
    SQL = "update [T-Transmittal_Info] set [Doc Number]= mid$([Doc Prefix],8) [Doc Prefix]= left$([Doc Prefix],7) where left$([Doc Prefix],7) = 'DELXX--' "
    
    DoCmd.RunSQL SQL
    Not sure about the dollar signs - but give it a go!
    Notice that I've changed the quotes around DELXX-- to single quotes
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not surprised you are gettign a syntax error
    Code:
    update [T-Transmittal_Info] set [Doc Number]= mid$([Doc Prefix],8) [Doc Prefix]= left$([Doc Prefix],7) where left$([Doc Prefix],7) = "DELXX--"
    should read
    Code:
    update [T-Transmittal_Info] set [Doc Number]= mid$([Doc Prefix],8),[Doc Prefix]= left$([Doc Prefix],7) where left$([Doc Prefix],7) = "DELXX--"
    the dollar sign should work.... if you have a problem 'just' remove them. it tells the interpreter to return an explicit string value rather than a variant.
    Last edited by healdem; 05-23-07 at 07:49.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Good spot on the missing comma - hadn't noticed that one!
    And I never knew that about the $ - I'll bear it in mind in the future
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2006
    Posts
    60
    well thanks for the help i managed to get the first bit (getting rid of the DELXX--) working good using
    "update [Q-Transmittal_Deleted_Items] set [Doc Prefix]= mid$([Doc Prefix],8)"

    i made a query to run the update off as if i ran it on the table (which has 70,000+ records and increasing) i think it would take a while.

    i was having a fiddle trying to split the numbers from the letters with no luck. the code above is no good as i dont know how many letters there will be, or how many numbers there will be, but i always want the numbers (however many there be) at the right end before any letters/symbols to be moved into the second box.

    i tried to use an if statement in the sql, saying if the very right entry in the first box is a number then add it onto the front of the contents of the second box, and then delete it from the first. it would have to loop through this a few times obviously but i could not even get the first bit to work with the if statement. any ideas?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by neRok
    ...
    i was having a fiddle trying to split the numbers from the letters with no luck. the code above is no good as i dont know how many letters there will be...
    ...yes you do... if you use a where clause to limit the rows returned to DELXX-- then by defintion you know where and how to perform the operation
    the first column will be set to DELXX-- thats what the left$(<blah>,7) does
    the second column will be set to waht ever comes after the DELXX-- thats what the mid$(<blah>8) bit does. thats why I asked is this a one off, and is this always going to be DELXX--.. otherwise we would have had to doa bit more work on the SQL statement to accept any parameter value.

    btw if you want to know how many characters there are in a string use the len() function

    Quote Originally Posted by neRok
    i tried to use an if statement in the sql,
    why?

    Quote Originally Posted by neRok
    any ideas?
    what SQL did you actually use, that didnt perform what you wanted

    if you feel happy to Id suggest you also post some data, especially if the data shows the faults you are having.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Feb 2006
    Posts
    60
    i must not have explained myself clearly with what i wanted to achieve. all this data is exported from another program into a text file which i then import into the database. the DELXX-- is to seperate these entrys (which need configuring) with the ones that are already correct. see the attached report for how i would like the data to end up. the top is how i can get it with the bit of code i already have which removes the DELXX--, which is not important.

    to throw some spanners in the works, the data in the first field maybe B45345345345345 or is could be BFDBFBDFBSF65 or it could be 345345-DFSDF-SFSDF-59. basically i need whatever numbers are at the far right of the first field to be moved to the second, and each row in the same group of data may have different amounts of numbers in the first field etc.

    my idea with the sql i tried with the if statement (which was inside the sql if thats even possible), was to test the last (right) character of each of the first fields, and if it was a number then move it to the front (left) of the second field. the if statement basically said if right(<first field>,1) = 0 or 1 or 2 or ... 9 then <second field> = right(<first field>,1) & <second field>

    btw, is there a function which gives only the numbers in a string you enter, ie. if you enter a1b2c3 it would give 123.
    Attached Thumbnails Attached Thumbnails data.JPG  
    Last edited by neRok; 05-24-07 at 05:13.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you know your data
    is ther ever more than one character after the --
    ie is FF123 a possible outcome


    if it is then you are probably goig to have to write a vb function to identify where the number element starts

    it could be done as an IIF.. but I think in this case IIF will be difficlult to set up difficult to debug

    something like
    Code:
    public GetDwgNo(strDwgRef) as variant
    dim iLoop as integer, intCharpos as integer, booProcessloop as boolean
    getdwgno=null 'set up a default in case there is no valid number 
    if isnull(strdwgRef) then exit function ' CDICDO... crap data in crap data out
    intcharpos=len(strdwgref)
    booProcessloop=true
    while booProcessloop=true
      if isnumeric(mid$(strdwgref,intcharpos)) then
         getdwgno=mid$(strdwgref,intcharpos) 'not the best way of doing it.. may be better to store a pointer and extract the string once
        intcharpos=intcharpos-1'set up for the next test
      else
        boocharpos=false ' finish processing on the first non valid number test
      endif
    wend
    exit function
    you woudlkneed to place the function in a public module, and then it will be available to any form, report or query that requires it
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Feb 2006
    Posts
    60
    yer there maybe any amount of letters after the --. there may also be more numbers after some letters, which would be seperated from the very end numbers by either more letters or a '-' or something. hence why i thought starting from the right and working to the left might be the go as you would just stop once you reach something thats not a number.

  13. #13
    Join Date
    Feb 2006
    Posts
    60
    ok i managed to get it working. at first i was using the sql method to get rid of teh DELXX-- but i didnt like the fact it kept asking if u wanted to modify some records.

    so to get it all to work nicely i open a second form using this filter
    [Doc Prefix] Like 'DELXX--*'

    then on that forms load i have this code
    Code:
    Private Sub Form_Load()
        
        DoCmd.GoToRecord , , acFirst
    
        Do
        
            Dim CharPos, CharTot As Integer, booProcessloop As Boolean, GetDwgNo As String
        
            [Doc Prefix] = Right$([Doc Prefix], Len([Doc Prefix]) - 7)
        
            CharPos = 1
            CharTot = Len([Doc Prefix])
            booProcessloop = True
            
            While booProcessloop = True
                If IsNumeric(Right$([Doc Prefix], CharPos)) Then
                    GetDwgNo = Right$([Doc Prefix], CharPos)
                    CharPos = CharPos + 1
                Else
                    booProcessloop = False
                End If
            Wend
            
            If GetDwgNo = Abs(GetDwgNo) Then
                CharPos = CharPos - 1
            Else
                CharPos = CharPos - 2
                GetDwgNo = Right$([Doc Prefix], CharPos)
            End If
                
            [Doc Number] = GetDwgNo
            [Doc Prefix] = Left$([Doc Prefix], CharTot - CharPos)
            
            On Error GoTo ExitLoop
            
            DoCmd.GoToRecord , , acNext
        
        Loop
        
    ExitLoop:
        
    End Sub
    i had to include the abs section cause if a '-' was used a seperator it thought it was part of the number and made it a negative, not what i wanted.

Posting Permissions

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