Date code adds a day but why??? 2

[Lhuffst]

Hi I'm using the following code as a filter for a report.

'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"))
    [COLOR=#CC0000][b]If IsDate(strEnd) Then dteEnd = strEnd[/b] line that translates wrong[/color]     

    strStart = DateAdd("d", -1, dteStart)
    strEnd = DateAdd("d", 1, dteEnd)
 ' Me.Filter = [EffectiveDate] or [RetireDate] in
 '  Me.Filter = "((EffectiveDate BETWEEN #" & strStart & "# And #" & strEnd & "#)) AND IsNull(retireDate) " _
'& " Or (retireDate BETWEEN #" & strStart & "# And #" & strEnd & "#)"      [COLOR=#A40000]original filter [/color]  

  Me.Filter = "((EffectiveDate  Or RetireDate BETWEEN #" & strStart & "# And #" & strEnd & "#))  "  [COLOR=#729FCF]Filter I just tried[/color]

I'm running the report from 4/1/15 to 6/30/15
The problem I"m having is the effective date column is showing 7/1/15 (based on the date passed)
when I step through the code, the strEnd prompt has the correct date (6/30/15) but the very next line shows 7/1/15

I'm not sure why it's doing that. Any help would be appreciated.
thanks
lhuffst

 

[MajP]

Maybe I do not understand. But if you pass in 4/1/2015 and subtract a day you get 3/31/2015. if you pass in 6/30/2015 and add a day you get 7/1/2015. What is not working? I modified it a little

Public Sub TestDates()
   'default dates - find out what is common
    '4/1/15 to 6/30/15
    Dim dteStart As Date
    Dim dteEnd As Date
    Dim strStart As String
    Dim strEnd As String
    dteStart = DateSerial(Year(Date), 1, 1)
    dteEnd = DateSerial(Year(Date), 12, 31)

    ' 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)
    Debug.Print strStart & " to " & strEnd
End Sub

prints out 3/31/2015 to 7/1/2015 as expected.

 

[Lhuffst]

I was expecting to get 4/1/15 to 6/30/15. I can see why it comes up 3/31/15 but why does the end date come up 7/1/15 instead of 6/30/15? That's the part I don't understand

 

[Andrzejek]

From your code:

[tt]strEnd = DateAdd("d", 1, dteEnd)[/tt]

Don't you say here: Add one day to my dteEnd and display the result in strEnd?

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]

Yes I finally figured that out. Not sure if I should use something else instead but for today, I changed the 1 to 0 and it works like a champ. Thanks

 

[MajP]

I was expecting to get 4/1/15 to 6/30/15. I can see why it comes up 3/31/15 but why does the end date come up 7/1/15 instead of 6/30/15

I am so confused. You expected that if you add 1 day to 6/30/2015 you would get 6/30/2015. What kind of math is that?

 

[Andrzejek]

That’s why I never do any juggling of the data between different data types

Your code would look something like that in my app:

Public Sub TestDates()[green]
   'default dates - find out what is common
    '4/1/15 to 6/30/15[/green]
    Dim dteStart As Date
    Dim dteEnd As Date
    Dim [blue]strTemp[/blue] As String
    dteStart = DateSerial(Year(Date), 1, 1)
    dteEnd = DateSerial(Year(Date), 12, 31)
[green]
    ' get the date range they want to use[/green]
    [blue]strTemp[/blue] = InputBox("Enter Start Date Range:", "Start Date", dteStart)
    If IsDate([blue]strTemp[/blue]) Then dteStart = CDate([blue]strTemp[/blue])
    [blue]strTemp[/blue] = InputBox("Enter End Date Range:", "End Date", Format(dteEnd, "mm/dd/yyyy"))
    If IsDate(strEnd) Then dteEnd = CDate([blue]strTemp[/blue])
    [green]
    'strStart = DateAdd("d", -1, dteStart)
    'strEnd = DateAdd("d", 1, dteEnd)
    'Debug.Print strStart & " to " & strEnd[/green]
End Sub

I can accept the data from the Users into a string, but then I convert it into a Date. And I am done with that String. I can re-use it again, but never 'use it' to do anything else.

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.

 

[1DMF]

I changed the 1 to 0 and it works like a champ

huh? why would you do that?

You wouldn't write in code

 1 + 0 + 0 + 0 + 0 + 0

So why are you performing a DateAdd and telling it to not add anything?

Take this date; add zero days to it and give me it back?

I am so confused.

Me too MajP!



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[Lhuffst]

Thanks everyone. I am going to switch the code but I had to get the report out right then. Since it was subtracting and adding a day, I swapped the 1 and -1 with 0 so it wouldn't really do anything but just for that day). Now that I'm back on site, I'm going to replace it with the better code above.
thanks again

 

[Lhuffst]

One last thing. I changed the code to what Andrzejek suggested but my filter isn't filtering out the records.
Syntax wise this is correct and the values for dteStart and dteEnd are correct; 4/1/15 and 6/29/15 (respectively).
Why wouldn't the filter work? I'm getting all the records back (56 pages worth) instead of the 20ish I expected.


Me.Filter = "[EffectiveDate] or [RetireDate] BETWEEN #" & dteStart & "# AND #" & dteEnd & "#"
this is my code now

Dim dteStart As Date
Dim dteEnd As Date
Dim strStart As String
Dim strEnd As String
'Dim dteStart As Date
'Dim dteEnd As Date
Dim strTemp As String
dteStart = DateSerial(Year(Date), 1, 1)
dteEnd = DateSerial(Year(Date), 12, 31)

    ' get the date range they want to use
    strTemp = InputBox("Enter Start Date Range:", "Start Date", dteStart)
    If IsDate(strTemp) Then dteStart = CDate(strTemp)
    strTemp = InputBox("Enter End Date Range:", "End Date", Format(dteEnd, "mm/dd/yyyy"))
    If IsDate(strTemp) Then dteEnd = CDate(strTemp)
    

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

     Me.Filter = "[EffectiveDate] or [RetireDate] BETWEEN #" & dteStart & "# AND #" & dteEnd & "#"
      Me.FilterOn = True

Thanks again
lhuffst

 

[Andrzejek]

You are going to be mad at yourself...
[tt]
strFilter = "EffectiveDate BETWEEN #" & dteStart & "# AND #" & dteEnd & "#"
strFilter = strFilter & " OR "
strFilter = strFilter & " RetireDate BETWEEN #" & dteStart & "# AND #" & dteEnd & "#"

Me.Filter = strFilter[/tt]


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]

I ended up having to change my filter to
strFilter = "[effectiveDate] <= #" & dteEnd & "# and nz(retiredate,#12/31/2050#)) >= #" & dteStart & "# "
strFilter = strFilter & " OR"
strFilter = "nz(retiredate,#12/31/2050#) >= #" & dteStart & "# and [effectiveDate] <= #" & dteEnd & "#"

Thank you for all the help

 

[Andrzejek]

So you ended up with your filter something like:

Condition AND Condition OR Condition AND Condition

To be on the safe side, I would add some ( ):

(Condition AND Condition) OR (Condition AND Condition)

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]

Thanks I will