I've got a situation where I need to parse data out of one field into others. For two updated fields that are empty (Field3 & Field4) it works great. For the third field (SubNo), I am getting unexpected results. If I remove the -1, then I get the data I would expect, but with a space at the end. Can't figure out what I'm doing wrong with the -1.
As is usual, thank you in advance for any advice you can give.
Joe
Code:
DB.Execute "UPDATE MailFile SET MailFile.Field3 = " _
& "Mid([SubNo_ORIG],InStr([SubNo_ORIG],'_')+1,2)" _
& "WHERE (((MailFile.SubNo_ORIG) Like '*_*'));"
DB.Execute "UPDATE MailFile SET MailFile.Field4 = " _
& "Mid([SubNo_ORIG],InStr([SubNo_ORIG],'_')+3,6)" _
& "WHERE (((MailFile.SubNo_ORIG) Like '*_*'));"
DB.Execute "UPDATE MailFile SET MailFile.SubNo = " _
& "Left([SubNo_ORIG],InStr([SubNo_ORIG],' ',-1))" _
& "WHERE (((MailFile.SubNo_ORIG) Like '* *'));"