Numerous entries in one text box (97)

Topic

New Reply

Numerous entries in one text box
Hi I can

Reply | Quote

Replies

If instr([fieldname],”1″) > 0 then
do something here.

the instr() function will return the character position that a given string falls in, therefore if the value is greater than 0, the string exists. If you know that you will be evaluating this for the numbers 1-10 then you could run a loop like this:

Dim i as integer
Dim strCriteria as string

i = 0

for i = 0 to 10
if instr(field,i) = true then
select case len(strCriteria)
case is = 0
strCriteria = i
case else
strCriteria = strCriteria & “or ” & i
end select
end if

next i

If, in this example, the text had the numbers 1, 3, and 5 in it, then strCriteria would =
“1 or 3 or 5”

This way you could build the query as
strQuery = “Select * from Table where CriteriaField = ” & strCriteria

Hope this helps.

jd

Reply | Quote

Oops. I forgot, just before next i you need to put in i = i +1

Reply | Quote

At least two misteaks in your code (I’m ignoring the part at the end about setting up the query). First, instr returns an integer (the position of the second string in the first string), so you should be checking if the result is 0. Second, if [field] contains two digit numbers (e.g. 10), then your code won’t work – instr(“8,9,10″,”1”) will return non-zero (specifically, 5).

Reply | Quote

It is not clear to me what you would like to do.

If you want to extract each comma separated value you might have a look at: http://www.mvps.org/access/strings/str0003.htm

Reply | Quote

Good catch Douglas, I don’t know what planet I was on when I did that.

Arage, try this:

Public Sub ZatHere()
Dim i As Integer
Dim strCrit As String

For i = 1 To 20
If InStr(1, “2,3,10,5”, i & “,”) > 0 Then
Select Case Len(strCrit)
Case Is > 0
strCrit = strCrit & “, or ” & i
Case Else
strCrit = i
End Select
End If
Next i

MsgBox strCrit

End Sub

Reply | Quote

thanks guys but I fixed it with a suggestion I picked up elsewhere, basically it will append proper condition to the WHERE clause of the query.

Function WHERECLAUSE() As String

Dim frmMyForm As Form
Dim strMyWhereClause, strControl1, strControl2 As String
Dim bCriteriaExists As Boolean
Dim i As Integer

bCriteriaExists = False
strMyWhereClause = “”
Set frmMyForm = Forms.Item(“frmTest”)
strControl1 = frmMyForm.Controls(“cboType”)

If Len(Nz(strControl1, “”)) 0 Then
strMyWhereClause = “([NewQuery]![PromotionType] = ‘” & strControl1 & “‘) AND (”
For i = 0 To 4
strControl1 = Nz(frmMyForm.Controls(“txtEvent” & i), “”)
strControl2 = Nz(frmMyForm.Controls(“cboRegion” & i), “”)
If Not (Len(strControl1) = 0 Or Len(strControl2) = 0) Then
bCriteriaExists = True
strMyWhereClause = strMyWhereClause & ” OR ([NewQuery]![RegionalDirectorCode] = ” & strControl2 & _
” AND [NewQuery]![EventNumber] IN (” & strControl1 & “))”
End If
Next i

‘Trim the extra ” OR ” before the first Region/Events criteria and put a final “)”
If bCriteriaExists = True Then
strMyWhereClause = Left$(strMyWhereClause, InStr(1, strMyWhereClause, ” OR “) – 1) & _
Right$(strMyWhereClause, Len(strMyWhereClause) – InStr(1, strMyWhereClause, ” OR “) – 3) & “)”
Else:
strMyWhereClause = “”
End If

WHERECLAUSE = strMyWhereClause

End If

End Function

Reply | Quote