Hi,
We generally store atomic information in a field (ie, something that
cannot be logically divided given the context of the problem we try to
solve) since there is no index that can help if you scan about finding
records where "the fourth, fifth and sixth characters" of the super
marketing number are equal to "abq". Instead, store that info in its own
field, and have WHERE fieldName = 'abq'. You can "compute back" the whole
marketing monster number as a computed field, later on, when it is required,
for output, but for work, try to be "atomic": one and just one information
per field.
That being said, you can use InStr and Mid (or left, or right ):
Left( "Once upon a time was a story", InStr( "Once upon a time was
a story" & " ", " ") -1 )
InStr may return 0 if it does not find the pattern. Supplying it, with &
" ", it will return a positive value. Since InStr return the position of
the " ", and since I do not want it, I subtract 1 from it, so, Left will
extract just the first "word" of the sentence. That works even if the whole
sentence is a single word, like "No."
Left( fieldName, InStr( fieldName & " " , " " ) -1 )
but think about atomic data, if possible,
Vanderghast, Access MVP
