Problem with Between 2 dates VBA (1 Viewer)

[NealeBabb]

Hi,
Im trying to use VBA to create a filter by date range. the user inputs 2 dates and the database filters all records by dates in between the 2 dates,

Code below

Dim var_CustDate1 As String
Dim var_CustDate2 As String
var_CustDate1 = InputBox("Please enter start date in format DD/MM/YYYY", "Enter Date", Date)
If Not IsDate(var_CustDate1) Then
MsgBox ("not a valid Date")
Exit Sub
End If
var_CustDate2 = InputBox("Please enter end date in format DD/MM/YYYY", "Enter Date", Date)
If Not IsDate(var_CustDate2) Then
MsgBox ("not a valid Date")
Exit Sub
End If
Me.Filter = "Format((
[List All Calls].[Opened Date]),'mm/dd/yyyy') Between (Format(#" & var_CustDate1 & "#, 'dd/mm/yyyy'))  AND  (Format(#" & var_CustDate2 & "#, 'dd/mm/yyyy'))  "

Me.FilterOn = True

Ive tried every combination of format for the dates but this is the closest ive got it to work,
if i enter dates 01/09/2013 and 12/09/2013 the filter works for the days in the month but also displays previous years, but if i change the dates to 01/09/2013 and 13/09/2013 it starts displaying all dates for all years in the months September, October, November and December

i know it is is something to do with the formats of the dates but Ive been looking at it that long now Ive gone blind to the simplest of mistakes

Any help would be greatly appreciated

 

[pr2-eugin]

Hello NealeBabb, What is the Data Type of the [Open Date] field? If it is date, you do not need to change the Format for the filter. This code should suffice..

Me.Filter = "
[List All Calls].[Opened Date] BETWEEN #" & Format(var_CustDate1, "mm\/dd\/yyyy") & _
            "# AND #" & Format(var_CustDate2, "mm\/dd\/yyyy") & "#"

Also your Code had the Format function coded up a bit bizarre. The above should work.

 

[NealeBabb]

Woo, it works =D Go you !!!

I knew it would be something Simple
Thanks for your help

 

[pr2-eugin]

You are welcome.