Help learning Access

[Newberry]

I've inherited a FoxPro database at work & I'm presently using Excel to
produce reports from the imported data.
I believe Access would be better suited for producing reports and so I am
trying to learn Access
..
I need to find particular words (locations i.e. store, compound) which have
been typed in a comment field, and in excel I would use something like:

=IF(ISNUMBER(SEARCH("store",R2)),"DS",IF(ISNUMBER(SEARCH("compound",R2)),"DC",IF(ISNUMBER(SEARCH("laundry",R2)),"DL","2B")))

How do I achieve this in Access as I want to produce a report that is sorted
by location.

FYI I can not change the FoxPro database only export the information in it.

Any constructive help would be appreciated
Newberry

 

[Brendan Reynolds]

You could do it with nested IIf() function calls. For example, you could use
an expression like this in a query ...

SortOnThis: IIf([TestMemo] Like "*store*","store",IIf([TestMemo] Like
"*compound*","compound","laundry"))

.... where 'TestMemo' is your comment field.

This assumes that the comment field will always contain one of the three
words - if it doesn't contain either 'store' or 'compound', this expression
assumes that it therefore must contain 'laundry'. The expression will become
correspondingly more complex if the comment field may contain other location
keywords, or no location keyword.

Sorting on the result of an expression like this is likely to be slow -
potentially, extremely slow, if sorting a large number of records across a
network.

The solution to both of these problems would be to add an indexed 'location'
field to the database, but I gather you can't do that.