Re: Help with Union & lookup

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I don't see where you are searching the surname field in the query you posted.

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

Hugh self taught wrote:
Hi Clifford,

What a twit I am... how can I have = Like
I realised it while I was driving to site. Now that I've fixed that I did a search for "gar" I ended up with 3 gary (first name)but there should also have been a Legarde (surname) Any suggestions on that?

"Hugh self taught" wrote:

Hi Clifford,

Okay The first part I've corrected & works. The Like is throwing out a syntax error (missing operator) in the first WHERE statement. The actual SQL is below.

SELECT P1.Competitor_idx, P1.First_Name, P1.Surname, C.CoupleNumber, P2.Competitor_idx, P2.First_Name, P2.Surname
FROM (Competitors AS P1 INNER JOIN Couples AS C ON P1.Competitor_idx = C.MaleID) INNER JOIN Competitors AS P2 ON C.FemaleID = P2.Competitor_idx
WHERE P1.First_Name = Like "*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" OR P2.First_Name = Like "*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*"
UNION ALL SELECT P1.Competitor_idx, P1.First_Name, P1.Surname, Null, Null, Null, Null
FROM (Competitors AS P1 LEFT JOIN Couples AS C1 ON P1.Competitor_idx = C1.MaleID) LEFT JOIN Couples AS C2 ON P1.Competitor_idx = C2.FemaleID
WHERE C1.CoupleID Is Null AND C2.CoupleID Is Null AND P1.First_Name = Like "*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*";

Maybe I'm missing some brackets but I've not had the time to experiment. Maybe on the weekend. Your help is greatly appreciated. BTW that "LIKE" expression I'm using works fine in another SQL query but with 3 sets of brackets.


"Clifford Bass" wrote:

Hi Hugh,

The C1.id and C2.id should not be causing a parameter box unless you do not have an "id" column in the couples table. Your original post indicated there was. It there is not, use another field such as the "cplnumber" field. You can use any column in the table as long as the column cannot contain null values in the table. The Like part may be working, but you would not know it while the C1.id and C2.id issue is there.

If that does not help, post your actual SQL.

Clifford Bass

"Hugh self taught" wrote:

Hi Clifford,

Thanks for this so far. First reference to C1.id & C2.id is "WHERE C1.id Is Null AND C2.id Is Null AND P1.fname =" so they have to be input from the keyboard. I also want to use a "LIKE" type lookup but my syntax Like "*" & [Forms]![frmFindCompetitor]![txtFindCompetitor] & "*" gives me an error.

Any ideas on getting past these 2 issues?

Thanks
Hugh
.

Relevant Pages

  • Re: Error when using RANK BY
    ... searching. ... If you're using IS Query Language not SQL ... rank property score for a search result. ...
    (microsoft.public.inetserver.indexserver)
  • Re: create parameters dynamically?
    ... a querydef's Parameters collection when the query's SQL ... if you have to create the querydef's SQL ... >i'm trying to take a class factory approach to searching. ... then use these params to modify the query. ...
    (microsoft.public.access.formscoding)
  • Re: dont get info i need in query
    ... Hi Carol. ... and I've been searching these ... the query and switch to SQL View in the View menu, ...
    (microsoft.public.access.queries)
  • SQL Count(*)
    ... other SQL: ... This is the Query resulting the records ... SELECT CountFROM (SELECT A.customerid FROM A INNER JOIN customers ... searching and really can't find it. ...
    (comp.databases.ms-access)
  • Re: Parameter Query Form Problem
    ... This is what I used to make my query, its was a Microsoft support site. ... controls on the form so it can run the query. ... ContractStartDate ... Combo Box for searching Customer Name ...
    (microsoft.public.access.formscoding)