paramaterized queries question? 1

[WilliamMathias]

Hi,

I've got a make table query which is passed a parameter
parDealType of type integer. For various reasons I can't use a where clause.

I want to pass a value for parDealType of "1 or 2"

My query works fine if I'm passing just 1
My query works fine if I'm passing just 2

but if I want all the DealTypeIDs that are either 1 or 2

To Illustrate:

My query has the following column:

Field DealTypeID
table tblCalcs


Criteria [parDealType]


Any Ideas? Will

 

[930driver]

You can't say parDealType is "1 or 2".

You have to use logic like this:

parDealType is 1 OR parDealType is 2 "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[WildHare]

You can't really pass multiple parameter values to a query using the standard [Enter values] kind of parameter prompt. You could set up a form with a multi-select list box control and pass the 1 or more selections:

---------------------------------------------
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))
-------------------------------------------

From DevAshish' Access Web

http://www.mvps.org/access/inde.html

78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[WilliamMathias]

Hi, thanks for your replys:

930Driver:

I've not been clear enough. I want to say:

WHERE dealtypeID = 1 or 2 (using a parameter)

not

WHERE [parameterImPassing] = 1 or 2

WildHare:

If I built a SQL criteria string as you suggest could I pass it as a parameter, rather than using it as a where clause?

Will

 

[RemyS]

If you only have two dealtype ID's (1 & 2) you could have this criteria:

WHERE dealtypeID Like "*" (although you wouldn't need this if there are only 2 dealtype ID's.)

however if there are more you could use:

WHERE (((DealtypeID)=1)) OR (((dealtypeID)=2))

but if you wanted to be really neat, you could use an option box in a form, defaulted to "0" (to represent both 1 & 2) and the following criteria:

WHERE (((dealtypID) Like IIf([Forms]![Optionform]![Optionbox]="0",(dealtypeID)=1 Or (dealtypeID)=2,[Forms]![Optionform]![Optionbox])))

This would enable you to run all three query criteria from one place, using the Form Option box value as your parameter.

Hope it helps. Still new to DB's and enjoying learning day by day

 

[930driver]

Will,

Yes, you can set up your WHERE clause using parameters

WHERE dealtypeID = [parDealType1] OR dealtypeID = [parDealType2]

You will be prompted for the parameter twice even if you use the same parameter name.

RemyS' third example is a good method and I recommend it for your situation as I understand it. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[WilliamMathias]

Hi

Thank you all, I used Remy's version and it worked.

Cheers, Will