Multiple inputs for strwhere (1 Viewer)

[Wolf]

I have an unbound box on an unbound form that will enable me to put in a criteria for the report to open, the code on the click button is as follows:


If Len(Me.Box1 & vbNullString) > 0 Then
strWhere = strWhere & "([Name] = """ & Me.Box1 & """)
End If

I would like to know if its possible to make this box return multiple values, for eg. if user writes in John it will return only john, if user write lets say John,Joe, it will return both, dosnt have to be comma it can be also ; or " wtvr.

 

[pr2-eugin]

Well it involves a bit of coding..

Dim indCtr As Integer
Dim idStr() As String
Dim locVar As String
locVar = Me.Box1
If Len(locVar & vbNullString) > 0 Then
    If InStr(locVar, ",") <> 0 Then
        idStr = Split(locVar, ",")
        Dim orI As Integer
        For orI = 0 To UBound(idStr)
            If Nz(idStr(orI), "") <> "" Then
                If indCtr = 0 Then
                    strWhere = "([Name] In ('" & idStr(orI) & "',"
                Else
                    strWhere = strWhere & "'" & idStr(orI) & "',"
                End If
                indCtr = indCtr + 1
            End If
        Next
        strWhere = Left(strWhere,Len(strWhere)-1) & "))"
    Else
        strWhere = strWhere & "([Name] = '" & locVar & "')"
    End If
End If

This should work, let me know how you get along.. I have used a comma as a separator..

 

[Wolf]

Cannot Get it to work, Dont know why...

 

[pr2-eugin]

Oops.. Sorry it might be because, I did not concatenate the previously generated WHERE.. Try this..

Dim indCtr As Integer
Dim idStr() As String
Dim locVar As String
locVar = Me.Box1
If Len(locVar & vbNullString) > 0 Then
    If InStr(locVar, ",") <> 0 Then
        idStr = Split(locVar, ",")
        Dim orI As Integer
        For orI = 0 To UBound(idStr)
            If Nz(idStr(orI), "") <> "" Then
                If indCtr = 0 Then
                    strWhere = [B][COLOR=Blue]strWhere &[/COLOR][/B] "([Name] In ('" & idStr(orI) & "',"
                Else
                    strWhere = strWhere & "'" & idStr(orI) & "',"
                End If
                indCtr = indCtr + 1
            End If
        Next
        strWhere = Left(strWhere,Len(strWhere)-1) & "))"
    Else
        strWhere = strWhere & "([Name] = '" & locVar & "')"
    End If
End If

If that does not work.. see what

Debug.Print strWhere

just after the last End If gives you..

 

[Wolf]

that worked great, THANKS!!!


Now, I would like to use that on a different form too same idea, only difference is that on that form I have 8 different criteria, here is my code.

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Len(Me.Eql2 & vbNullString) > 0 Then
strWhere = strWhere & "([Transaction Date] = " & Format(Me.Eql2, conJetDate) & ") AND "
End If

If Len(Me.NtEql2 & vbNullString) > 0 Then
strWhere = strWhere & "([Transaction Date] <> " & Format(Me.NtEql2, conJetDate) & ") AND "
End If

If Len(Me.GrtrTn2 & vbNullString) > 0 Then
strWhere = strWhere & "([Transaction Date] >= " & Format(Me.GrtrTn2, conJetDate) & ") AND "
End If

If Len(Me.LsTn2 & vbNullString) > 0 Then
strWhere = strWhere & "([Transaction Date] <= " & Format(Me.LsTn2, conJetDate) & ") AND "
End If


If Len(Me.Text157 & vbNullString) > 0 Then
strWhere = strWhere & "([Memo] = """ & Me.Text157 & """) AND "
End If

If Len(Me.Text158 & vbNullString) > 0 Then
strWhere = strWhere & "([Memo] <> """ & Me.Text158 & """) AND "
End If

If Len(Me.Text159 & vbNullString) > 0 Then
strWhere = strWhere & "([Memo] >= """ & Me.Text159 & """) AND "
End If
If Len(Me.Text160 & vbNullString) > 0 Then
strWhere = strWhere & "([Memo] <= """ & Me.Text160 & """) AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "All Records are being generated", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Debug.Print strWhere


End If

Me.Visible = False

DoCmd.OpenReport "QryTrn", acViewReport, , strWhere



End Sub

 

[nanscombe]

You may end up with an extra AND at the end of the WHERE clause if you do it the way you show. You will probably end up with "Clause1 AND Clause2 AND Clause3 AND"

If Len(Me.Eql2 & vbNullString) > 0 Then
  strWhere = strWhere & "([Transaction Date] = " & Format(Me.Eql2, conJetDate) & ") AND "
End If

I tend to do it this way. It will only add the AND if strWhere already has something in it so it'll end up as "Clause1 AND Clause2 AND Clause3"

If Len(Me.Eql2 & vbNullString) > 0 Then
  If strWhere <> vbNullString then strWhere = strWhere & " AND "
  strWhere = strWhere & "([Transaction Date] = " & Format(Me.Eql2, conJetDate) & ")"
End If

Or even go as far as writing yourself a couple of small functions thus.

Dim strWhere as String

Private Function whereStart()
  strWhere = vbNullString
End Function

Private Function whereAdd(byval theValue as Variant, byval theClause as String)
' Test to see if a value has been entered
  If len(theValue & vbNullString) >0 then

    If strWhere <> vbNullString Then strWhere = strWhere & " AND "
    strWhere = strWhere & theClause

  Endif
End Function

Private Function whereGet()
  whereGet = vbNullString
  If strWhere <> vbNullString Then whereGet = " WHERE " & strWhere
End Function

Private Sub whereBuild()
  whereStart

  whereAdd Me.Eql2, "([Transaction Date] = " & Format(Me.Eql2, conJetDate) & ")"

  whereAdd Me.NtEql2, "([Transaction Date] <> " & Format(Me.NtEql2, conJetDate) & ")"
  whereAdd Me.GrtrTn2, "([Transaction Date] >= " & Format(Me.GrtrTn2, conJetDate) & ")"
  whereAdd Me.LsTn2, "([Transaction Date] <= " & Format(Me.LsTn2, conJetDate) & ")"

  whereAdd Me.Text157, "([Memo] = """ & Me.Text157 & """)"
  whereAdd Me.Text158, "([Memo] <> """ & Me.Text158 & """)"
  whereAdd Me.Text159, "([Memo] >= """ & Me.Text159 & """)"
  whereAdd Me.Text160, "([Memo] <= """ & Me.Text160 & """)"

  If strWhere = vbNullString Then
    MsgBox "All Records are being generated", vbInformation, "Nothing to do."
  Else 
    Debug.Print strWhere
  End If

  Me.Visible = False

  DoCmd.OpenReport "QryTrn", acViewReport, , strWhere
End Sub