Multiple matches as one recordset 2

[PRMiller]

I am having trouble with the correct syntax for a multi-select problem.

I have a query driven by a form. The form allows users to search by client, which is abbreviated as "DIV." The DIV is a three-character abbreviation, consisting of letters and numbers. If a user wants records only for a particular client, they key in the 3-character code and the date range and run the query. If they want all clients, they leave the field blank, select the date range, then run the query.

Where I have a problem is if they want to run the query for TWO clients. Our client list is huge and always changing, so we don't have a combo box for the user to select from. Instead, we let them enter text. I have been unable to find a way to allow them to run more than one (but less than all) clients.

If the two client names were ABC and MNO, I had thought to have the user enter something like "ABC; MNO", but I couldn't figure out how to translate this to the query.

Here's a drastically reduced copy of the query (the driving form is "P_frmSummaryReport"):

SELECT HF_DIV1573, agent, EvalDate, phone, Eval_id 
WHERE (((HF_DIV1573) Like forms!P_frmSummaryReport!txtDIV) And ((EvalDate) Between forms!P_frmSummaryReport!txtFromDate2 And forms!P_frmSummaryReport!txtToDate2) And ((Left([Agent],2))<>"zz")) 
Or (((EvalDate) Between forms!P_frmSummaryReport!txtFromDate2 And forms!P_frmSummaryReport!txtToDate2) And ((Left([Agent],2))<>"zz") And ((Trim(forms!P_frmSummaryReport!txtDIV & ""))=""))
ORDER BY agent DESC;

We need the criteria at the end (graciously provided by PHP) to allow for null values and blank space values entered in the original records if we want the user to be able to truly view all records.

Any help is appreciated.

Thanks,
Paul

 

[dhookom]

To allow users to enter more than one DIV delimited with commas like "ABC,MNO,RST" I create a column/field like:
WhereAt:Instr("," & forms!P_frmSummaryReport!txtDIV & "," ,"," & [Div] & ",")

Set the criteria under this column to:
>0

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PRMiller]

Duane,

Thanks for the reply (and on a Sunday!). Is the number of DIVs that the user can enter limited by the number of commas I put in that statement?

Thanks,
Paul

 

[dhookom]

You shouldn't have to change the expression that I provided. Users would enter values like ABC,RTW,WWW.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PRMiller]

Ok, running into a syntax error (missing operator). I pulled a simpler query, with the actual field names, inserting the code you provided. Here it is:

SELECT WhereAt:InStr("," & forms!P_frmSummaryReport!txtDIV & ",","," & [DBA_EvalHeadSummData116].[HF_DIV1573] & ",") AS HF_DIV1573, DBA_agent.agent, DBA_EvalHeadSummData116.EvalDate

FROM DBA_EvalHeadSummData116 
INNER JOIN DBA_agent ON DBA_EvalHeadSummData116.AgentLink = DBA_agent.agentlink

WHERE (((InStr("," & forms!P_frmSummaryReport!txtDIV & ",","," & [DBA_EvalHeadSummData116].[HF_DIV1573] & ","))>0) AND ((DBA_EvalHeadSummData116.EvalDate) Between [forms]![P_frmSummaryReport]![txtFromDate2] And [forms]![P_frmSummaryReport]![txtToDate2]) AND ((DBA_EvalHeadSummData116.HF_DIV1573) Like [forms]![P_frmSummaryReport]![txtDIV]) AND ((Left([Agent],2))<>"zz")) OR (((DBA_EvalHeadSummData116.EvalDate) Between [forms]![P_frmSummaryReport]![txtFromDate2] And [forms]![P_frmSummaryReport]![txtToDate2]) AND ((Left([Agent],2))<>"zz") AND ((Trim([forms]![P_frmSummaryReport]![txtDIV] & ""))=""))

ORDER BY DBA_agent.agent DESC;

(I did place the ">0" in the criteria box, but then switched to SQL view to edit further,losing that criteria)

After receiving this, I just removed "WhereAt:". Of course, then the query returned a numerical value rather than displaying the actual contents of the cell.

I then tried adding the >0 into the expression itself, as follows:

SELECT WhereAt(InStr("," & forms!P_frmSummaryReport!txtDIV & ",","," & [DBA_EvalHeadSummData116].[HF_DIV1573] & ","),>0) AS HF_DIV1573, DBA_agent.agent, DBA_EvalHeadSummData116.EvalDate

FROM DBA_EvalHeadSummData116 INNER JOIN DBA_agent ON DBA_EvalHeadSummData116.AgentLink = DBA_agent.agentlink

WHERE (((InStr("," & forms!P_frmSummaryReport!txtDIV & ",","," & [DBA_EvalHeadSummData116].[HF_DIV1573] & ","))>0) AND ((DBA_EvalHeadSummData116.EvalDate) Between [forms]![P_frmSummaryReport]![txtFromDate2] And [forms]![P_frmSummaryReport]![txtToDate2]) AND ((DBA_EvalHeadSummData116.HF_DIV1573) Like [forms]![P_frmSummaryReport]![txtDIV]) AND ((Left([Agent],2))<>"zz")) OR (((DBA_EvalHeadSummData116.EvalDate) Between [forms]![P_frmSummaryReport]![txtFromDate2] And [forms]![P_frmSummaryReport]![txtToDate2]) AND ((Left([Agent],2))<>"zz") AND ((Trim([forms]![P_frmSummaryReport]![txtDIV] & ""))=""))

ORDER BY DBA_agent.agent DESC;

Clearly, I am over-complicating things. What did I miss here?

Thanks again,
Paul

 

[PRMiller]

Woop, never mind, I'm an idiot. I was REPLACING my DIV column with your expression, rather than adding it as an additional column. DOH!

I can get the query to return indepent DIVs, but it is not returning records for anything like "Q74,QAG." Hmm... have to keep experimeting here.

 

[PRMiller]

Latest iteration of the query:

SELECT InStr("," & forms!P_frmSummaryReport!txtDIV & ",","," & DBA_EvalHeadSummData116.HF_DIV1573 & ",") AS WhereAt, DBA_EvalHeadSummData116.HF_DIV1573, DBA_agent.agent, DBA_EvalHeadSummData116.EvalDate

FROM DBA_EvalHeadSummData116 INNER JOIN DBA_agent ON DBA_EvalHeadSummData116.AgentLink = DBA_agent.agentlink

WHERE (((InStr("," & forms!P_frmSummaryReport!txtDIV & ",","," & DBA_EvalHeadSummData116.HF_DIV1573 & ","))>0) 
AND ((DBA_EvalHeadSummData116.HF_DIV1573) Like [forms]![P_frmSummaryReport]![txtDIV]) 
AND ((DBA_EvalHeadSummData116.EvalDate) Between [forms]![P_frmSummaryReport]![txtFromDate2] 
And [forms]![P_frmSummaryReport]![txtToDate2]) 
AND ((InStr("," & [forms]![P_frmSummaryReport]![txtDIV] & ",","," & [DBA_EvalHeadSummData116].[HF_DIV1573] & ","))>0) 
AND ((Left([Agent],2))<>"zz")) 
OR (((DBA_EvalHeadSummData116.EvalDate) Between [forms]![P_frmSummaryReport]![txtFromDate2] 
And [forms]![P_frmSummaryReport]![txtToDate2]) 
AND ((Left([Agent],2))<>"zz") 
AND ((Trim([forms]![P_frmSummaryReport]![txtDIV] & ""))=""))

ORDER BY DBA_agent.agent DESC;

Still having an issue with the query returning records. Entering one DIV will return the appropriate number of records. Entering multiple DIVs returns no records at all. I have tried both "Q74,QAG" and "Q74, QAG." Spacing of the characters from the comma doesn't seem to be the issue, so I'm at a loss as to why it is not returning any records.

Any help would be greatly appreciated.

Thanks,
P

 

[PHV]

Get rid of this:
AND ((DBA_EvalHeadSummData116.HF_DIV1573) Like [forms]![P_frmSummaryReport]![txtDIV])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PRMiller]

PHV,

That did it. For my own knowledge, how does the query know to return only records specified in the form?

I know that the InStr function returns the numerical position of the specified character in a string, which we're doing with the field labeled "WhereAt."

I'm not sure how the query can further understand only to take the characters found there, if all we're saying is "select... where the value of WhereAt is greater than 0." Or am I reading this wrong?

Thanks,
Paul

P.S. - PHV, I misspelled your handle in my first post, sorry about that!.

P.P.S. - dhookom and PHV, have a star. This will get an end-user off my back!

 

[PHV]

I'm not sure how the query can further understand only to take the characters found there
Just because HF_DIV1573 is in the SELECT list of fields.
Note: you may get rid of the InStr stuff in the SELECT list if you don't care the WhereAt value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244