Help learning Access

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")))
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/5969-help-learning-access.html

How do I achieve this in Access as I want to produce a report that is sorted
by location.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5969

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

Any constructive help would be appreciated
Newberry

--
Remove DOG from email address to contact

--
Remove DOG from email address to contact

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5969
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.

--
Brendan Reynolds

"Newberry" wrote in message
news:43608983$0$23289$db0fefd9@news.zen.co.uk...
> 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")))
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=5969
>
> 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
>
> --
> Remove DOG from email address to contact
>
> --
> Remove DOG from email address to contact
>