query help

  • Thread starter Thread starter aspickwick
  • Start date Start date
A

aspickwick

I am trying to write a query to categorize or bucket a memo field. I would
like to query the memo field and create 2 fields, one action and one noun
where the results would give me for the following memo field:
"replace drain valve, adjust door guide screw"
The following results
Action - replace; Noun - drain valve and
Action - adjust; Noun - door guide screw

And not:
Action - replace; Noun - Door guide screw
Action - adjust; Noun - drain valve
 
If you dictate a rule that the first word is always the verb, and subsequent
words are considered the noun, and actions are seperated by the period(or end
of text), then this may be feasible and simple.

Extracting the first word is easy. Use the InStr() function to find the
first space.

intPosSpace = InStr([fieldname], " ")
If intPosSpace > 0 then
strVerb = Left$([Fieldname], intPos - 1)
End If
intPosPeriod = InStr([Fieldname], ".")
If intPosPeriod > 0 then
strNoun = Mid$([Fieldname], intPosSpace+1, intPosPeriod -1)
else
strNoun = Mid$([Fieldname], intPosSpace+1)
end if

If you have multiple periods, then you'll need to loop until no more periods.
 
Thank you for your help! I will try it out.

S.Clark said:
If you dictate a rule that the first word is always the verb, and subsequent
words are considered the noun, and actions are seperated by the period(or end
of text), then this may be feasible and simple.

Extracting the first word is easy. Use the InStr() function to find the
first space.

intPosSpace = InStr([fieldname], " ")
If intPosSpace > 0 then
strVerb = Left$([Fieldname], intPos - 1)
End If
intPosPeriod = InStr([Fieldname], ".")
If intPosPeriod > 0 then
strNoun = Mid$([Fieldname], intPosSpace+1, intPosPeriod -1)
else
strNoun = Mid$([Fieldname], intPosSpace+1)
end if

If you have multiple periods, then you'll need to loop until no more periods.

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting



aspickwick said:
I am trying to write a query to categorize or bucket a memo field. I would
like to query the memo field and create 2 fields, one action and one noun
where the results would give me for the following memo field:
"replace drain valve, adjust door guide screw"
The following results
Action - replace; Noun - drain valve and
Action - adjust; Noun - door guide screw

And not:
Action - replace; Noun - Door guide screw
Action - adjust; Noun - drain valve
Click to expand...
Click to expand...
 
Back
Top