Help with VBA with an unbound form

T

Ted

I am pretty much a vb noob and found some code to run an unbound form for a
clients database which I have altered:

I have a search section in the header of the form with a few unbound fields
, UnboundDateOpenedStart , UnboundDateOpenedEnd & UnboundFileNo all across
the top.

what i want to know is how can I create another row of criteria I mean
where exactly and how do i specify the OR condition in the following code,
assuming the 2nd set of unbound fields would be UnboundDateOpenedStart2,
UnboundDateOpenedEnd2 & UnboundFileNo2 :

Thanks for any help ahead of time :)


Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.UnboundDateOpenedStart) Then
strWhere = strWhere & "([OpenDate] >= " &
Format(Me.UnboundDateOpenedStart, conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundDateOpenedEnd) Then
strWhere = strWhere & "([OpenDate] <= " &
Format(Me.UnboundDateOpenedEnd, conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundFileNo) Then
strWhere = strWhere & "([FileNum] like """ & Me.UnboundFileNo & """)
AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
 
S

Steve Sanford

Hi Ted,

Try this:
(--watch for line wrap--)
'---------------------------
Private Sub cmdFilter_Click()
On Error GoTo Err_HandleError

Dim strWhere As String

Dim strCriteria1 As String
Dim lngLen1 As Long

Dim strCriteria2 As String
Dim lngLen2 As Long

Const conJetDate = "\#mm\/dd\/yyyy\#"

'---------1st Criteria----------
If Not IsNull(Me.UnboundDateOpenedStart) Then
strCriteria1 = strCriteria1 & "([OpenDate] >= "
strCriteria1 = strCriteria1 & Format(Me.UnboundDateOpenedStart,
conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundDateOpenedEnd) Then
strCriteria1 = strCriteria1 & "([OpenDate] <= "
strCriteria1 = strCriteria1 & Format(Me.UnboundDateOpenedEnd,
conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundFileNo) Then
strCriteria1 = strCriteria1 & "([FileNum] like """ & Me.UnboundFileNo
& """) AND "
End If

lngLen1 = Len(Trim(strCriteria1)) - 5
If lngLen1 > 0 Then
strCriteria1 = Left(strCriteria1, lngLen) 'remove the " AND " at the
end.
End If
'---------------------------------

'---------2nd Criteria---------
If Not IsNull(Me.UnboundDateOpenedStart2) Then
strCriteria2 = strCriteria2 & "([OpenDate] >= "
strCriteria2 = strCriteria2 & Format(Me.UnboundDateOpenedStart2,
conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundDateOpenedEnd) Then
strCriteria2 = strCriteria2 & "([OpenDate] <= "
strCriteria2 = strCriteria2 & Format(Me.UnboundDateOpenedEnd2,
conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundFileNo) Then
strCriteria2 = strCriteria2 & "([FileNum] like """ & Me.UnboundFileNo2
& """) AND "
End If

lngLen2 = Len(Trim(strCriteria2)) - 5
If lngLen2 > 0 Then
strCriteria2 = Left(strCriteria2, lngLen) 'remove the " AND " at the
end.
End If
'---------------------------------


If lngLen1 < 1 And lngLen2 < 1 Then 'Nah: there was nothing in the
string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
'Yep: there is something there

If lngLen1 > 0 And lngLen2 > 0 Then 'both
strWhere = "(" & strCriteria1 & ") OR (" & strWhere2 & ")"
ElseIf lngLen1 > 0 Then
strWhere = strCriteria1
ElseIf lngLen2 > 0 Then
strWhere = strCriteria2
Else
MsgBox "Something is wrong!!!"
End If

'set filter
Me.Filter = strWhere
Me.FilterOn = True
End If


Exit_HandleError:
Exit Function

Err_HandleError:
MsgBox Err.Description, vbExclamation, "Search Error " & Err.Number
Resume Exit_HandleError

End Sub
'---------------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Ted said:
I am pretty much a vb noob and found some code to run an unbound form for a
clients database which I have altered:

I have a search section in the header of the form with a few unbound fields
, UnboundDateOpenedStart , UnboundDateOpenedEnd & UnboundFileNo all across
the top.

what i want to know is how can I create another row of criteria I mean
where exactly and how do i specify the OR condition in the following code,
assuming the 2nd set of unbound fields would be UnboundDateOpenedStart2,
UnboundDateOpenedEnd2 & UnboundFileNo2 :

Thanks for any help ahead of time :)


Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.UnboundDateOpenedStart) Then
strWhere = strWhere & "([OpenDate] >= " &
Format(Me.UnboundDateOpenedStart, conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundDateOpenedEnd) Then
strWhere = strWhere & "([OpenDate] <= " &
Format(Me.UnboundDateOpenedEnd, conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundFileNo) Then
strWhere = strWhere & "([FileNum] like """ & Me.UnboundFileNo & """)
AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...
 
T

Ted

Wow Steve thank you for your help, one quick question the variable strWhere2
in this section is it supposed to be there or is that a typo?:

If lngLen1 < 1 And lngLen2 < 1 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
'Yep: there is something there

If lngLen1 > 0 And lngLen2 > 0 Then 'both
strWhere = "(" & strCriteria1 & ") OR (" & strWhere2 & ")"
ElseIf lngLen1 > 0 Then
strWhere = strCriteria1
ElseIf lngLen2 > 0 Then
strWhere = strCriteria2
Else
MsgBox "Something is wrong!!!"
End If


Steve Sanford said:
Hi Ted,

Try this:
(--watch for line wrap--)
'---------------------------
Private Sub cmdFilter_Click()
On Error GoTo Err_HandleError

Dim strWhere As String

Dim strCriteria1 As String
Dim lngLen1 As Long

Dim strCriteria2 As String
Dim lngLen2 As Long

Const conJetDate = "\#mm\/dd\/yyyy\#"

'---------1st Criteria----------
If Not IsNull(Me.UnboundDateOpenedStart) Then
strCriteria1 = strCriteria1 & "([OpenDate] >= "
strCriteria1 = strCriteria1 & Format(Me.UnboundDateOpenedStart,
conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundDateOpenedEnd) Then
strCriteria1 = strCriteria1 & "([OpenDate] <= "
strCriteria1 = strCriteria1 & Format(Me.UnboundDateOpenedEnd,
conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundFileNo) Then
strCriteria1 = strCriteria1 & "([FileNum] like """ & Me.UnboundFileNo
& """) AND "
End If

lngLen1 = Len(Trim(strCriteria1)) - 5
If lngLen1 > 0 Then
strCriteria1 = Left(strCriteria1, lngLen) 'remove the " AND " at the
end.
End If
'---------------------------------

'---------2nd Criteria---------
If Not IsNull(Me.UnboundDateOpenedStart2) Then
strCriteria2 = strCriteria2 & "([OpenDate] >= "
strCriteria2 = strCriteria2 & Format(Me.UnboundDateOpenedStart2,
conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundDateOpenedEnd) Then
strCriteria2 = strCriteria2 & "([OpenDate] <= "
strCriteria2 = strCriteria2 & Format(Me.UnboundDateOpenedEnd2,
conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundFileNo) Then
strCriteria2 = strCriteria2 & "([FileNum] like """ & Me.UnboundFileNo2
& """) AND "
End If

lngLen2 = Len(Trim(strCriteria2)) - 5
If lngLen2 > 0 Then
strCriteria2 = Left(strCriteria2, lngLen) 'remove the " AND " at the
end.
End If
'---------------------------------


If lngLen1 < 1 And lngLen2 < 1 Then 'Nah: there was nothing in the
string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
'Yep: there is something there

If lngLen1 > 0 And lngLen2 > 0 Then 'both
strWhere = "(" & strCriteria1 & ") OR (" & strWhere2 & ")"
ElseIf lngLen1 > 0 Then
strWhere = strCriteria1
ElseIf lngLen2 > 0 Then
strWhere = strCriteria2
Else
MsgBox "Something is wrong!!!"
End If

'set filter
Me.Filter = strWhere
Me.FilterOn = True
End If


Exit_HandleError:
Exit Function

Err_HandleError:
MsgBox Err.Description, vbExclamation, "Search Error " & Err.Number
Resume Exit_HandleError

End Sub
'---------------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Ted said:
I am pretty much a vb noob and found some code to run an unbound form for a
clients database which I have altered:

I have a search section in the header of the form with a few unbound fields
, UnboundDateOpenedStart , UnboundDateOpenedEnd & UnboundFileNo all across
the top.

what i want to know is how can I create another row of criteria I mean
where exactly and how do i specify the OR condition in the following code,
assuming the 2nd set of unbound fields would be UnboundDateOpenedStart2,
UnboundDateOpenedEnd2 & UnboundFileNo2 :

Thanks for any help ahead of time :)


Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.UnboundDateOpenedStart) Then
strWhere = strWhere & "([OpenDate] >= " &
Format(Me.UnboundDateOpenedStart, conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundDateOpenedEnd) Then
strWhere = strWhere & "([OpenDate] <= " &
Format(Me.UnboundDateOpenedEnd, conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundFileNo) Then
strWhere = strWhere & "([FileNum] like """ & Me.UnboundFileNo & """)
AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...
Click to expand...
 
S

Steve Sanford

I hate it when that happens...... :(

It should have been "strCriteria2". I started out duplicating what you had,
but it got confusing with so many "strWheres", so I changed to "criteria" and
missed one of the where's.

"strWhere2" should be "strCriteria2"

'-----
If lngLen1 < 1 And lngLen2 < 1 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
'Yep: there is something there

If lngLen1 > 0 And lngLen2 > 0 Then 'both
strWhere = "(" & strCriteria1 & ") OR (" & strCriteria2 & ")"
ElseIf lngLen1 > 0 Then
strWhere = strCriteria1
ElseIf lngLen2 > 0 Then
strWhere = strCriteria2
Else
MsgBox "Something is wrong!!!"
End If
'-----


HTH
 
T

Ted

Whew nevermind i got it to work, thanks so much for your help again. I had
like 30 criterias in for each line so it took me a while to get it working.
Thanks so much Steve

-Ted

Steve Sanford said:
Hi Ted,

Try this:
(--watch for line wrap--)
'---------------------------
Private Sub cmdFilter_Click()
On Error GoTo Err_HandleError

Dim strWhere As String

Dim strCriteria1 As String
Dim lngLen1 As Long

Dim strCriteria2 As String
Dim lngLen2 As Long

Const conJetDate = "\#mm\/dd\/yyyy\#"

'---------1st Criteria----------
If Not IsNull(Me.UnboundDateOpenedStart) Then
strCriteria1 = strCriteria1 & "([OpenDate] >= "
strCriteria1 = strCriteria1 & Format(Me.UnboundDateOpenedStart,
conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundDateOpenedEnd) Then
strCriteria1 = strCriteria1 & "([OpenDate] <= "
strCriteria1 = strCriteria1 & Format(Me.UnboundDateOpenedEnd,
conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundFileNo) Then
strCriteria1 = strCriteria1 & "([FileNum] like """ & Me.UnboundFileNo
& """) AND "
End If

lngLen1 = Len(Trim(strCriteria1)) - 5
If lngLen1 > 0 Then
strCriteria1 = Left(strCriteria1, lngLen) 'remove the " AND " at the
end.
End If
'---------------------------------

'---------2nd Criteria---------
If Not IsNull(Me.UnboundDateOpenedStart2) Then
strCriteria2 = strCriteria2 & "([OpenDate] >= "
strCriteria2 = strCriteria2 & Format(Me.UnboundDateOpenedStart2,
conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundDateOpenedEnd) Then
strCriteria2 = strCriteria2 & "([OpenDate] <= "
strCriteria2 = strCriteria2 & Format(Me.UnboundDateOpenedEnd2,
conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundFileNo) Then
strCriteria2 = strCriteria2 & "([FileNum] like """ & Me.UnboundFileNo2
& """) AND "
End If

lngLen2 = Len(Trim(strCriteria2)) - 5
If lngLen2 > 0 Then
strCriteria2 = Left(strCriteria2, lngLen) 'remove the " AND " at the
end.
End If
'---------------------------------


If lngLen1 < 1 And lngLen2 < 1 Then 'Nah: there was nothing in the
string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
'Yep: there is something there

If lngLen1 > 0 And lngLen2 > 0 Then 'both
strWhere = "(" & strCriteria1 & ") OR (" & strWhere2 & ")"
ElseIf lngLen1 > 0 Then
strWhere = strCriteria1
ElseIf lngLen2 > 0 Then
strWhere = strCriteria2
Else
MsgBox "Something is wrong!!!"
End If

'set filter
Me.Filter = strWhere
Me.FilterOn = True
End If


Exit_HandleError:
Exit Function

Err_HandleError:
MsgBox Err.Description, vbExclamation, "Search Error " & Err.Number
Resume Exit_HandleError

End Sub
'---------------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Ted said:
I am pretty much a vb noob and found some code to run an unbound form for a
clients database which I have altered:

I have a search section in the header of the form with a few unbound fields
, UnboundDateOpenedStart , UnboundDateOpenedEnd & UnboundFileNo all across
the top.

what i want to know is how can I create another row of criteria I mean
where exactly and how do i specify the OR condition in the following code,
assuming the 2nd set of unbound fields would be UnboundDateOpenedStart2,
UnboundDateOpenedEnd2 & UnboundFileNo2 :

Thanks for any help ahead of time :)


Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.UnboundDateOpenedStart) Then
strWhere = strWhere & "([OpenDate] >= " &
Format(Me.UnboundDateOpenedStart, conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundDateOpenedEnd) Then
strWhere = strWhere & "([OpenDate] <= " &
Format(Me.UnboundDateOpenedEnd, conJetDate) & ") AND "
End If

If Not IsNull(Me.UnboundFileNo) Then
strWhere = strWhere & "([FileNum] like """ & Me.UnboundFileNo & """)
AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search Form with a SubForm 3
Allen Browne's Search Form - Debugging help needed 2
search form problem 0
Allen Brownes ECount() 5
Require a field in search form 3
Send Records from Form, Method or Data Error not Found" 0
Create Report On the Fly 1
Multiple Search Criteria with AND/OR??? 4

Top