Query recognizing blank cells troubles 1

[JohnnyG23]

I need some help on a query. What I am trying to do is have the query look at Employees and find all the preferred names that are not in the CURRENT table and update the CURRENT table. My problem right now is that all of the fields that need to be updated in CURRENT are just blank so the query isn't recognizing them so it doesn't update anything. Is there an SQL statement I can add that will recognize that a cell, preffered_name, in CURRENT is 'blank' but in Employees it is John and it will then update it? Thanks.

UPDATE [CURRENT]

INNER JOIN Employees ON CURRENT.TechID = Employees.TechID 

SET

[CURRENT].PREFFERED_NAME = [Employees].PREFERRED_NAME, 
[CURRENT].Employee_Name = [Employees].emplname, 
[CURRENT].Prefix = [Employees].Prefix, 
[CURRENT].Suffix = [Employees].Suffix


WHERE 

(

((CURRENT.PREFFERED_NAME)<>(Employees.PREFERRED_NAME))

AND

((Employees.Preferred_Name)<>"")

AND

((CURRENT.TechID)=[Employees].[TechID])

);

 

[GingerR]

Hi. My first question is why do you have two tables with the same data in them? It's not a preferred way to design a database.

Secondly, your question is kind of confusing. Maybe the fix is to

1) check for null: Employees.Preferred_Name is not null

or

2) len(Employees.Preferred_Name)>0

vs.

Employees.Preferred_Name)<>""

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PHV]

WHERE Trim([CURRENT].PREFFERED_NAME & "") = "";

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JohnnyG23]

GingerR, I agree with you on the two databases part but that's how my boss wanted it so I can't really argue it. I'm sorry it was hard to understand but it's kind of hard to explain without seeing it all in person so I tried to explain as best I could. But, I think you are right in finding the nulls thing. I am just not sure how to do this. I tried typing in what you put but it gave me a mismatch error.

PHV I am kind of confused where to type your code in at. Am I removing everything in the WHERE statement and just adding what you have or is that meant to overwrite something I already have? Thanks

 

[PHV]

I suggested a brand new WHERE clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JohnnyG23]

Oh wow. That made things look much better. Is there any way to modify that so that it will ignore display equal blank cells in both Employees and in CURRENT.

-Quick reference-
CURRENT = Preffered
Employees = Preferred

Like, when I do Datasheet view for the select query, which is almost exactly the same as the update query only it's a select query, it comes back as:

Preffered_Name Preferred_Name
'blank' 'blank'
'blank' John
'blank' 'blank'
'blank' 'blank'
etc...


I am hoping to have it display as follows:

Preffered_Name Preferred_Name
'blank' John

That way it only display the text changes and not all the blank fields. I guess the SQL line would be to ignore all of the blank cells that are the same? I hope you can understand what I am trying to say here. If not let me know and I'll try and explain it again further in detail. Thanks.

 

[PHV]

WHERE Trim([CURRENT].PREFFERED_NAME & "") = ""
AND Trim([Employees].PREFFERED_NAME & "") <> "";

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JohnnyG23]

Awesome. Thanks so much PHV. Problem solved.

 

[JohnnyG23]

Alright, after doing some other things and testing this it doesn't want to change a preferred name if one already exists in both fields. Is there anyway to fix this? Thanks

 

[GingerR]

What have you tried? What doesn't work?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[JohnnyG23]

I tried editing the

((Trim([Employees].PREFERRED_NAME & ""))<>""))

line, but no luck there.
I also tried adding

(Employees.Preferred_Name <> CURRENT.Preffered_Name)

I realize that may not be the best or correct line to add but SQL isn't my native language so I'm slowly learning it as I go.

As far what doesn't work; say Employees has a preferred name of Test123 and CURRENT has a name of Test123456, when I run the query it doesn't return any results. So I pretty much need a line that says if text exists in both fields compare and return result right?

 

[JohnnyG23]

Ok, turns out all I needed to do was add

OR

(([Employees].Preferred_Name)<>([CURRENT].Preffered_Name))

to the end of my WHERE statement and all seems fine now.