Between 9 pm (today) and 4am (Tomorrow) VBA


I want to see if I can make sure that I run a certain code for between times: 2100 (today) and 0400 (Tomorrow)

I have this code:  but not sure if this is picking up the right time frame...

Dim dtTime As Date, dtTime2 As Date, dtTime3 As Date

dtTime = TimeValue(Now())
dtTime2 = #9:00:00 PM#
dtTime3 = #4:00:00 AM#

If dtTime > dtTime2 And dtTime < dtTime3 Then
     'my code executes here
End If

Open in new window

Ernest GroggSecurity Management InfoSecAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
What event are you trying to run this in?

Normally if you are going to do something like this, you would put it in a form timer event.  But a time value cannot be both > 9 PM and < 4AM,  it has to be one or the other, so your IF statement would have to look like:

If dtTime > dtTime2 OR dtTime < dtTime3 Then
     'my code executes here
End If

but if you are not careful, that code will run over and over again, so if you don't want it to go into a loop, that will only stop when it reaches 04:00:01 AM, then you need to add some other logic in there somewhere.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ernest GroggSecurity Management InfoSecAuthor Commented:
What I am doing is the Night Shift runs a report that generates numbers for the daily entries.  What happens is when they run this report, if the report is running between

9pm that day and 4am that morning (depends on what time they get to run the report), then it will generate an email to send out.  But if they run it again later on, I have safeguards to check for a timestamp first.  So it won't run in a loop but just exits out.  

So I am guessing that I actually need the OR statement instead?
Dale FyeOwner, Developing Solutions LLCCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.