Me.Filter not working 1

[Lhuffst]

Hi. I have a report that prompts for beginning and ending dates. These dates are then used in the me.filter on the report on open

The issue I'm having is with a compound evaluation. When it processes the first set of criteria, it seems to skip the 2nd half.
I need to return all records that either Start or End within the prompted dates.

Dim dteStart As Date
Dim dteEnd As Date
Dim strStart As String
Dim strEnd As String

   'default dates - find out what is common
    dteStart = Format("1/1/" & DatePart("yyyy", Date), "mm/dd/yyyy")
    dteEnd = Format("12/31/" & DatePart("yyyy", Date), "mm/dd/yyyy")

    ' get the date range they want to use
    strStart = InputBox("Enter Start Date Range:", "Start Date", dteStart)
    If IsDate(strStart) Then dteStart = strStart

    strEnd = InputBox("Enter End Date Range:", "End Date", Format(dteEnd, "mm/dd/yyyy"))
    If IsDate(strEnd) Then dteEnd = strEnd

    strStart = DateAdd("d", -1, dteStart)
    strEnd = DateAdd("d", 1, dteEnd)

    lblReportDates.Caption = Format(dteStart, "mm/dd/yyyy") & " - " & Format(dteEnd, "mm/dd/yyyy")

[b]'Original filter that works[/b]
' Me.Filter = "([EffectiveDate] >= #" & strStart & "# and [EffectiveDate] <= #" & strEnd & "# )"[/color]   

[b]'New Filter but seems to skip the retire date if there are records that were also started in that time frame[/b]
'Me.Filter = "(([EffectiveDate] >= #" & strStart & "# and [EffectiveDate] <= #" & strEnd & "# ) or ([retireDate] >= #" & strStart & "# and [retireDate] <= #" & strEnd & "#))"

[b]'Tried to combine the filters differently[/b]   
 Me.Filter = ([EffectiveDate] Or [RetireDate] ) >= #" & strStart & "# and ([EffectiveDate] or [retireDate] ) <=  #" & strEnd & "# )"       Me.FilterOn = True

for example.
Company A Vehicle 1 Effective Date: 1/1/2014 Retire Date: 1/30/2014
Company A Vehicle 2 Effective Date: ]1/1/2013 Retire Date: 10/1/2013
company A Vehicle 3 Effective Date: 1/1/2013 Retire Date: 10/1/2014
Company A Vehicle 4 Effective Date: 1/1/2014 Retire Date: null

If your prompted dates are 1/1/2014 to 12/31/2014 then I should get
company A Vehicle 1 --- should be on report because it was effective in the date range OR Retired in the date range
company A Vehicle 2 --- should be skipped as it's out of range
company A Vehicle 3 --- should be on list because it retired in the date range
Company A Vehicle 4 --- shoudl be on list because it was effective in the date range and doesn't have a retire date

Can anyone help with the correct syntax for the filter? Thanks lhuffst

 

[dhookom]

I would never prompt a user with InputBox() since it isn't a good interface. It's not much different than a parameter query faq701-6763. I would use a form with two date text boxes so the user could select from a calendar.

Dim strWhere as String
strWhere = " ([EffectiveDate] BETWEEN #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#) OR " & _
   "(Nz([Retiredate],#" & Me.txtRetireDate & "#) BETWEEN #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#)"
debug.Print strWhere
DoCmd.OpenReport "[Your Report Name Here]", acViewPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Did you try something like this:

Dim strFilter As String

strFilter = "(EffectiveDate BETWEEN #" & strStart & "# And #" & strEnd & "#) " _
& " Or (retireDate BETWEEN #" & strStart & "# And #" & strEnd & "#)"

Debug.Print strFilter

Me.Filter = strFilter

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Lhuffst]

Duane - unfortunately, this is proprietary software and we are limited to where and how we can make changes.
Andy - That worked like a charm.
Thank you both

 

[dhookom]

Lhuffst,
Andy deserves a star for "That worked like a charm".

Duane
Hook'D on Access
MS Access MVP