String concatenation problem

[hardrock]

Hi All,

I have a textbox called on my form called Prod. A user can enter a string of production orders separated by a comma int this text box for e.g he/she could enter

111222,111223,111224

From this i want to be able to create a string output in a second textbox in the format

(prod = '111222' OR prod = '111223' OR prod = '111224)

This allow me to build a SQL string. What would be the easiest way to do this in VBA?

Thanks

 

[spikepl]

Why do you require the user to type things that probably already exist in your DB? You introduce the possibility for typos, repeated enties, and other errors that have to be dealt with. Why not let user multiselect from a listbox? Or add one by one from a listbox or combobox? Or perhaps from some form with a Yes/No field that the user can click on for each item of interest?

There is a useful function caled Spilt: http://www.access-programmers.co.uk/forums/showthread.php?t=97750

 

[hardrock]

Hi there, sorry but that would not work for me. You see i am creating a live Pass through Query in SQL from code in my form , and the only condition that changes would be the range of production orders i put in my WHERE clause. so for e.g.

If i entered 111222 in my textbox the output would be be in format

WHERE PROD = '111222'

If i entered 111222,111223,111224 in my text box , the comma delimiter would insert OR PROD in the statement thus giving an output of

WHERE PROD = '111222 OR PROD = '111223' OR PROD = '111224'

Yes it is a method of concatenation, but not sure how to do it.
I hope someone can help. Thanks

 

[boblarson]

You can use a multiselect listbox to do this. But if you insist on this way just use:

Function GetWhere(strInput As String, strDelimiter As String) As String
Dim strWhere As String
Dim varSplit As Variant
Dim i As Integer
 
VarSplit = Split(strInput, strDelimiter)
 
For i = 0 To UBound(varSplit)
   strWhere = strWhere & Chr(39) & varSplit(i) & Chr(39) & ", "
Next
 
If strWhere <> vbNullString Then
   strWhere = "WHERE [Prod] In(" & Left(strWhere, Len(strWhere) -2) & ")"
End If
 
GetWhere = strWhere
End Function

 

[hardrock]

Hi bob, thanks for this. Please advise how I call/ use this using a textbox as my input.

 

[boblarson]

So in your query's SQL that you are building. Instead of including a where you could just use

strSQL = "Select Field1, Field2, Field3 From TableName " & GetWhere(Me.TextBoxnameHere, ",") & " Order By Field2, Field3"

for example

 

[hardrock]

Gotcha... thank-you. will try out tomorrow