Update table based on partial text in field

[JK]

I have a table that contains data I'm pulling from our main company database,
J.D. Edwards. Unfortunately, I can't find a field in Edwards that defines an
inactive account inactive. There is no date closed field or anything else as
far as I can tell.

The only way I know for sure an account is considered inactive is if it has
four stars in the mailing name field (i.e. ****)

I'd like to add a new check box field to my companies table called
ckInactive. Then, I would like to run an update table query and if an account
name contains at least three (3) stars (***) check the ckInactive check box.

I'm using access 2003.

Any help writing the SQL statement would be greatly appreciated. Any other
ideas would be helpful as well.

Thx as always everyone!

Jason

 

[John Spencer MVP]

WEll you can find the records with the mailing address containing three or
more consecutive addresses using a where clause that looks like

UPDATE SomeTable
SET ckInactive = True
WHERE [MailingAddress] LIKE "*[*][*][*]*"

And to clear the value
UPDATE SomeTable
SET ckInactive = False
WHERE [MailingAddress] LIKE "*[*][*][*]*"

You could do that all in one query using

UPDATE SomeTable
SET ckInactive = IIF([MailingAddress] LIKE "*[*][*][*]*",True,False)

There is a minor problem with that and if the Mailing Address is NULL (Blank)
then the first two queries will not change the value of ckInactive.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Use InStr function.
IIF(InStr([mailing name], Chr(42)&Chr(42)&Chr(42)) >0)