By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 920 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

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

P: 2
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
Sep 5 '07 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,324
Use Like FormControl & "*" instead.
Sep 5 '07 #2

Rabbit
Expert Mod 10K+
P: 12,324
Resuscribing .
Sep 5 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.