Input multiple Integer values as parameter at IN clause (1 Viewer)

[Shinta]

Greetings:

I'm trying the following statement to work:

SELECT myField FROM myTable WHERE IN ([Forms]![MyForm]![myFormField]);

This is done at the Query designer; as it can be seen, it is dependent of a field at a Form. By far, this works only if the field contains a single value (in this case, Integers). I've tried to pass an Array of Integers and a String List, with no luck.

Could you please help me with a way to pass several values that the IN clause could interpret?

Thanks a lot in advanced; regards,

 

[Ranman256]

you don't need the IN. IN is only for referencing another query.
you just want a where
SELECT myField FROM myTable WHERE [Field2] =[Forms]![MyForm]![myFormField]

 

[plog]

First, the IN keyword needs to appear after a field:

myField IN ([Forms]![MyForm]![myFormField])

Second, you can't do it like that. Whatever you type in your form is going to act like one string. So if you type 3 numbers in your form:

1, 2, 3

This is what your WHERE clause will look like that is interpreted:

myField IN ("1, 2, 3")

To a computer that IN only contains 1 value--everything inside the quote marks. What you want it to look like to search those 3 fields is this:

myField IN ("1", "2", "3")

And you just can't get there from the form. My advice is to build a report based on your query without criteria, then open the report using VBA and at that time you can apply a filter. You will want to use the DoCmd.OpenReport: (https://msdn.microsoft.com/en-us/library/office/ff192676.aspx)

 

[Shinta]

Hello Ranman256!

Thanks a lot for stopping by and help; I receive the next message: "The expression is too complex to be evaluated". The phenomena is the same: if I put a single Integer, it works, several, fails.

Any ideas?

Thanks a lot in advanced

p.s. It all seems that plog's reply came at the same time that I was doing my post... if we take his considerations, then there won't be a way to send multiple numeric data at the Query from the Form (will be always be taken as String)..

 

[Shinta]

What I'll have to do (I wanted to avoid it: it seems not possible), is to make a Query for *each* numeric combination; that is:

1 query for: IN (17)
1 query for: IN (1,8,16,30)
etc..

Thanks a lot for the help... I've for a limit and it's important for the considerations in my designs.

If any have got any other suggestions, they are deeply welcomed

Regards,

 

[jdraw]

Can you step back and tell us what you are trying to accomplish in simple English terms?

What is special about 17? That is, based on your last post, you suggested 2 queries 1 for 17 and 1 for any of 1,8,16,30.

 

[Shinta]

Greetings jdraw:

Sorry for my clumsy description of my need. I'll try to be as clear as my English is capable to explain:

I've got a Table with several registries, from which they are classified with a special ID (not the pk). I wish that the user, after selecting a element from a ComboBox, a Query previously created at the Query designer will be triggered based on the selection. This element from the ComboBox will represent a group of these special ID's {let’s say: Black = (2,3,4,56), Blue = (9,10,11,38), and Red = (17)}. This Query has an “IN” clause, which is what I pretend will retrieve the data classified by a parameter, via the help of a hidden TextBox and some VBA, or any other better idea (this already works with independent queries, but I wished to do it in a single one, thanks to the parameter).

I hope that my description is more accurate and detailed.

Thanks again for the interest.

 

[plog]

let’s say: Black = (2,3,4,56), Blue = (9,10,11,38), and Red = (17)

You shouldn't be storing data in queries, which is what you are effectively doing. If you need to resolve numbers to colors, then you need a table to do so:

Colors
ID, Color
2, Black
3, Black
17, Red
11, Blue
...
...

Then you bring that table into your query, link it appropriately and now every record resolves to a color and you can then query via that field. You could even have an input where people input a color and the query opens to the appropriate records.

 

[jdraw]

I agree with plog' comment re storing data in table.
However, I'm still having difficulty understanding what you are doing.
My best guess at the moment:

You have a list of records.
The user who is reviewing the records, assigns some ID which is selected form a combo, and this ID has a special meaning eg GroupIdentifier.

 

[Shinta]

Greetings:

Mmmm... I see. I can do like a sort of *auxiliary table* for the relationship (or "translation") of the ID's to Strings.

So, I guess that my approach was out of place. The answer is, then, that Access will always input as Strings a parameter to a Query, isn't it? (just for the sake of learning ).

Thanks a lot to all of the consulters: you are of deep help

 

[jdraw]

Shinta,

I have a feeling that this link is part of what you are seeking, or will give you some additional info.

 

[Shinta]

Hello jdraw!

Exactly! This is my need (only that with numbers, not Strings).

I can see that the suggestion is to read the values of a List and then create the final Query. Can that be done at the Query designer? or just by VBA + Report? (as suggested before by plog).

Thanks again for the real intention to help.

Regards,

 

[jdraw]

You can certainly work with a listbox --multi select, and using vba you can construct the IN clause with numbers for your query WHERE clause.

I will try to find some examples, but it would be more efficient if you could post
-a clear statement of what you are trying to do
-a copy of your database(compact and repair, then make a zip and attach to a post.

Good luck

 

[jdraw]

Shinta,

I found a function that does the creation of an IN clause based on your selections made on a listbox.
I have created a demo database (attached).
In this database is :
-a table animal with ID and Names
-a Form containing :
-------a Listbox (populated with Animal names)
-------a textbox showing a mockup of your base SQL
-------a label telling your to select 1 or more entries in the listbox, then click the button
-------a button that calls a function using your listbox selections, then creates an IN clause, then updates your base SQL with the in clause ( the brackets and comma separated integers)
-a module that I got from Bytes and modified a little for your demo. There are comments within the module/function.

I hope it helps you.
Good luck with your project.

 

[Shinta]

Hello jdraw

Thanks a lot for the detailed interest in all this. Right now, I'm uploading a DB that models directly my issue. I've tried to translate to English as much as possible.

As it can be seen, the idea is that at InventoryForm, the user could "configure" the Query, and then, run it.

I've put as well the separated queries that does the work individually, and my try of a "dynamic query" based on a hidden TextBox at the Form, which would hold the user selection and, in such fashion, the ID's that correspond to such.

I'll study with calm the DB that you've uploaded based on a list.

Thanks a lot!

Regards

 

[jdraw]

I'm looking at your database. I see auxIDMat.

I think a lot of this could be automated, but I need more details. You say this is

a DB that models directly my issue

.

I do not understand the database structure. Just one table???
Please provide more info about the underlying business this database supports.


These metals should probably be in a separate table
metal
[IMMEX] Placa y Estructura
Acero Inoxidable
Aluminio Delgado
Aluminio Grueso
Aluminio Grueso / Fierro
Antimonio
Basura
Bote
Bronce
Cobre 1°
Cobre 2°
Fierro
Fierro (Mixto)
Fierro (Placa y Estructura)
Fierro (Rebaba)
Lámina
Lámina (Automóvil)
Lata de Lámina
Otros (Victaulic)
Otros (Victaulic-Alimentadores)
Otros (Victaulic-Caterpillar)
Perfil 1°
Perfil 2°
Radiador (Aluminio)
Radiador (Bronce)
Radiador (Raco)
Rin (Aluminio)

Also Family as a separate table
family
Acero
Aluminio Delgado
Aluminio Grueso
Antimonio
Basura
Bote
Bronce
Cobre
Fierro
Lamina
Perfil
Radiador
Rin
Victaulic

and a relationship between Family and Metal

How do you maintain this code?

If (Me.materialComboBox.Value = "Black") Then
  Me.auxIDMat.Value = "2, 3, 4, 5, 6, 7, 8, 56, 58"
ElseIf (Me.materialComboBox.Value = "Blue") Then
  Me.auxIDMat.Value = " 13, 14, 15, 23, 34, 25, 27, 31, 57"
ElseIf (Me.materialComboBox.Value = "Red") Then
  Me.auxIDMat.Value = "9, 10, 11, 12, 16, 18, 19, 20, 21, 22, 26.28, 29, 30, 32, 33"
Else
  Me.auxIDMat.Value = "17"
End If

If English is not your native language, write your description then translate to English using Google translate.


Cómo aceptable es esto?

No entiendo la estructura de base de datos. Sólo una mesa ???
Sírvanse proporcionar más información sobre el negocio subyacente es compatible con esta base de datos.

Si el Inglés no es su lengua materna, escribir su descripción a continuación, se traducen en Inglés utilizando Google Translate.

 

[Shinta]

Hello again jdraw:

This is getting somehow "hairy" n.n"... lets see...

The uploaded DB models my issue in the sense that, I believe, it would be too excessive to upload the whole business DB (besides, it would be too heavy in MBs), to show the problem itself (too much stuff outside the main thing).

So.. mm... ok... yes, the info I need to exploit is in a single Table. This table integrates several business movements with materials and its weight (in Kgs), previously captured by other users. What I wish to do is that my Queries will "filter" the info in the table based in the criteria at the Form, which will in return concentrate several ID's based on the user selection. This would happen as follows:

* The raw info is in the Inventory Table (in this case, from the 1st of July/2016 until today)
a) The user will define the date margins for the query (With hours if needed).
b) The user will define if is interested in the Input or Output of the material from the Inventory.
c) The user will define the "family" (or color), of the materials, so the Query will know which materials return.

As all the materials has their own unique ID, I can do the filter based in the Color, transparent for the user the internal ID's. The matrix of materials and colors is as follows (coded in VBA at the DB uploaded, or yet, at each individual Query):

Black: 2, 3, 4, 5, 6, 7, 8, 56, 58
Blue: 13, 14, 15, 23, 34, 25, 27, 31, 57
Red: 9, 10, 11, 12, 16, 18, 19, 20, 21, 22, 26.28, 29, 30, 32, 33
Other: 17

As you can see, the individual queries already do the job... my wish is to understand how can I integrate all this in a single query (it was to me "obvious" that if the IN clause works directly with a sequence of values - in this case, Integers -, then, It would work in the same fashion with a parameter, but it seems that it would rather being impossible in a direct way as it will only receive a single String as parameter, unless we create a concatenated parameter, which I don’t have idea how to send it to the actual query besides this auxiliary TextBox).

... amm.. I don't know what else to say; is it still so blurry all this?

Thanks a lot for the patience and interest: this forum does not let me down with its professionalism in all the time I’ve been here

Regards,

 

[jdraw]

OK.
Tell me about
Blue: 13, 14, 15, 23, 34, 25, 27, 31, 57
what are the 13,14.....? How does it get to 57?
Where do these come from within your database?
How often do they change?

I saw Family and metal_id in your table, but as in my last post ---I do not know what these represent.

 

[Shinta]

Ok... ok... amm...

"Family" represents a sort of category for the materials. It is different than the "Color" (which represents another paradigm for categorization). It could be created another field called "Color", I guess, and such category would impact the materials as well.

The numbers in the matrix showed are the IDs from the Materials (field "idMetal"). This values comes when the users captures the different movements that affect this Inventory Table. This ID's do not change with the time (well, besides an abrupt boss request but, in general terms, they should keep static).

I'm uploading the DB again, which include the Catalog of Materials.

Regards,

 

[jdraw]

I have reviewed what you have been doing with the Inventory form.

Below is a replacement procedure for the materialComboBox_AfterUpdate()

I have tested the code with your latest database and it works. At least it produces results.
You can check it, and post questions as necessary.

I renamed your procedure and built the one below. You can paste it directly into the code behind your Inventory form.

Note:
For this procedure to work there has to be a saved query named "TheQuery". Access will look for this as the name of a saved query. You can create such a query by going to create query, sql view and paste
SELECT * FROM METERIALS;
then save the query as "TheQuery"

You can change the name, and if you do, you have to modify lines 140 and 150 in the procedure to the name you have given to "theQuery".

Also note that you must close the query, before running your Inventory form multiple times. Run the form, review the query. Close the query, run the inventory form....

'---------------------------------------------------------------------------------------
' Procedure : materialComboBox_AfterUpdate
' Author    : mellon
' Date      : 08/07/2016
' Purpose   : modified procedure to create a common query skeleton that can be modified
' based on selections on the Inventory form. This uses the SQL for a standard query
' -the select and the Order By. This routine creates the INClause based on your Color choices.
' The routine also also assembles the common sql and the custom InClause to update the SQL of a query.
' And then opens the query.
'
' This routine replaces the SQL in a query named "TheQuery" each time it is executed.
'---------------------------------------------------------------------------------------
'
'Note: You must have a dummy query saved in your database. This query can be quite simple.
' for example - it could be
'  SELECT * FROM METERIALS;
'It just has to exist and you must know the name. By default I am using "TheQuery" as the name.
'You can change the name, but if you do, you have to change the name in line 140 and 150 of this procedure!!!
'
Private Sub materialComboBox_AfterUpdate()

    Dim CommonSQL As String
    Dim INClause As String
    Dim CommonOrderBySQL As String

    'This is the SQL common to all of your queries
    On Error GoTo materialComboBox_AfterUpdate_Error

10  CommonSQL = "SELECT movement, metal, net," _
                & " total_amount AS total_weight, reference_number" _
                & " FROM InventoryMovements " _
                & " WHERE movement Not Like '*Transferencia*' AND " _
                & " movement_type = '" & [Forms]![Inventory]![movTypeComboBox] & "' AND " _
                & " movement_date BETWEEN #" & [Forms]![Inventory]![beginDate] & "# AND #" _
                & [Forms]![Inventory]![endDate] & "#  AND " _
                & " InventoryMovements.idMetal "

    'This is the Order By info common to your queries
20  CommonOrderBySQL = " ORDER BY InventoryMovements.movement, InventoryMovements.metal, InventoryMovements.reference_number;"

    'Creating the IN Clause based on your COLOR "categorization"
    
30  If (Me.materialComboBox.Value = "Black") Then
40      INClause = " In (2, 3, 4, 5, 6, 7, 8, 56, 58) "
50  ElseIf (Me.materialComboBox.Value = "Blue") Then
60      INClause = " In (13, 14, 15, 23, 34, 25, 27, 31, 57) "
70  ElseIf (Me.materialComboBox.Value = "Red") Then
80      INClause = " IN (9, 10, 11, 12, 16, 18, 19, 20, 21, 22, 26.28, 29, 30, 32, 33) "
90  Else
100     INClause = " IN (17) "
110 End If

    'These 2 lines for debugging
120 Debug.Print INClause
130 Debug.Print CommonSQL & INClause & CommonOrderBySQL

    'This modifies the SQL for the saved query named "TheQuery".
    'It builds the InClause based on the combo selection and then pulls the parts of the
    'query sql together.
140 CurrentDb.QueryDefs("thequery").SQL = CommonSQL & INClause & CommonOrderBySQL

    'This opens the query with the latest SQL
150 DoCmd.OpenQuery "TheQuery" 'This is the default name

    On Error GoTo 0
    Exit Sub

materialComboBox_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure materialComboBox_AfterUpdate of VBA Document Form_Inventory"
End Sub

Good luck.