Using a Parameter In() Statement with values from a form

[Northern Eagle]

Hi, I have created a query that uses a value selected from a list box in a form. The problem is that I need the query to accept multiple parameters as well as individual ones. I tried to make the default value for the list box "value1" Or "value2" Or "value3" so users could leave it blank to return all 3 but this didn't work.

I know from looking at the Microsoft support website (KB210530) that you can create two types of Parameter In() Statement that allow you to supply multiple parameters to a query but I don't know how to combine either of these with a form.

This is the SQL from the query:

SELECT tblTests.CRN, tblTests.PAT, tblTests.TESTED, tblTests.GENDER, [KR CONVERT XLOC CODES].OUT AS XLOC, tblTests.CHLM, tblTests.GONN, tblTests.CAT, tblTests.AGE, tblTreatment.TREATED, tblTreatment.NURSE, qryPostcode.Prefix, qryPostcode.Suffix

FROM (((((tblClinicDetails RIGHT JOIN (((tblTreatment RIGHT JOIN tblTests ON tblTreatment.CRN = tblTests.CRN) LEFT JOIN tblTreatmentClinic ON tblTreatment.TTMTCLIN = tblTreatmentClinic.[LOC CODE]) LEFT JOIN [KR CONVERT XLOC CODES] ON tblTests.XLOC = [KR CONVERT XLOC CODES].[IN]) ON tblClinicDetails.XLOC = [KR CONVERT XLOC CODES].OUT) LEFT JOIN qryPostcode ON tblTests.CRN = qryPostcode.CRN) LEFT JOIN tblPCT ON tblTests.PCT = tblPCT.PCTCode) LEFT JOIN [LOCAL CURRENT POSTCODES] ON (qryPostcode.Prefix = [LOCAL CURRENT POSTCODES].[POST PRE]) AND (qryPostcode.Suffix = [LOCAL CURRENT POSTCODES].[POST SUFF])) LEFT JOIN PCTS ON [LOCAL CURRENT POSTCODES].PCT = PCTS.[Organisation Code]) LEFT JOIN tblPCT AS tblPCT_1 ON tblClinicDetails.PCT = tblPCT_1.PCTCode

WHERE (((tblTests.TESTED) Between [Forms]![fDOHRpt]![DOHFrom] And [Forms]![fDOHRpt]![DOHTo]) AND ((tblTests.CHLM)="P") AND ((tblTests.CAT)="s01" Or (tblTests.CAT)="s04" Or (tblTests.CAT)="s09") AND ((IIf(IsNull([Organisation Name]),IIf(IsNull([tblPCT].[PCT]),IIf(IsNull([tblPCT_1].[PCT]),"UNKNOWN PCT",[tblPCT_1].[PCT]),[tblPCT].[PCT]),[Organisation Name]))=[Forms]![fDOHRpt]![PCTList])) OR (((tblTests.TESTED) Between [Forms]![fDOHRpt]![DOHFrom] And [Forms]![fDOHRpt]![DOHTo]) AND ((tblTests.GONN)="P") AND ((tblTests.CAT)="s01" Or (tblTests.CAT)="s04" Or (tblTests.CAT)="s09") AND ((IIf(IsNull([Organisation Name]),IIf(IsNull([tblPCT].[PCT]),IIf(IsNull([tblPCT_1].[PCT]),"UNKNOWN PCT",[tblPCT_1].[PCT]),[tblPCT].[PCT]),[Organisation Name]))=[Forms]![fDOHRpt]![PCTList]));

I'm working with Access 2003 and Windows XP Professional.

This is the first time I've posted on this forum so if I've missed out anything you need to help then please let me know.

Thanks.

Paul

[Rabbit]

Use Like FormControl & "*" instead.

[Rabbit]

Resuscribing .