Plz help. VBA CODE.

[Mr. Hero]

I am some what fimiliar with VBA. I adapted a code from Mr. Allen Browne's Search form. but When do a search from 10/01/2009 - 02/28/09, the form doesn't work. I checked all my quieries and they work fine. I was wondering if someone could take a look at this code and maybe offer some advise in how to get past this little problem.

Below is the code I am using,

Private Sub cmdFilter_Click()

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

'Date field example. Use the format string to add the # delimiters and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterIs) Then
strWhere = strWhere & "([Issue] = """ & Me.txtFilterIs & """) AND "
End If

'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.txtFilterLevel) Then
strWhere = strWhere & "(

 = """ & Me.txtFilterLevel & """) AND "[/COLOR]
[COLOR=navy]End If[/COLOR]
 
[COLOR=navy]'See if the string has more than 5 characters (a trailng " AND ") to remove.[/COLOR]
[COLOR=navy]lngLen = Len(strWhere) - 5[/COLOR]
[COLOR=navy]If lngLen <= 0 Then 'Nah: there was nothing in the string.[/COLOR]
[COLOR=navy]MsgBox "Can not search with blank spaces", vbInformation, "Nothing to do."[/COLOR]
[COLOR=navy]Else 'Yep: there is something there, so remove the " AND " at the end.[/COLOR]
[COLOR=navy]strWhere = Left$(strWhere, lngLen)[/COLOR]
[COLOR=navy]'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).[/COLOR]
[COLOR=navy]'Debug.Print strWhere[/COLOR]
 
[COLOR=navy]'Finally, apply the string as the form's Filter.[/COLOR]
[COLOR=navy]Me.Filter = strWhere[/COLOR]
[COLOR=navy]Me.FilterOn = True[/COLOR]
[COLOR=navy]End If[/COLOR]
[COLOR=navy]On Error Resume Next[/COLOR]
[COLOR=navy]CurrentDb.TableDefs.Delete "tblGraph"[/COLOR]
[COLOR=navy]Dim stdocname As String[/COLOR]
 
[COLOR=navy]DoCmd.SetWarnings False[/COLOR]
[COLOR=navy]DoCmd.OpenQuery "qrygraphfilter", acViewNormal, acReadOnly[/COLOR]
[COLOR=navy]stdocname = "frmChart"[/COLOR]
[COLOR=navy]DoCmd.OpenForm stdocname[/COLOR]
 
[COLOR=navy]End Sub[/COLOR]

 

[DCrake]

For a start don't name your field Date. This is an Access reserved word. also when constructing your sql it should read

"Where [Field] Between #" & Startdate & "# And #" & Enddate & "#"

 

[Mr. Hero]

DCrake,
I wanted to thank you for you time in helping me. I thought I had replied to your quote, apparently my post didn't go through. My issue was that I didn't use the correct data type (this always helps. ). But Thanks for the help.