INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Date code adds a day but why???

Date code adds a day but why???

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

CODE

'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 line that translates wrong     

    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 & "#)"      original filter   

  Me.Filter = "((EffectiveDate  Or RetireDate BETWEEN #" & strStart & "# And #" & strEnd & "#))  "  Filter I just tried 

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

RE: Date code adds a day but why???

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

CODE -->

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.

RE: Date code adds a day but why???

(OP)
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

RE: Date code adds a day but why???

From your code:

strEnd = DateAdd("d", 1, dteEnd)

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.

RE: Date code adds a day but why???

(OP)
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

RE: Date code adds a day but why???

CODE -->

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?

RE: Date code adds a day but why???

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:

CODE

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 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(strEnd) Then dteEnd = CDate(strTemp)
    
    'strStart = DateAdd("d", -1, dteStart)
    'strEnd = DateAdd("d", 1, dteEnd)
    'Debug.Print strStart & " to " & strEnd
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.

RE: Date code adds a day but why???

Quote:

I changed the 1 to 0 and it works like a champ
huh? why would you do that?

You wouldn't write in code

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?

Quote:

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

RE: Date code adds a day but why???

(OP)
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

RE: Date code adds a day but why???

(OP)
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

CODE

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

RE: Date code adds a day but why???

smile You are going to be mad at yourself...

strFilter = "EffectiveDate BETWEEN #" & dteStart & "# AND #" & dteEnd & "#"
strFilter = strFilter & " OR "
strFilter = strFilter & " RetireDate BETWEEN #" & dteStart & "# AND #" & dteEnd & "#"

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.

RE: Date code adds a day but why???

(OP)
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

RE: Date code adds a day but why???

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.

RE: Date code adds a day but why???

(OP)
Thanks I will

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close