Returning Partial Strings

[ScottM]

I'm trying to separate a word/string of characters in a text field from the
rest of the field, but the strings are of varying lengths. Is there a way to
extract this? For example, my field contains the following values:

RAST 2006-1 M5
CWALT 2006-AR1 A

How can I extract the "RAST" and the "CWALT" from each?
How can I extract the "2006-1" and the "2006-AR1" from each?

Thanks in advance!

 

[fredg]

I'm trying to separate a word/string of characters in a text field from the
rest of the field, but the strings are of varying lengths. Is there a way to
extract this? For example, my field contains the following values:

RAST 2006-1 M5
CWALT 2006-AR1 A

How can I extract the "RAST" and the "CWALT" from each?
How can I extract the "2006-1" and the "2006-AR1" from each?

Thanks in advance!

Look up the Split Function in VBA help.

 

[raskew via AccessMonster.com]

Hi -

You can use the Instr() function to locate the first space in the field.
Example from the debug window:

x = "RAST 2006-1 M5"
y = "CWALT 2006-AR1 A"
? left(x, instr(x, " ")-1)
RAST
? left(y, instr(y, " ")-1)
CWALT

HTH - Bob

 

[John W. Vinson]

I'm trying to separate a word/string of characters in a text field from the
rest of the field, but the strings are of varying lengths. Is there a way to
extract this? For example, my field contains the following values:

RAST 2006-1 M5
CWALT 2006-AR1 A

How can I extract the "RAST" and the "CWALT" from each?
How can I extract the "2006-1" and the "2006-AR1" from each?

Thanks in advance!

You can use the builtin string handling functions Left(), Mid(), and InStr().
To get the leftmost bit (before the first space) use

Left([fieldname], InStr([fieldname], " ") - 1)

The second bit is a bit more complex since you need to find both delimiting
blanks:

Mid([fieldname], InStr([fieldname], " ") + 1, Instr(Instr([fieldname], " ") +
1, [fieldname], " ") - InStr([fieldname], " ") - 1)

John W. Vinson [MVP]