Simon,
You're right - it was the SQL statement. Good troubleshooting. I experimented and googled and it appears that access is biased towards US format when evaluating queries. If access can interpret the date as US then it will. In the process of hunting I found a code snippet that converts the date literal to 'international' format of YYYY-MM-DD. This is supposed to work without regard to local settings. It appears to, even with the short format set to dd-MMM-yy (12-Oct-02).
The code line is:
sdate = year(sdate) & "-" & left("00",2-len(month(sdate))) & month(sdate) & "-" & left("00",2-len(day(sdate))) & day(sdate)
I used the above when I build the queries in Xlgraph and hopefully that takes care of the date issue. - note that I still don't modify strUserQuery (I think that's what Michael is using in the temperature script to interface to Xlgraph).
I also needed to change the time format with local date set to D/M/YY to avoid problems here with the data being interpreted correctly when pasted into excel. I added a routine that will convert the AM/PM to 24-hour clock to work around that.
I've put up a new version of Xlgraph here
If this doesn't seem to have fixed the issue let me know.
jim
You're right - it was the SQL statement. Good troubleshooting. I experimented and googled and it appears that access is biased towards US format when evaluating queries. If access can interpret the date as US then it will. In the process of hunting I found a code snippet that converts the date literal to 'international' format of YYYY-MM-DD. This is supposed to work without regard to local settings. It appears to, even with the short format set to dd-MMM-yy (12-Oct-02).
The code line is:
sdate = year(sdate) & "-" & left("00",2-len(month(sdate))) & month(sdate) & "-" & left("00",2-len(day(sdate))) & day(sdate)
I used the above when I build the queries in Xlgraph and hopefully that takes care of the date issue. - note that I still don't modify strUserQuery (I think that's what Michael is using in the temperature script to interface to Xlgraph).
I also needed to change the time format with local date set to D/M/YY to avoid problems here with the data being interpreted correctly when pasted into excel. I added a routine that will convert the AM/PM to 24-hour clock to work around that.
I've put up a new version of Xlgraph here
If this doesn't seem to have fixed the issue let me know.
jim
Comment