Recordset based on two filters

[redbay]

Is it possible to base a recordset on two filters, at the moment it is filtered on ComplaintId and the URL Parameter set as ComplaintID, but i would like to filter on ComplaintID and then customerID. any help please?

 

[Cheech]

Yep switch to advanced in the recordset and you can filter on as many options as you like.

Cheech

[Peace][Pipe]

 

[redbay]

Have tried changing the advanced recordset as so
SELECT *
FROM qryActDet
WHERE ComplaintID = MMColParam AND CustomerID = MMColParam

Anchanged the variable to
Name Default Value Run-Time Value
MMColParam 1 RequestQueryString("ComplaintID")
MMColParam 1 RequestQueryString("CustomerID")

but the page doesnt open and gives me the following error

Error Type:
Microsoft VBScript compilation (0x800A0411)
Name redefined
/mac/actdet.asp, line 32, column 4
Dim rsActDet__MMColParam
---^

 

[Cheech]

Change the name of the MMColParam's to something more relevant, ie. strComplaintID and strCustomerID. The error actually tells you that the variables are being redefined.

Cheech

[Peace][Pipe]

 

[redbay]

I now have this

SELECT *
FROM qryActDet
WHERE ComplaintID = MMColParam AND CustomerID = MMColParam


Name Default Value Run-Time Value
MMColParam 1 strComplaintID
MMColParam 1 strCustomerID

but when i test it i get the message

Syntax error missing operatot in query expression "ComplaintID = 1 AND CustomerID = 1"

ive tried a couple of variations but still get the same message...help!

 

[Cheech]

so your complaintID's and your customerID's are the same?

[Peace][Pipe]

 

[redbay]

No, for example
ComplaintID 105 / CustomerID 129 / Action 203
ComplaintID 106 / CustomerID 129 / Action 199

I'm trying to show all actions against 105 / 129 but i'm getting all actions against customer 129

 

[Cheech]

Where are you getting the 2 id's from? A form? Try using

Name     Default Value   Run-Time Value
strComp  1               RequestQueryString("ComplaintID")
strCust  1               RequestQueryString("CustomerID")

I often find that if you output the querrystring to the page prior to processing it will often give a good clue as to what the problem is. Change your recordset code from

rsComplaints.Source = "SELECT *  ................."
rsComplaints.CursorType = 0
rsComplaints.CursorLocation = 2
rsComplaints.LockType = 1
rsComplaints.Open()

to

rsComplaints.Source = "SELECT *  ................."
Response.Write(rsComplaints.Source)
rsComplaints.CursorType = 0
rsComplaints.CursorLocation = 2
rsComplaints.LockType = 1
rsComplaints.Open()

And see what happens.

[Peace][Pipe]