Need to concatinate two colums and use for search 1

[1DMF]

hello,

I have the following SQL statement...

SELECT     Members.FirstName + ' ' + Members.LastName AS Full_Name, Business_Register.*
FROM         Business_Register 
LEFT OUTER JOIN Members ON Business_Register.Adv_MemNo = Members.ID
WHERE     (Full_Name LIKE '%myname%')

only SQL is moaning saying Full_Name is not a valid field?

Is it not possible to merge two columns and then do a comparison against the result of the merge?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[kjv1611]

Well, I thnk you can't use the alias there in your WHERE clause.

Instead, you'd have to do:

SELECT     Members.FirstName + ' ' + Members.LastName AS Full_Name, Business_Register.*
FROM         Business_Register
LEFT OUTER JOIN Members ON Business_Register.Adv_MemNo = Members.ID
WHERE     (Members.FirstName + ' ' + Members.LastName LIKE '%myname%')

I know I've tried to do the same thing before - well, use an Alias in the WHERE clause that I set in the SELECT clause, but it wouldn't work until I just put the original field(s) name(s) instead.

'Course if someone knows a way to make that work, I'd be all giddy and stuff!

--

"If to err is human, then I must be some kind of human!" -Me

 

[JBinQLD]

Mate, the % wildcard in access is *

 

[kjv1611]

JBinQLD, I just glanced at your profile, and noticed something you may want to correct..

Battle [highlight]scared[/highlight] with Access since 1994.

That is, unless you really are now SCARED, and not scarred. [WINK]

--

"If to err is human, then I must be some kind of human!" -Me

 

[Remou]

Be careful with plus (+):

a=null
b="abc"

?a+b
Null

?a & b
abc

Ampersand (&) should be used to concantenate strings.

 

[PHV]

Anyway, the WHERE clause defeats the LEFT OUTER JOIN ...

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

 

[JBinQLD]

kjv1611: Both are apt really, when one wakes in the middle of a night cos of a nightmare about the current sales team promises ones scars make one scared!

Thanks for pointing out the typo, I'm generally more scarred than scared! Cheers mate!

PS. Did replacing the wildcards help?

 

[1DMF]

Thanks for all the replies,

I've changed the sql but found if I used ampersand it errored about varchar and nvarchar not compatible datatypes with the boolean AND operator.

So I guess it saw & as AND not 'add'

So what join should I use PHV?

using the + and KJV's concatination in the WHERE clause did the trick!

there *shouldn't* be a case where first/last is null/blank , but would appreciate advice on how to fix?

Is it possible to use NZ(var,val) in a select statement via JET ?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

PS. Did replacing the wildcards help?

Yes, but i was actually testing direct with the SQL editor via enterprise manager, I use * in the VBA ;-)

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[kjv1611]

Well, even if you don't have the first name, wouldn't you still want the record? Or would you prefer some sort of exceptions list instead?

--

"If to err is human, then I must be some kind of human!" -Me

 

[1DMF]

This is a members database , and the make member button doesn't work unless you have both names, so i'm not really worried, i'll get the records I want anyway

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!