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"

Reply via email to