Parameter Query

[joesmithf1]

Hi,
I know how to create a parameter query where users could enter ONE criteria each time for a particular field of a table. Please see codes below. Now is there a way I can allow users to enter more than one criteria SEPARATED with commas for a particular field of a table?

Thank you.

Joe



"SELECT DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, First(DEDPARMS" & payp & ".DEDPLAN_CD) AS FirstOfDEDPLAN_CD, First(DEDPARMS" & payp & ".DEDTYPE_CD) AS FirstOfDEDTYPE_CD, " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='R',[overded_am],0)) AS [Employer Amt], Sum(IIf(Right([Dedetail" & payp & ".Dedtype_CD],1)='R',[Dedetail" & payp & ".ded_am],0)) AS [Employer Actl], " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='A',[overded_am],0)) AS [Admin Amt], Sum(IIf(Right([Dedetail" & payp & ".Dedtype_CD],1)='A',[Dedetail" & payp & ".ded_am],0)) AS [Admin Actl], " & _
"Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='E',[overded_am],0)) AS [Employee Amt], Sum(IIf(Right([Dedetail" & payp & ".Dedtype_CD],1)='E',[Dedetail" & payp & ".ded_am],0)) AS [Employee Actl], " & _
"First(DEDPARMS" & payp & ".STATUS) AS FirstOfSTATUS, First(DEDPARMS" & payp & ".AGENCY) AS FirstOfAGENCY, First(DEDPARMS" & payp & ".ORG) AS FirstOfORG, First(DEDPARMS" & payp & ".TITLE) AS FirstOfTITLE, " & _
"First(DEDPARMS" & payp & ".STTL) AS FirstOfSTTL, First(Right(DEDPARMS" & payp & ".title,2)) AS RepUnit, First(Left([DEDPARMS" & payp & ".DEDTYPE_CD],2)) AS Type, " & _
"First(Left([DEDPARMS" & payp & ".DEDTYPE_CD],2)) AS LeftType, First(DEDPARMS" & payp & ".DEDTYPE_CD) AS FirstOfDEDTYPE_CD1, " & _
"First(Right([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Tier, First(Left([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Carrier, Plan.PlanDesc, Plan.TypeDesc " & _
"FROM (DEDPARMS" & payp & " LEFT JOIN Dedetail" & payp & " ON (DEDPARMS" & payp & ".DEDPLAN_CD = Dedetail" & payp & ".DEDPLAN_CD) AND (DEDPARMS" & payp & ".FORMAT_NM = Dedetail" & payp & ".FORMAT_NM) " & _
"AND (DEDPARMS" & payp & ".DEDTYPE_CD = Dedetail" & payp & ".DEDTYPE_CD) AND (DEDPARMS" & payp & ".EMP_ID = Dedetail" & payp & ".EMP_ID)) LEFT JOIN Plan ON (DEDPARMS" & payp & ".DEDPLAN_CD = Plan.Plan) AND (DEDPARMS" & payp & ".HLTH_TYPE = Plan.Type) " & _
"GROUP BY DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, Plan.PlanDesc, Plan.TypeDesc " & _
"HAVING (((First(DEDPARMS" & payp & ".STATUS)) Not In ('P')) AND ((First(Right(DEDPARMS04.title,2)))=[Enter a Repunit]) AND ((First(Left([DEDPARMS" & payp & ".DEDTYPE_CD],2))) In ('01')) AND ((First(Right([DEDPARMS" & payp & ".DEDPlan_CD],2)))<>'00' And (First(Right([DEDPARMS" & payp & ".DEDPlan_CD],2)))<>'17')) " & _
"ORDER BY DEDPARMS" & payp & ".EMP_ID, First(DEDPARMS" & payp & ".STATUS);"

 

[namliam]

Use the IN operator

 

[Moniker]

I'm not sure an IN operator accomplishes what he wants. He wants, for example, three parameters, but only one prompt for them. For example, "Enter First Name, Last Name, and Age" could be answered in one parameter as, "Joe, Smith, 30".

While you could potentially program that, it's a pain and their are much better ways to do it. Build a simple form to capture all the information you want to capture. I'll stick with my first name, last name and age example. Link that form to a table called "t_query_lkp". (The "lkp" is short for lookup.)

Then in the query, for each of those fields, use a DLookUp in the criteria section of each appropriate field, like this:

FirstName Field Criteria: =DLookUp("FirstName","t_query_lkp")

etc.

This would be cleaner with dynamic SQL. Search the forums for that term for examples.

 

[joesmithf1]

Thank you Moniker for the explanation! Actually, what Namliam suggested is exactly what I wanted; just mulitple entries for a specific field in a table(not multiple fields).

Now maybe you guys could help me with this. I changed the codes to IN([Enter Repunit]), then when I open the form and input mulitple entries(separated by commas), I get no results. But if I just input one entry, it works. Am I missing something?

Thank you.

Joe

 

[Moniker]

What you're missing is that commas don't mean anything to the parameter. For example, let's say this is your table:

Bob
Joe
Mary
Bob

If you have a parameter to filter on the name, just putting in one of the names will return just that name. Typing in "Bob" into the parameter will return the two records with "Bob" on them. However, if you type in anything that's not in the list, you will get no records returned. For example, if you type in "Joe, Mary", then Access is going to literally look for the value "Joe, Mary" in that field, which it won't find.

Parameters in Access cannot be value lists like this. Access takes the parameter literally, and it will not parse out comma separated values. In query talk, Access is literally looking for this:

NameField In ("Joe, Mary")

Joe and Mary are not separate values in this case. I think you were thinking it was translating into this:

NameField In ("Joe", "Mary")

Access is not doing the latter case there.

The solution to automatically be able to do this is programmatic. You could use a form to have your users enter a list of values (1,2,3,4,5) and then you would have to discreetly place each value into your IN statement. You could separate out the values using the SPLIT function to get the discreet values. For text values, you'd have to wrap each entry in single quotes. Run this as SQL straight out of VBA and you have a solution.

I get what you're trying to do here (now), but you've come up against a brick wall when trying to use parameters in such a fashion. You'll see that if you separate each parameter, it will work. As a test, make three parameters and enter three distinct values, like this:

Expr1: Bob
Expr2: Joe
Expr3: Mary

In the NameField criteria, put In([Expr1], [Expr2], [Expr3]). That will work, although it defeats the purpose of comma separation. However, it also gives an example of what I mean about having discreet values.

 

[joesmithf1]

This make sense. Thank you very much, Moniker! I was hoping i wouldn't have to program it. I'll try your method.

Joe