Update Query or Code

Surjer

Registered User.
Local time
Today, 12:28
Joined
Sep 17, 2001
Messages
232
Hello all,

Having some troubles updating some records. I need to run some code that will erase a portion of the record.

fieldA (Text) Value might equal "400.401 my text"

I have another table that contains all the codes like

400
401
500
504
324

i need to delete any portion that matches any record in the other table

Needs to end up like " my text"

Thanks in advance for any advise!

Jerry
 
Can you give more examples of the data in fieldA please...
 
Field A will always contain a 3 digit number which might or migh not be matched in TableB

Field A might contain 2 or 3 sets of 3digit numbers separated by a "." dot.

230 COLUMN
470.715
401 Conc
200.400 asph
400
420
421.400.255 hello



I only want the comments that proceed the 3 digit codes
 
If the numbers that you are trying to match from the first table are always the first 3 digits: 400.401 my text should be 400. Then use an update quey to update the field:

Update to: Left([FieldA],3)
 
UPDATE tblFinal SET tblFinal.fldFixedCode = Right([tblFinal]![fldRawCode],InStr(1,[tblFinal]![fldRawCode]," ",1)-1)
WHERE (((tblFinal.fldRawCode) Like "* *"));

Think I got it!

It will contain a space if there is text so thats the ticket to look for!
 
Code:
UPDATE tblFinal SET tblFinal.fldDescription = Right([tblFinal]![fldRawCode1],Len([tblFinal]![fldRawCode1])-(InStr(1,[tblFinal]![fldRawCode1]," ",1)))
 

Users who are viewing this thread

Back
Top Bottom