Access Expression

I would like to construct a query from a table with a debtor name field. The
select statement needs to exclude all records that have the word Corp, CO,
Ltd, LLC, or Company in the field. Basically I want to eliminate businesses
and leave only individuals. I also do not want to eliminate someone with a
name such as Jill Comp. Thus I need to be specific for the CO and exclude
any records that merely have a Co someplace in the field.

Also, is there a good primer on Access Expressions with lots of examples?
Sort of Access Expressions for Dummies?

Thanks in advance and have a good weekend.

You would be better off adding a field named IsCompany (Yes/No) to your
table and setting its default value to False. Then create an update query
and update all the records to False. Finally change Update To to True and
add the following expression in the criteria of the query:
Like "*" & In(Corp, CO, Ltd, LLC, Company) & "*"
Run the query and IsCompany for DebtorNames that contain those words will be
marked True.

Note that this is not full proof and you should go back and manually review
the records and manually make appropriate changes to IsCompany.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com

"James K via AccessMonster.com" wrote in message
...
I would like to construct a query from a table with a debtor name field.
The
select statement needs to exclude all records that have the word Corp, CO,
Ltd, LLC, or Company in the field. Basically I want to eliminate
businesses
and leave only individuals. I also do not want to eliminate someone with
a
name such as Jill Comp. Thus I need to be specific for the CO and exclude
any records that merely have a Co someplace in the field.

Also, is there a good primer on Access Expressions with lots of examples?
Sort of Access Expressions for Dummies?

Thanks in advance and have a good weekend.

I agree with PC DataSheet that you would probably be better off adding another
field named IsCompany to your table.

I don't think his criteria will work to find those that need to be marked true.

You would need something like the following as the where clause.

DebtorName Like "* LLC*" OR
DebtorName Like "* Corp*" OR
DebtorName Like "* Company*" OR
DebtorName Like "* LTD*" OR
DebtorName & " " Like "* CO *" OR
DebtorName Like "* CO.*"

After the update, I would suggest you search for all the records with "*Co*" and
marked as IsCompany. Then review them manually to see if any were inadvertantly checked.

IF you cannot add the field, then the criteria listed above could be used to
identify records that were not companies, by making a change to Not Like and AND

DebtorName Not Like "* LLC*" AND
DebtorName Not Like "* Corp*" AND
DebtorName Not Like "* Company*" AND
DebtorName Not Like "* LTD*" AND
DebtorName & " " Not Like "* CO *" AND
DebtorName Not Like "* CO.*"

PC Datasheet wrote:

You would be better off adding a field named IsCompany (Yes/No) to your
table and setting its default value to False. Then create an update query
and update all the records to False. Finally change Update To to True and
add the following expression in the criteria of the query:
Like "*" & In(Corp, CO, Ltd, LLC, Company) & "*"
Run the query and IsCompany for DebtorNames that contain those words will be
marked True.

Note that this is not full proof and you should go back and manually review
the records and manually make appropriate changes to IsCompany.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com

"James K via AccessMonster.com" wrote in message
...
I would like to construct a query from a table with a debtor name field.
The
select statement needs to exclude all records that have the word Corp, CO,
Ltd, LLC, or Company in the field. Basically I want to eliminate
businesses
and leave only individuals. I also do not want to eliminate someone with
a
name such as Jill Comp. Thus I need to be specific for the CO and exclude
any records that merely have a Co someplace in the field.

Also, is there a good primer on Access Expressions with lots of examples?
Sort of Access Expressions for Dummies?

Thanks in advance and have a good weekend.