Excel SQL results not updating when data changed for current day

[ko1967]

Background:

I created a SQL query as follows.

Exact Query SELECT `CLog$`.CID, `CLog$`.OpenDt, `CLog$`.CloseDt, `CLog$`.AID, `CLog$`.Trader, `CLog$`.Strategy, `CLog$`.Security, `CLog$`.PnL, `CLog$`.DaysHeld, `CLog$`.NotesFROM `CLog$` `CLog$`
WHERE (`CLog$`.OpenDt>=? And `CLog$`.OpenDt<=?) OR (`CLog$`.CloseDt>=? And `CLog$`.CloseDt<=?)
ORDER BY `CLog$`.CloseDt, `CLog$`.OpenDt

CLog is a table on another tab w/i the same worksheet. The parameters are tied to 2 cells ($a$1 and $b$1).

So in english terms I'm pulling all rows where either the open date or closed date are within two dates ($a$1 and $b$1).

The data in the CLog table contains rows where the following 3 conditions can exist

1. OpenDt ="" (formula results) and CloseDt = "" (formula results)
2. OpenDt <> "" (actual value) and CloseDt = "" (formulat results)
3. OpenDt <> "" (actual value) and CloseDt <> "" (actual value)

This works perfect except as follows.

For rows where I change the value in OpenDt or CloseDt on a given date (e.g. 7/17/2017) these rows do not appear in results (or it appears in results based on prior value) on a given date (e.g. 7/17/17) the data was changed.

For example, with parameters ($a$1 and $b$1) set to 7/10/17 and 7/17/17 respectively if I have a row where I change the OpenDt from "" to 7/17/17 this row is not pulled into the query. If I have a row where OpenDt = 7/1/17 and I change the CloseDt from "" to 7/17/17 the row is not pulled into the query. If I have a row with OpenDt = 7/15/17 and I change the value to 7/16/17 when I run the query this row still appears with OpenDt = 7/15/17.

I think, but I'm not positive that tomorrow when I open the spreadsheet all the above rows will appear in the query. The parameters are set to refresh the query if either parameter changes. I've closed the spreadsheet and reopened and this doesn't not fix the problem. I've executed Refresh All and refresh from the query itself. Nothing will get the changed data to appear in the query. It almost seems like Excel is keeping a copy of the data in memory for the day and not looking at the changed data when it updates for a given date but that's just a WAG.

Any and all help is greatly appreciated.

- KO

 

[ko1967]

Additional info: I opened the file today and altered the dates in the parameter cells ($b$1 - 7/18/17) and the rows for the altered data in the table (i.e. dates I changed to 7/17/17) still do not appear in the query results. It's as if Excel has taken a copy of the datatable when the query was first created.