I will really appreciate any help that can be provided, I have been stuck on this for ages now and it's driving me crazy.

I have 3 tables.
1. tbl_users - userID, firstname, lastname, company, username, password.
2. tbl_postcodes - userID, postCode
3. tbl_estateagents - name, address, postCode

We enter the users details, username & password and input the users postcodes they are allowed data on (usually 10+ per user).

when the user logs on and goes into the Estate Agents section I want the estate agents details to filter LIKE all the postcodes against that users userID.

Please help.

Edited by - slink on 18 Dec 2002 17:31:31

If you are using Access try something like:

RecordsetName.Sourece = "SELECT name, address, postCode FROM tbl_estateagents WHERE postcode IN(SELECT postCode FROM tbl_postCodes WHERE userID = " & Session("UserID" & ""

If you are using SQL Server, use a stored procedure, somethig like:

SELECT name, address, postCode FROM tbl_estateagents WHERE postcode IN(SELECT postCode FROM tbl_postCodes WHERE userID = @UserID)

If you are using MySQL you cant usea a subqueries, so you'll have to create 2 recordsets, the first to retun the Users Postcodes, and a second to retrieve the estate agent details, something like:

Recordset1.Sourece = "SELECT postCode FROM tbl_postCodes WHERE userID = " & Session("UserID"

Store the Postcodes in a string:


If NOT Recordset1.BOF OR NOT Recordset1.EOF Then

strPostCodes = Recordset1.Fields.Item("postCode".Value
While NOT Recordset1.EOF
strPostCodes = strPostCodes & ", " & Recordset1.Fields.Item("postCode".Value
Recordset1.MoveNext()
Wend


FOO FOO
FOO FOO
Recordset2.Sourece = "SELECT name, address, postCode FROM tbl_estateagents WHERE postcode IN(" & strPostCodes & ""
FOO FOO
FOO FOO

End If ' NOT Recordset1.BOF OR NOT Recordset1.EOF




Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Thank you for your response, I am using Access.
I pasted this into the advanced recordset SQL field.

"SELECT * FROM tbl_agents WHERE PostCode IN(SELECT PostCode FROM tbl_userPostCodes WHERE userID ="&Session("UserID"&""

I made a few changes as table names & columns are slightly different, but I just get error: -

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression 'PostCode IN(SELECT PostCode FROM tbl_userPostCodes WHERE userID =)'.
/postcode/test.asp, line 13

Line 13 in my code is:

rsEstateAgents.Open().

Please bare with my as I am new to ASP & VBscript databaseing.

Thanks for your help so far though.

I have made a little progress but still not working, this is what I have:

SELECT * FROM tbl_agents WHERE PostCode IN (SELECT PostCode
FROM tbl_userPostCodes
WHERE userID = MMColParam)

In the variables I have have:

MMColParam 1 Request.QueryString("MM_Username"

Would the code above filter the tbl_userPostCodes by the userID taken at logon and give me a list of postcodes. Then take the post codes and use them to search tbl_agents and give out records with matching postcodes?

I would like it to find postcodes 'LIKE' the ones from tbl_userpostcodes, what would I add to do this?

Change as follows:

MMColParam 1 Session("MM_Username"

I'm not really sure how "PostCode LIKE" will help, there is no correlation between a post code's text value and its geographical location. Finding postcodes of a similar text value will be nonsense in terms of how close they are to each other.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Thanks for your response.

I have changed as requested, when I use the test button I get no data back and when I try it live I get an error as below:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/postcode/test.asp, line 20

line 20 = rsEstateAgents.Open() - whats wrong here?

I need to use postcode LIKE because in tbl_userpostcodes the post codes will be 'RG1' or SN2' and in the tbl_agents they will be full post codes, such as RG14 5SJ. This may be why I receive 'no data' when I test, but dont know whyI would get an error when live.