Allen Browne's Search Form - Debugging help needed

[RebeccaMinAR]

Your help is appreciated. I inhereted a mess of a program, and am hoping to
use the wonderful Allen Browne Search form to dig the data out.

I've got this coded, and it's not working! I keep getting an error related
to the last statements - on the me.filter line, near the end. I know it's
just a simple thing, but it's making me nuts!

Can someone take a look and see where I screwed up? Thanks!
-----------------------------

Private Sub Command21_Click()



Dim strWhere As String

Dim lngLen As Long

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



If Not IsNull(Me.RegSiteNameSearch) Then

strWhere = strWhere & "([RegSiteName] Like ""*" &
Me.RegSiteNameSearch & "*"") AND "

End If



If Not IsNull(Me.RegInitialSubDateFIRST) Then

strWhere = strWhere & "([RegInitialSubDate] >= " &
Format(Me.RegInitialSubDateFIRST, conJetDate) & ") AND "

End If



If Not IsNull(Me.RegInitialSubDateLAST) Then

strWhere = strWhere & "([RegInitialSubDate] <= " &
Format(Me.RegInitialSubDateLAST, conJetDate) & ") AND "

End If



lngLen = Len(strWhere) - 2

If lngLen <= 0 Then

MsgBox "No criteria", vbInformation, "Nothing to do."

Else

Me.Filter = "(strWhere, lngLen)"

Me.FilterOn = True

End If

End Sub

 

[Allen Browne]

Suggestions:

1. To remove the trailing " AND " (3 characters and 2 spaces), you need to
chop off 5 characters, not 2, so:
lngLen = Len(strWhere) - 5

2. You didn't actually chop the characters off:
strWhere = Left(strWhere, lngLen)

3. You assigned some literal characters to the form's filter instead of the
contents of strWhere:
Me.Filter = strWhere

If you're still stuck, check out what's wrong with the contents of your
string by adding this line just before the one where you assign Me.Filter:
Debug.Print strWhere
Then when it fails, open the Immediate Window (Ctrl+G), and examine what
came out.

 

[RebeccaMinAR]

Perfect, Allen - I knew it was something silly. Thanks!

Now if I could just get the combo box search figured out - I'm sort of a
cookbook coder, and I only half understand what I should be doing with that.

Suggestions:

1. To remove the trailing " AND " (3 characters and 2 spaces), you need to
chop off 5 characters, not 2, so:
lngLen = Len(strWhere) - 5

2. You didn't actually chop the characters off:
strWhere = Left(strWhere, lngLen)

3. You assigned some literal characters to the form's filter instead of the
contents of strWhere:
Me.Filter = strWhere

If you're still stuck, check out what's wrong with the contents of your
string by adding this line just before the one where you assign Me.Filter:
Debug.Print strWhere
Then when it fails, open the Immediate Window (Ctrl+G), and examine what
came out.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Your help is appreciated. I inhereted a mess of a program, and am hoping
to
use the wonderful Allen Browne Search form to dig the data out.

I've got this coded, and it's not working! I keep getting an error related
to the last statements - on the me.filter line, near the end. I know it's
just a simple thing, but it's making me nuts!

Can someone take a look and see where I screwed up? Thanks!
-----------------------------

Private Sub Command21_Click()



Dim strWhere As String

Dim lngLen As Long

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



If Not IsNull(Me.RegSiteNameSearch) Then

strWhere = strWhere & "([RegSiteName] Like ""*" &
Me.RegSiteNameSearch & "*"") AND "

End If



If Not IsNull(Me.RegInitialSubDateFIRST) Then

strWhere = strWhere & "([RegInitialSubDate] >= " &
Format(Me.RegInitialSubDateFIRST, conJetDate) & ") AND "

End If



If Not IsNull(Me.RegInitialSubDateLAST) Then

strWhere = strWhere & "([RegInitialSubDate] <= " &
Format(Me.RegInitialSubDateLAST, conJetDate) & ") AND "

End If



lngLen = Len(strWhere) - 2

If lngLen <= 0 Then

MsgBox "No criteria", vbInformation, "Nothing to do."

Else

Me.Filter = "(strWhere, lngLen)"

Me.FilterOn = True

End If

End Sub