Parsing Multiple Values from a Textbox

[kumark]

Hi All,

I have a form in which the user has the privilege to download some data from the table.

the user will paste some of the numbers to the textbox from the excel in order to download the specific data.

i need to write a query to parse the textbox using the in condition

I am attaching an image for your reference

Regards
Kumar

 

[namliam]

You cant write a query based on that kind of information (directly).
You will have to create some VBA to "Create" your SQL for you.

Pseudo code

mySQL = "Select ... from ... where ... yourfield in (" & Yourtextbox & ")"
currentdb.querydefs("YourQuery").sql = mysql 'Write the sql into a query object.
export yourquery

 

[BlueIshDan]

	Dim sql As String: sql = "SELECT [fields] FROM [TABLE] WHERE "
	
	For Each var_split In Split(txtBox.Value, vbNewLine)

		sql = sql & " [field] = Split(var_split , ",")(0) & " OR " 
	Next

	sql = Left(sql, Len(sql) - 4)

 

[namliam]

which is the same as using the IN statement I suggested except it is a lot harder

Not to mention your code has a syntax error in it, though its probably aircode, so yeah not a biggy

 

[BlueIshDan]

which is the same as using the IN statement I suggested except it is a lot harder

Not to mention your code has a syntax error in it, though its probably aircode, so yeah not a biggy

yeah I just wrote it in the quick reply box lol.

 

[BlueIshDan]

You are able to pass a string into the IN of a query? :O That's awesome lol

 

[pr2-eugin]

namliam & BlueIshDan boys come on !

kumark, idhar dekho, I would go for the editing the QueryDef and exporting option. It is lot less messy and simple. If you are having troubles post back !

 

[BlueIshDan]

namliam & BlueIshDan boys come on !

kumark, idhar dekho, I would go for the editing the QueryDef and exporting option. It is lot less messy and simple. If you are having troubles post back !

Could you elaborate on this for me? For my education

 

[namliam]

You are able to pass a string into the IN of a query? :O That's awesome lol

No you cant you have to "splice it in" like my pseudo code does, which is simular to your OR way of doing it... except you dont need the loop and splitting and all that.
Just two different ways of doing the same really.

 

[pr2-eugin]

It is the method namliam showed in Post#2.

For this to work, you will have a Query (precomiled/saved with a name) under the name (for example) MyQuery. Then on the click of the button, you have to create a runtime query, like.

Dim mySQL As String

mySQL = "SELECT theFieldName, weAreInterested " & _
        "FROM theTable " & _
        "WHERE theIDColumn IN (" & Me.yourBigTextBoxName & ")"

Then you assign/edit the SQL of the precomipled query, with this mySQL.

CurrentDB.QueryDefs("MyQuery").SQL = mySQL

Then you simply export the Query, probably using the DoCmd.TransferSpreadsheet or DoCmd.OutpuTo method. Simples.

PS: If the user is using Enter (vbNewLine/vbCrLf) as the separator, then use the Replace function.

mySQL = "SELECT theFieldName, weAreInterested " & _
        "FROM theTable " & _
        "WHERE theIDColumn IN (" & Replace(Me.yourBigTextBoxName, vbCrLf, ",") & ")"

 

[BlueIshDan]

aww had be excited lol
my lazy senses were tingling!

 

[kumark]

Hi Guys,

Thanks for all your Replies..

I got the solution

db.Execute " select * into New_Tbl from Tbl_Nme where invid in
(" & Text63 & ")"

I changed the property of the textbox enter Key Behavior from default to new line entry.

Manually i had ended each line with comma ..

Thanks for the Suggestions and Solutions.

Thank u

 

[namliam]

select * into New_tbl

This is a very bad option to use it will cause your database to needlessly bloat not to mention you have to clean up the table and all that stuff.... Using a query ONLY is much neater.