Update Query or Code

[Surjer]

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

 

[Uncle Gizmo]

Can you give more examples of the data in fieldA please...

 

[Surjer]

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

 

[jfgambit]

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)

 

[Surjer]

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!

 

[Surjer]

UPDATE tblFinal SET tblFinal.fldDescription = Right([tblFinal]![fldRawCode1],Len([tblFinal]![fldRawCode1])-(InStr(1,[tblFinal]![fldRawCode1]," ",1)))