435,426 Members | 3,152 Online
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

# Quick help with extracting a value

 P: n/a I'm hoping this is simple: field1 | field2 abc-123 def-456 abc-123 I need to update field2 to everything that comes after the dash in field 1. So final result would be field1 | field2 abc-123 | 123 def-456 | 456 abc-123 | 123 thanks Nov 13 '05 #1
2 Replies

 P: n/a Johnny Gardner wrote: I'm hoping this is simple: field1 | field2 abc-123 def-456 abc-123 I need to update field2 to everything that comes after the dash in field 1. So final result would be field1 | field2 abc-123 | 123 def-456 | 456 abc-123 | 123 thanks update table set field2 = Mid(field2 , InStr(1, field2 , "-") + 1) -- [OO=00=OO] Nov 13 '05 #2

 P: n/a Johnny Gardner wrote: I'm hoping this is simple: field1 | field2 abc-123 def-456 abc-123 I need to update field2 to everything that comes after the dash in field 1. So final result would be field1 | field2 abc-123 | 123 def-456 | 456 abc-123 | 123 thanks You can use the "INSTR()" function to find the location of the dash and the "MID()" function to take all the characters after the dash in the string value in field 1 and an update query to load that value in field 2. Place the following on the update line in field 2: Mid([field1],instr([field1],"-")+1) Or you could do the whole process with the following SQL: UPDATE Table1 SET Table1.field2 = Mid([field1],InStr([field1],"-")+1); The MID function will start to read the string specified in the first variable at the character number given in the second part of the function. The INSTR (IN STRING)function returns the number of characters from the start of the string till it finds the character specified in the second part of the function "-". The +1 adds one to the value returned by the INSTR function to start the string at the character after the dash. Nov 13 '05 #3

### This discussion thread is closed

Replies have been disabled for this discussion.

### Similar topics

Browse more Microsoft Access / VBA Questions on Bytes