CODEWITHSUNDEEP
sqlexcelms-access
Ian Fafard
Ian Fafard

Reputation: 51

Query Access from Excel: Variable Parameters

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?

Upvotes: 0

Views: 88

Answers (3)

Gustav
Gustav

Reputation: 55816

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#)

Upvotes: 1

Ian Fafard
Ian Fafard

Reputation: 51

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!

Upvotes: 1

user4039065
user4039065

Reputation:

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.

Upvotes: 2

Related Questions