Query Value

[Ken]

I have a query file in which the partial data field needs to populated
another field within the same query.

For Example:-

The Value of the DESC field has the following Sample String:-

"NOVFIN DEC 12 08 DISC DTC 2387"

The value 2387 needs to be in field called BROKERCODE which is a numeric
field. This code can be either 3 or 4 characters long. However, the code is
always after the word/string DTC.

Would the Instr Function be appropiate? If so, what would the syntax be??

 

[John W. Vinson]

I have a query file in which the partial data field needs to populated
another field within the same query.

For Example:-

The Value of the DESC field has the following Sample String:-

"NOVFIN DEC 12 08 DISC DTC 2387"

The value 2387 needs to be in field called BROKERCODE which is a numeric
field. This code can be either 3 or 4 characters long. However, the code is
always after the word/string DTC.

Would the Instr Function be appropiate? If so, what would the syntax be??

Assuming that it will be after the string " DTC " - as opposed to a chance
occurance of that string such as "ANDTCLO JAN 15 08..." - you could use

Brokercode: Mid([DESC], InStr([DESC], " DTC ") + 6)

This will work if the code is the last thing in the field - if it's not wrap
the whole Mid() function in Val().