Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Numerous entries in one text box (97)

    Numerous entries in one text box
    Hi I can

  2. #2
    Lounger
    Join Date
    Apr 2001
    Location
    KY USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numerous entries in one text box (97)

    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

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    KY USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numerous entries in one text box (97)

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

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numerous entries in one text box (97)

    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).

  5. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numerous entries in one text box (97)

    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

  6. #6
    Lounger
    Join Date
    Apr 2001
    Location
    KY USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numerous entries in one text box (97)

    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

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numerous entries in one text box (97)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •