Joe, Sounds like an Access bug to me. (Can an application crashing ever be anything else? :) )
However, if I might make a suggestion.... Create a table (Company_by_email) with two columns (Email_address, Company_Name) that you can do this kind of match against. This design will scale better and allow for a unique index on Company_by_email.Email_address and allow for the same Company_Name to map to multiple Email_address values too. Then you can get rid of all of the logic (AKA: "IIF" statements) and use SQL the way it was intended to be used. I think you could match the values by doing a join idea like... Now don't fault me on my syntax.... I am not a DBA... but I think this should be possible like .... And don't fault me if "Access" does not fully support SQL either. :) Select RefreshData.[User Email Address], Company_by_email.Company_Name from RefreshData left outer join on Company_by_email on RefreshData.[User Email Address] LIKE "%Company_by_email". -- Carey Matthew Black BMC Remedy AR System Skilled Professional (RSP) ARS = Action Request System(Remedy) Love, then teach Solution = People + Process + Tools Fast, Accurate, Cheap.... Pick two. On Tue, Dec 1, 2009 at 4:40 PM, Joe DeSouza <[email protected]> wrote: > ** > Listers, > > First of all this is an Access related issue, so qualifies to be an off > topic question I guess. I just figured many of you might be using Access as > a choice of tool for data cleaning so may be familiar with what I am > facing.. > > Below is the query I have written in Access, to set the name of the company > in the table RefreshData, based on the domain of a users business email > address.. > > While this query runs perfectly and sets the company name, if I try to save > the query for future use in Access, it doesn't save it but crashes out > Access, and when I reopen it, the query has nothing in the SQL panel besides > the default SELECT; that appears when you open a new access query to build a > new query.. > > This is the update query that I've successfully ran, but unable to save.. > UPDATE RefreshData SET RefreshData.[Company] = > IIf(RefreshData.[User Email Address] Like '*centresolutions.com',"Centre > Solutions", > IIf(RefreshData.[User Email Address] Like '*csc.com',"Computer Sciences > Corporation", > IIf(RefreshData.[User Email Address] Like '*farmersinsurance.com',"Farmers > Insurance", > IIf(RefreshData.[User Email Address] Like '*foremost.com',"Foremost > Insurance Group", > IIf(RefreshData.[User Email Address] Like '*kraftlake.com',"Kraft Lake > Insurance Agency (KLIA)", > IIf(RefreshData.[User Email Address] Like '*us.ibm.com',"IBM - United > States", > IIf(RefreshData.[User Email Address] Like '*wipro.com',"Wipro Technologies", > IIf(RefreshData.[User Email Address] Like '*wtp.ca',"WTP", > IIf(RefreshData.[User Email Address] Like '*zurich.com',"Zurich Financial > Services Group", > IIf(RefreshData.[User Email Address] Like '*zurichbf.com',"Zurich Benefit > Finance", > IIf(RefreshData.[User Email Address] Like '*zurichna.com',"Zurich North > America",RefreshData.[Company]))))))))))); > > Any idea why this may be happening? > > As a workaround I have saved this in notepad for future use - not convenient > but will have to do.. > > Joe _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:[email protected] ARSlist: "Where the Answers Are"

