Paramater Query , Applying Criteria Multiple Numbers

[CORINEREYES]

Hi,




Good day to everyone.


I have this issues applying criteria to a Number. Example a user will input multiple numbers (employee numbers) to the parameter query in a query in access. How do i do it? I have searched for many topics however it only discuss text and not numbers.


I have attached a screenshot for reference.


PLEASE HELP?


Thank you in advance.


Maria

[Welshgasman]

Use IN() for the criteria.
Always better to use a form for criteria, as then it can be validated before use.

[CORINEREYES]

Use IN() for the criteria.
Always better to use a form for criteria, as then it can be validated before use.

In ([Enter the ID values separated by commas:]) 

I have used this function already but it does not work?

this my SQL for the query

SELECT [Employee Details].[STAFF ID] INTO MultiTrans
FROM [Employee Details]
WHERE ((([Employee Details].[STAFF ID]) IN ([Enter the ID values separated by commas:])));

[Welshgasman]

Start off with a simple SELECT until you get it working.
Small steps, one at a time.

I cannot see why that would not work, I have used that syntax many times with out issues?

strRptQuery = "qryEmployeeHours"
Set ctl = Me!lstDayType
'Open the report with an OpenArgs value to get selected hours for employee
' Now select what type of day records from listbox
If ctl.ItemsSelected.Count > 0 Then
    For Each varItem In ctl.ItemsSelected
        strParam = strParam & ctl.ItemData(varItem) & ","
    Next varItem
  Else
    MsgBox ("At least one Day Type is required")
    Exit Sub
End If


' Need to pass the criteria as a string
strParam = " IN (" & Left(strParam, Len(strParam) - 1) & ")"



strParam = "[DateType]" & strParam
DoCmd.OpenReport "rptHours", acViewReport, , strParam, , "Selected"

[mike60smart]

I would suggest you explain what you are trying to do with your database.

From your screenshot you have multiple tables for Employee Details?

[CJ_London]

you can't use parameters in that way - WGM's suggestion provides a filter which you can't do within the context of a query

Only way would be to use the eval function - and this assumes the staff id is numeric and not text

WHERE eval("" & [STAFF ID] & " IN (" & [Enter the ID values separated by commas:] & ")")=True

However this is a very risky way of using a query - what if the user misses a comma?, types the wrong number?

[Micron]

IMO to use IN for this and do it reliably would be to multi select from a form listbox. If there would be many to be chosen and that would present an issue due to scrolling through a list, then a pair of listboxes and the ability to move selected items to the "chosen" listbox is another way. However, using listbox(es) means that the sql needs to be constructed in vba.

[CORINEREYES]

I would suggest you explain what you are trying to do with your database.

From your screenshot you have multiple tables for Employee Details?

Hi!

Those are only previous tables that i do not want to delete, this is my test database only.

The query in the picture is an Append query, it takes records from a database named Employee Details and appends the records in a table named MultiTrans by a user entering the employee number he/she may wish. However a parameter in the criteria can only hold 1 number. I would like for a user to input as many numbers as possible.

[CORINEREYES]

you can't use parameters in that way - WGM's suggestion provides a filter which you can't do within the context of a query

Only way would be to use the eval function - and this assumes the staff id is numeric and not text

WHERE eval("" & [STAFF ID] & " IN (" & [Enter the ID values separated by commas:] & ")")=True

However this is a very risky way of using a query - what if the user misses a comma?, types the wrong number?

Hi!

This works perfectly fine! I will have to dealt later on if a user has mistakes in entering.

Thank you so much for the help!

[CORINEREYES]

Thanks all for the suggestions, i will go through all of them. thanks again for the guidance,

[moke123]

A little unusual to have 25 tables of employee details.
You may have some normalization to do.

[CJ_London]

answered in post#8

[moke123]

Hi!

This works perfectly fine! I will have to dealt later on if a user has mistakes in entering.

Thank you so much for the help!

It's not a question of "if" they're going to make mistakes. They certainly will.

As already suggested, A multi-select list box or a pick list are some of the best ways to keep users from entering bad data.

here's an example of how to generate the "In Clause"

[CORINEREYES]

It's not a question of "if" they're going to make mistakes. They certainly will.

As already suggested, A multi-select list box or a pick list are some of the best ways to keep users from entering bad data.

here's an example of how to generate the "In Clause"

Hi moke123, thank for the examples, i will incorporate this to my project if necessary.