Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date Filter not working 1

Status
Not open for further replies.

paulnnosh

MIS
Mar 25, 2004
32
Hi guys,

I am trying to filter a subform to list only those records that have an Action Date less than todays date. I am running the following code:

Private Sub butOverdue_Click()
Dim strFilter As String

strFilter = ""

strFilter = "[Action Date] < #" & Format$(Date, "dd/mm/yyyy") & "#"
Debug.Print strFilter

If Len(strFilter) Then

Me!subProspects.Form.Filter = strFilter
Me!subProspects.Form.FilterOn = True


End If

End Sub

When I run this the content of strFilter is:

[Action Date] < #11/06/2004#

but all the filter does is strip out all records with an empty date field.

Any anybody shed any light please?

Many Thanks
 
Try :-

strFilter = "[Action Date] < #" & Format$(Date, "dd/mmm/yyyy") & "#"


If that works - I'll explain why !




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.
 
Date() is in the format set by the International setting in your local machine

So I'm guessing Date is ALREADY in dd/mm/yyyy format for you. So your Format statement did nothing to help.

However JET totally ignores the international setting of the PC and will always try to interpted a date in the confusing colonial mm/dd/yy form if it can.

You therefore need to send dates to JET that cannot be misinterpreted. Therefore you always send dates to JET in dd/mmm/yyyy format because that sends the month as three LETTERs. JET can cope with that and always interprests the letters as month regardless of position.


Thus - it fixes your problem.


QED.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.
 
Oh I see.

Thanks for your help. You have saved me a LOT of headaches!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top