Hi and thanx for reading my post..

Underneat is the current SQL i'm trying to get to work (in Access 2000)

Cant get it to work, cause i'm getting Join errors..

-----------CODE--------------
strSQl = "SELECT p.PID, p.Lastname, p1.PID, p1.Lastname" & _
" FROM Person p, Person p1" & _
" INNER JOIN (PersonPerson pp ON pp.PID = p.PID OR pp.PID2 = p.PID)" & _
" WHERE (pp.PID LIKE '*" & CStr(Me.txtSearch) & "*'" & _
" OR pp.PID2 LIKE '*" & CStr(Me.txtSearch) & "*')"
----------END CODE-----------

-------------DESCRIPTION--------------------
1) Got a table "Person" where i'm inserting PID and Lastname.
2) Registering person 1-> PID = 1, Lastname = Doe
3) Registering person 2-> PID = 2, Lastname = John
4) Using table PersonPerson (manually) to connect these two together.
PID1 = 1, PID2 = 2
5) By using this i can use SQL to get the data from both persons taht are
connected
6) So basically, i want to go through all records in the personperson table, to find PID and PID2, and then use _both_ the fields to check against the Person-table. First getting Person.PID and Person.Lastname (For PERSON 1) THEN... do the exact same operation for PID2.


-------------END DESCRIPTION------------------

Result should look like this :

Person1 ID, Person1 Lastname, Person2 ID, Person2 Lastname
----------------------------------------------------------
1 Doe 2 John



Will be enormously happy if anyone could help me out with this code...

/(_Mirador

I'm not 100% on what you want. Is this your table structure?
If so - this should help:
It will generate the following output:

(Yes, Person 4's name is Harry Potter - but no - I haven't read the books )

Great !

I will try the code right away..

I'll send you the results i got

Mirador.

Make sure you're using the latest code - I've edited the post a few times since I first posted it (stupid syntax bug)

Super-great !!!!!

It worked like a dream..

You have no clue how much you've helped me !

Owe u a digital-beer.

Mirador.

lol - no worries mate.

But I've gotta drive tonight - so how about a digital soda?

/Me hands over a digital master-soda

hehe..

btw : since you're so master'ish at SQL.. maybe u could try and help me with this query too ?

-----------CODE--------------
strSQl = "SELECT Person.PID" & _
" FROM Person INNER JOIN (Kjøretøy INNER JOIN PersonKjøretøy ON Kjøretøy.KID = PersonKjøretøy.KID) ON Person.PID = PersonKjøretøy.PID" & _

" WHERE Person.Etternavn LIKE '%" & Me.txtSearch & "%' OR Person.Etternavn LIKE '%" & Me.txtSearch2 & "%'" & _
" OR Person.Alias LIKE '%" & Me.txtSearch & "%' OR Person.Alias LIKE '%" & Me.txtSearch2 & "%' OR Person.Yrke LIKE '%" & Me.txtSearch & "%' OR Person.Yrke LIKE '%" & Me.txtSearch2 & "%'" & _ etc.etc.etc...
---------- END CODE ---------------

----------- DESCRIPTION--------------------
As you can see it's 2 x fields where i want to search..
Basically... i'm running 2 different queries depending on (and checking) if only one, or two fields are filled in for the search... AND...

If both the fields are filled in, it should ex : if Me.txtsearch is "June" and Me.txtSearch2 is "2004" make sure it's only getting a record if both "June" and "2004" is included in _ANY_ of the fields in the record..

I have realized that i cannot only use AND alone, because then all the fields have to match, and i cannot use OR alone either.. because then it grabs if it only matches one of them..

Got any clues on this one ?

Thanx for your help mate..

Mirador.

So basically - you are trying to select records in which BOTH of the criteria is in ONE of the fields. If this is correct (and if it's not I'm lost ) - then this should help (take note of the brackets):

Basically you are saying (i'll use a generic example of a Product record. No offence - but I really can't understand the table you have with the column names ):

If txtSearch1 is "big" and txtSearch2 is "round"

SELECT blah
FROM Product
WHERE (Product.Name LIKE '%big%' AND Product.Name LIKE '%round%')
OR (Product.Description LIKE '%big%' AND Product.Description LIKE '%round%')
OR (Product.Comment LIKE '%big%' AND Product.Comment LIKE '%round%')

So you will get products with big AND round in either the Name, Description or Comment column.

Is this what you were after - or have I just spoken pure gibberish?

Hay mate thanx for your reply

did u enjoy your digital soda ? hehe..

Well... gotta admit that it's a bit confusion for myself too

I'll try to explain a bit better :

----------------------------
Q :
Lets say u want to search for every record that includes both "Green" and "Yellow" in _any_ of the fields in the record, and u want to see _only_ those records.

A:
You find a record where field "lastname" has "Green" in it and field "Comment" has "Yellow" in it -> MATCH!!
-------------------------------

If i understood it right, the one you posted has to have both "Green" and "Yellow" in one field right ?
So.. u would get a match if ex. "Comment" field had the text :
"All green people are infact Yellow because they bla-bla.-bla..."

I have to lets say.. use the second searchfield (txtsearch2) to... "narrow" down the search.

Example :
--------------------
Lets say u want to find a person named "Mike" and u press search. U would maybe get something like 1000 matches if u got a huuge database.
But then u altso know that "Mike" is from "Uganda". So... therefore i type "Mike" in txtsearch and "Uganda" in txtsearch2 to make sure that u get all the "Mike" records which got "Uganda" in any of the other fields..
--------------------

Yea.. that's about it dunno if it's even possible but i surely hope so : )heeh..

Hope that made things a littlebit more clear

Thanx for your help btw !!!.. most appreciated..

want another digital soda ? or.. maybe a digital beer this time

Best regards
Mirador.

Ahh - I see what you mean. It's also possible - but the code is quite long, depending on the columns in your table. It's basically the same as my other example above - but switch the ANDs and ORs.

For each search criteria you have to check if it's in any of the columns. Again - using my Product table:

Doing this for each column should get what you're after. Basically you're saying SearchCriteria1 needs to be in any of the columns (using the OR) - AND SearchCriteria2 needs to be in any of the columns.

Is that what you're after?

And I'm well past a digital beer - with the last few weeks I've had at work. Better make it a digital (double) scotch!

Tjohooo !!..

yea.. that's JUST what i was after..

I had thoughts in this track, but wasn't certain because it would be so extremely long hehe.. Didn't know quite how to write it either..

but.. THANK YOU AGAIN !!

Double scotch coming up.. or.. maybe it's back to coffee ? heheh !!:



Mirador..

These last few weeks (since mid-April) have been awful for me too. Do you suppose that the universe has taken some kind of unusually perverse twist against us "denizens of databases" lately?

-PatP