parameter query for searching for number

[okinawan79]

Hi, I have 2 tables: Table A contains [ID] (and other columns) AND Table B contains [Request ID] (and other columns).

However, [Request ID] contains several number separated by comma. For example: 10, 15, 20 etc.

I created a query with parameter with : Like "*" & [ID] & "*" under [Request ID] criteria.


Here is my problem:

if [Request ID] = 44, 60 then it is returning [ID] 4 records (4, 6, 44, & 60). Is there a way to show only 2 records? 44 & 60?

Thank you

 

[CJ_London]

if your query is accessing the data by using forms!myform!requrstID then this will not work

if you are building your sql or a filter in VBA then build it like this

"ID IN (" & me.requestid & ")"

 

[okinawan79]

It did not return any data.. I think I did something wrong.. here is my sql:

SELECT DISTINCT tblRQ.RQ_USER_13, tblRQ.RQ_REQ_ID, tblRQ.RQ_ID, tblRQ.RQ_REQ_NAME, tblChangeRequest.ID
FROM tblRQ, tblChangeRequest
WHERE (([ID] In (" & [RQ_ID] & ")));

example data:

tblRQ tblChangeRequestRQ_ID ID1, 23 2424, 67 641, 42 67 41

Should return 3 records ...but it was returning 4 records because "6" was found in 67 .

Thank you

 

[CJ_London]

that is a mixture of styles used in queries and vba

if you have something like

 SQLstr="SELECT DISTINCT tblRQ.RQ_USER_13, tblRQ.RQ_REQ_ID, tblRQ.RQ_ID, tblRQ.RQ_REQ_NAME, tblChangeRequest.ID
FROM tblRQ, tblChangeRequest
WHERE (([ID] In (" & me.[RQ_ID] & ")))"
set rst=currentdb.openrecordset(sqlstr)

then providing RQ_ID="44, 60" and is the name of a control on your form

it will only return records where ID=44 or 60 because the where clause becomes

WHERE [ID] In (44, 60)


if you have tried to do that in a query, regret it will not work

 

[okinawan79]

Thank you!