I have used a template for a selection criteria form given to me some time ago by a UA member.I am trying to use this type of form to open another form which would use the data selected from my criteria form.

Users select a Division, Department, Location, or Position (or any combination of those fields from listboxes on my selection form.Any employees who match the criteria will then appear in a listbox on the selection criteria form.

I want to use those selected employee records as the data to open another form.

I think the problem is that the EmplID in my other db is numeric as it comes from another system where it is numberic.

In my new db, the EmplID is Text.

Can someone tell me what I need to change to make EmplID text in the following event code?Thank you.Brandi

I have two Subs ( Sub SelectEmployee_AfterUpdate() and (Sub FilterEmployeeList(). I am copying both Subs below. I am not sure which one needs to be changed but qrySelectEmployee is where I need EmplID to be Text.

The user selects the criteria they want from 4 different Listboxes for Division, Department, Location, and Position. Then the code populates the Employee list with the appropriate employees. I believe the code is creating a query called qrySelectEmployees. I think The query doesn't actually exist until the code creates it.So I am trying to figure out where in the code it creates the EmplID field as numeric and change it to Text.

Why create the whole query when all you really need is the WHERE clause (minus the "WHERE" keyword)?

If it were me, I'd forget all about the query nonsense. It's a huge red herring. If the form/report recordsource you want to filter has all the fields in it, you don't need any of the fields in the SELECT statement at all.

You can create the filter and pass it to either a form or a report in the Open command...This post has been edited by MadPiet: Dec 7 2017, 07:30 PM

This link describes using a multi-select list box to filter a report. Although that isn't quite what you are trying to do, it may help you to assemble an appropriate Where condition. It is essentially what MadPiet suggested in the most recent posting, but with a little more detail.

I agree that creating queries is likely to be more than you need in this case, but I am having trouble following the flow of the process just by reading the code.

If you are using the selections in several controls as the criteria for the Employee listing in the list box (that is, to set the list box Row Source), best IMO would be to make the selections, then click a command button, rather than resetting the Row Source after each selection. Again, I'm not quite sure how you are processing Division, etc. into the Row Source criteria.

In any case, you will need to make provisions for any or all of the controls not having entries or selections.

OK. maybe I should start over.This is a form with multiple select listboxes. users can choose one or more Divisions, one or more Departments, one or more Locations, and one or more Positions.Based on their selections, there is another listbox on the form that displays Employee names and their Division, Department, Location, and Position.

They can further select on the Employee Listbox to eliminate other employees from the List,

Once they have the employees they want in the Employee listbox, they press a button to select those records.Then the following code runs to create a query with those records only. These are the records I need as the data for my form.

So it all works, except the query that is created is considering the EmplID to be numeric instead of text. I think it is just a matter of putting quotes somewhere around EmplID but not sure where exactly to put them.

Here is the event code that creates the dynamic query.Thank you.Brandi

the query that is created is considering the EmplID to be numeric instead of text

Do you mean in qryEmployeeSelect? You are selecting from qryEmployeeList, so all I can guess is that it is numeric there. Without seeing the database, or even any sample data, there is really no way to know from here what is happening.

As for the Where condition for the query you are building, you are selecting from qryEmployeeList, but your criteria use tblEmployee, although it is not part of the SELECT statement. I don't see how that can work at all. A Debug.Print strSQL line of code would help evaluate the query. It appears you have something like the following abbreviated construction:

WHERE tblEmployee.EmpID IN (15,22,38,44) AND tblEmployee.Active = True

That is not really the point at the moment, but it is worth considering.

Again, selecting from one domain (qryEmployeeList) and applying criteria from another domain (tblEmployee) that is not included in the SELECT is very likely to cause problems.

Other points: Why ActiveControl? It seems the code is being applied to a specific control, so I would just name it.

I would just assign the SQL as the Record Source at run time, or else open the form and use the Where condition (without the word WHERE) as the OpenForm WhereCondition value. I don't think the QueryDef is causing the problem you describe, but it looks like you are taking the long way around.

EmpID's come from another system where they are Text.An EmpID would be something like 0000117, 0000119, 0000127They are definitely text in qryEmployeeList

I see what you mean about the select statement referring to tblEmployee and I have changed that to qryEmployeeList.I also like the idea of using In instead of OR but not sure how to change that in the SQL below.

When my dynamic query runs, it changes those to 117, 119, and 127. Code for the dynamic query is currently as follows: I am certain this is where I need to designate EmpID as Text but I don't know how to do that. I am not good at writing code and for me it is easier to have this query to use as the control source for another form and also for reports. Would you be able to show me how to change the Or to In? I think it will require some brackets somewhere?This code is just telling me which Employees are currently selected from a full list of employees that comes from qryEmployeeList. I am also including the SQL for qryEmployeeSelect where the records listed above are selected on my criteria form.

SO THE FOLLOWING CREATES THE DYNAMIC QUERY.Private Sub SelectEmployee_AfterUpdate() Dim varItem As Variant Dim strTemp As String Dim strSQL As String Dim db As DAO.Database Dim qry As QueryDef

AND THIS IS THE QUERY THAT IS GENERATED FROM THE CODE ABOVE. Here is the SQL for qryEmployeeSelect where it has changed the text EmplId's from qryEmployeeList to numbers. It is stripping the leading zeroes. And some EmpID's start with the word Temp if it is an acquisition. IN THAT CASE THE QUERY PROMPTS FOR A FIELD NAMED [Temp01] as an example vs. using Temp01 as the value in Criteria.

I am attaching a sample database. For purposes of the sample only 4. Budget Merit matrix will work.this is the area in question. You can select employees by filtering on Division, Department, Location, Position and even further select out employees from the employee list.Once you have the employees you want, press the Select all button above the employee list. This creates the dynamic query, qrySelectEmployee.

If you look at the query, you will see that it is changing text to numbers and I can't figure out why.

Thank you for the link. I have read it and it seems to be very similar to the code I am trying to modify. I sort of understand code when I read it but the syntax is always difficult for me.

I am trying to modify the code to use the In statement but so far I don't have the right syntax. Does anyone see my mistake?

Ultimately I want the dynamic query to create a criteria such as In("001","002","Temp01")

The old code which is commented out in the following was giving me 1 or 2 or [Temp01] where it thought Temp01 was a field and was prompting me for it's value.My new code underneath the old code is attempting to change the old code to give me the In string but it is not working. I added a line above the loop that I think would start the strTemp with In(

Note the modifications - I wrapped the result of For Each varItem In Me.ActiveControl.ItemsSelecteda little bit.

You need to wrap each of those values returned in single quotes, because EmpID is defined as a string, not as some kind of number. You many need to clean up some of that at the end of your code so that your query is right (the trimming off excess characters part may be off a little). You can use Debug.Print strSQL and then copy & Paste that into a query and see if it works.

CODE

Private Sub SelectEmployee_AfterUpdate()

Dim varItem As VariantDim strTemp As StringDim strSQL As StringDim db As DAO.DatabaseDim qry As QueryDef

Thank you so much for your help. I think I am getting close. So far I am getting an error. I changed the last part that used to drop off the last 4 characters to strip off the last 1 character.Here is the error I get. It looks like it is getting the right EmpID's 009 and 018 but I'm not sure what it doesn't like.

IN ('string1', 'string2','string3') (strings are going to be in single quotes.)orIN (1,3,4) -- numbers have no delimiters.

take a step backward and play with IN() for a few minutes and get that part working. Once you get it working on a simpler example, come back and fight with this one. The big thing is to get your head around how IN() does and does not work first. Then you can apply that to what you're doing.

Here's a really simple example. (I have a form with a multi-select listbox and a button that builds the filter string using IN ()

I like your idea of making the quote a constant. That really helps me understand. I am trying to make the criteria that is dynamically created say something like In ("001","002","Temp07")When I just type in the criteria myself it does select the correct records.Thank you for your help. I am so sorry that I am not getting this!

I also made a CQuoteComa constant to put the comma after each empID.

Here is what I have now. (I just kept my dim names from before but tried to follow your logic. It now give me an error 424 Object Required and highlights this line in the event procedure.strTemp = strTemp & cQUOTE & (qryEmployeeList.EmpID) = cQUOTE & Me.ActiveControl.ItemData(varItem) & cQuoteComma