Querying Multi Line Text Box in MS Access Forms

[spindlebeakin10]

Hello All,

I'm having difficulty setting up a query with the functionality to pass multiple lines of values in a text box on a MS Access Form to find the matches in a SQL Database connected via ODBC.

At a high level, essentially what I'm trying to set up is a user friendly form where someone with no knowledge of access can open up the database and paste in employee numbers (from excel, notepad, etc.) and retrieve all the records tied to that employee number.

Setting this up for searching for one employee number at a time is no problem, but the hiccup for me is figuring out to code this for multi line entry. For example, I want to be able to paste in a list of numbers to an unbound text box on a form like this

21471959
21471960
21471961
21471962
21471963
21471964
21471965

...and retrieve the results. The number of values in the list is not static, so the code needs to be flexible enough to accomodate however many values the user inputs into the text box.

I'm not sure if I will need to use VBA... or, if SQL will work.

The current SQL query I have is based on

table = dbo_empdatacopy
Field = lEmplDeptId
Form text box reference = [Forms]![Emp_Search]![Text3]


SELECT
dbo_empdatacopy.lEmplDeptId, dbo_empdatacopy.EMail

FROM
dbo_empdatacopy



WHERE
(((dbo_empdatacopy.lEmplDeptId) In ([Forms]![Emp_Search]![Text3])));

Can anybody provide a snippit of code to help me loop through every line that is populated? Thanks in adv if you can!

[weekend00]

SELECT
dbo_empdatacopy.lEmplDeptId, dbo_empdatacopy.EMail

FROM
dbo_empdatacopy
WHERE
[Forms]![Emp_Search]![Text3] like "*" & dbo_empdatacopy.lEmplDeptId & "*"

[spindlebeakin10]

Welp that worked! Thank you sooo much weekend00!

Cheers

[jzwp11]

Welcome to the forum!

If your user enters a list as you have shown, there will be a carriage return character (ASCII code 13) followed by a line feed character (ASCII code 10) between each successive number. I guess the question is will every user enter the data in the same way?

I would guess that you could use the Replace() function to replace the two ASCII characters with a comma followed by a space and then use that in a query you build in code.

The Replace function would look like this:

myTextString = Replace(Me.Textboxcontrolname, Chr(13) & Chr(10), ", ")

Then for the query something like this:

mySQL="SELECT....FROM... WHERE EmployeeID IN " & myTextString &

[weekend00]

I believe "where ... like ... " works fine for text with CRLFs.

[jzwp11]

weekend00,

It looks like we both came up with solutions that would work.