Simple date filter string for VBA

[kyuball]

Hello,

After searching and searching, I have not been able to find the answer I need and I was hoping that someone would either lead me to the right thread or help a brother out...

I am on Access2003 and I have a continuous form with data culled from a table that lists services. It's a pretty simple table that has a PK, name of service, and a date of service. I put two unbound text boxes to filter the records for specific date ranges. So for the first text box, which would be the one that filters for start date, I wrote something like this:

Private Sub Text10_AfterUpdate()

Dim stStartdate as String
stStartdate = stStartdate & "([servicedate] >= " & Me.Text10 & ")"
Me.Filter = stStartdate
Me.FilterOn = True

End Sub

When I enter a date into Text10 (it has been formatted to Short date and returns a US based date system, which is correct) and press tab or enter, it turns the filter on but filters nothing.

Then I tried:

stStartdate = stStartdate & "([servicedate] >= "" & Me.Text10 & "")"

and got the ole Run-time error 2001

Then I tried:

stStartdate = stStartdate & "([servicedate] >= ""*" & Me.Text10 & "*"")"

and got he same error

What am I doing wrong?

 

[dkinley]

Dates are used with the "#" symbol. Try something like ...

stStartdate = "[servicedate] >= #" & Me.Text10 & "#"

Although I don't think you can use the ampersand to properly 'and' to logical concepts. You will need to use the AND operator with each element having their own criteria. Do a search on 'multiple criteria' to see samples.

HTH,
-dK

 

[kyuball]

You rock, dkinley! It worked like a charm!!!

For those dummies (like me) who may be using this thread in the future:

Private Sub Text10_AfterUpdate()

Dim stStartdate as String
stStartdate = stStartdate & "([servicedate] >= #" & Me.Text10 & "#)"
Me.Filter = stStartdate
Me.FilterOn = True

End Sub

I guess it is only fitting that the solution to a simple problem would be a simple answer....

Kyu

 

[dkinley]

Glad it worked first off and thanks for posting back your final solution.

Good luck!

-dK