0

I've been doing in depth, functional area dashboards in excel that are refreshed upon end-user command of the 'refresh all' button. The only problem I am running into is refreshing daily production when it is past midnight, thus turning access' back end query of 'date()' to, well, the current day at midnight.

This has been the condition I want to work properly: I want everything >= 5 AM for either today or previous day based on the NOW time.

WHERE start_time >=
   (iif(timevalue(now()) between #00:00# and #4:59#,date()-1,date()))
AND timevalue(start_time) >= #5:00#;

The thing is that it is returning in such an extremely slow rate.

I don't think I've ever waited for it to complete. I'm not sure if its calculating this logic on every record involved in the back end table or not, which would explain the lock up.

I really want to avoid building any logic dynamically as I am simply using Excel to call upon this Access query through the query wizard. I would hate to have to resort to an access button triggering a module to build the query dynamically and then focus the excel window and refresh.

It would be nice to create an object on, say, a [Form]! but that is only useful when the form is active.. even then the SQL rejects any sub-calculations within the object of the form.

Any thoughts?

Improve this question
4
  • Bah, I just realized it would still be ignoring midnight - 4:59 with the #5:00# last condition. The reason I started at #5:00# was to omit any bleedover data shortly after midnight... I don't think there is a way to NOT build this in VBA. I have no issue writing in VBA to accomplish what I need to do; Could the only option be to use the Excel VBA to build a dynamic query within access, save the query definition in access, and then call it through excel? Commented Oct 26, 2015 at 22:34
  • So when now() is #5:00# then you require that start_time >= now()? I suppose that will not give many results, right? I think you should first write down in maybe pseudo code, what you really want. Make the requirements unambiguously clear. Commented Oct 26, 2015 at 22:54
  • If im refreshing any time during the day between 5 am and 11:59 pm, just to return >=date() if its after midnight.. needs to be previous day after 5 am and everything for current day (which would be anything after midnight - 4:59) .. Gustav made a good point below, im just struggling, for some reason, with abnormally long query time Commented Oct 26, 2015 at 23:19
  • Does your table have an index on the start_date column? If not, that will improve the response time considerably, in most of the solutions provided here. Commented Oct 27, 2015 at 8:47

3 Answers 3

Reset to default
2

I believe parsing down to the mathematical equivalent of a boolean HOUR(Now)<5 should speed things up considerably. 

WHERE start_time >= (Date + (Hour(Now)<5) + TimeSerial(5, 0, 0))

A boolean True is considered -1.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

This seems to be working; I needed a ' ' to concat times correctly. Gustav brought up the 'between' and 'or'; this is working fine on my offline test db - I will mark this way down as a possible solution. I also added seconds in order to capture last minute data of 23:59:00 to 23:59:59

        WHERE 

iif(timevalue(now()) Between #00:00# And #4:59#,
(start_time 
 Between Date()-1&' '&#05:00# And Date()-1&' '&#23:59:59#)

  OR

(start_time Between date()&' '&#00:00# And date()&' '&#23.59:59#),

(start_time Between date()&' '&#00:00# And date()&' '&#23.59:59#));

I just now need to build into it the now() condition in an iif statement to decided which condition to exectute!

Improve this answer

2 Comments

This makes no sense. First, you should never, ever if at all possible not use string handling - including concatenating - for date/time operations. Second, TimeValue(Now()) should be replaced by Time(). Third, if you mean that the day criteria should be determined by the current time being before or after 05:00, your original critera is inaccurate and has to be rephrased.
I tested this in an offline database and it worked perfectly, so I don't understand how it cannot make sense. I will say that it may not be optimally written by any means. Thank you for the time() I never used it that way before; ive always used now() because I work mostly with applications using data input containing timestamps with dates.... This seems to work just fine:::: iif(time() between #00:00# and #4:59#,<CONDITION 1>,<CONDITION 2>)
1

You can use:

WHERE start_time 
    (Between Date() - 1 + #05:00:00# And Date() - 1 + #23:59:59#) 
    Or 
    (Between Date() + #05:00:00# And Date() + #23:59:59#)
Improve this answer

2 Comments

That's close, I just need to attach the now() condition to determine how it will return. Remember, 5 am - 11:59 now() times need to return everything after 5 am, now times of midnight - 4:59 need to return everything after previous day 5AM I could be overthinking it because I know this is something I could rapidly develop dynamically, but trying to stay 100% in SQL is tricky sometimes, and im on the edge of crossing that line with this particular function
It is not just close, it is to the point if your criteria is everything >= 5 AM for either today or previous day. See my latest edit, please.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.