Parameter query and using AND function

[spn200286]

(I have made a similar thread before, however this sites the problem more clearly)

I have a parameter querie, im using the instr function so that i can type in 1,2,4 and it shows me every1 with option 1 or 2 or 3, however i want it to show only who has 1 and 2 and 3

below is the function i am using

InStr([Enter Skills Seperated by Commas: 1,3,7],[Job_Skill_Number])

hopefully its a simple edit, any help anyone can give would be apreciated
if more info is needed please say

 

[Galaxiom]

Without knowing more about the way you record the values in the table it is difficult to suggest anything. There is probably some way using multiple parameters.

However I would recommend you start using forms to enter your parameters. It is a much more civilized way to interact than dealing with parameter entry boxes. For one it means you don't have to retype them every time you run the query.

 

[namliam]

Not a simple edit by any means and dont think it is even possible in this way...

 

[spn200286]

Cheers for your fast replies, well i guess my quick fix wont be as quick as i had hoped ah well give me a chance to brush up on my access skills which i have been neglecting

as to how i record my values

A clients has a set of skills

these skills are recorded by giving each skill a skillno(using autono for the ease but this can be changed(also the PK)) and then linking them into a subform so a list of skills can be amased

i then want to pull clients who have say driving, brick laying and fork lift truck licence and only show who has all of these, but the parameters can change each time (hense my thinking a parameter querie would work)

 

[spn200286]

Added a prototype of the database if that clears up any thing i have missed

 

[Galaxiom]

Query with the three separate parameters going into:
WHERE [fieldname] = [parameter1] OR [fieldname] = [parameter2] etc.

(Or maybe even with your InStr trick.)

Then Group By the PersonID and derive a Count field. Those with three records meet the criteria.

 

[Banana]

Galaxiom has given you a possible solution by using grouping & counting via having.

The trouble with InStr is you are negating any opportunity for index optimizations; Access will have no choice but to run a full table scan. Perhaps the table is so small it's not noticeable but when it get to a certain size, it's going to get slower and slower.

At the heart of problem, we want a set of optional parameters since we don't know how many of them we actually need. For this requirement, parameter query is ill-suited, and WhereCondition or dynamic SQL is going to be a better fit.

If you are showing the result in a pop up form, you can make use of WhereCondition in the OpenForm method. On your first form where your user would enter the criteria, you could do something like this:

Dim v As Variant
Dim x As Variant
Dim a As String
Dim s As String

v = Split(Me.MyCriteria, ",")

For Each x in v
   s = s & a & " [FieldName] = " & x
   a = " AND " 
Next

DoCmd.Open acForm, "TargetForm", WhereCondition:= s

But maybe you have a subform or something where OpenForm (and thus WhereCondition) is not accessible. In this case, dynamic SQL is what we do. Basically same code to loop and collect the parameters then:

s = "SELECT ... FROM ... " & s
Me.MySubform.Form.Recordsource = s

However you do it, this will be much better optimized.

I hope that helps.

 

[spn200286]

Belated thanks to both of you for your help

I shall try both methods and see which one i feel better with

Im rusty on my VBA so relearning it aswe speak, so many pose one or two more questions if it all goes horribly wrong