Filtering dates not working on some PC's

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Filtering dates not working on some PC's

Post by Rudi »

Hi,

I'm experiencing a strange issue and I've run out of ideas to resolve it.
In our team we have code to extract month data from a database using SQL and writing it to an Excel worksheet
The code brings the records in great, but after the records are available, the next block of code filters the records based on a specified date stored in a date variable called "dtThis". On my PC the filtering is working perfectly and the specified date is filtered, but on other users PC's, they get no record matches and the filter ends up showing no records. In the screenshots below, the first one is my PC showing the date in the variable and the date to be filtered in the B column. The second screenshot is from a user where the code is returning no records as the filter is not finding matches in the date field.

We have changed regional settings, use the Format() function in VBA to try forcing the fate format, and one or two other ideas....but no success on these users PC's? The code works (on my PC) so there is something else weird happening that I am obviously overlooking?

Any ideas....I'd be grateful for some support. TX.
1.jpg
2.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

jstevens
GoldLounger
Posts: 2617
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Filtering dates not working on some PC's

Post by jstevens »

Rudi,

Perhaps it relates to how the date is stored in the SQL database: alone with the time-stamp.

In my SQL statement I use the "Short Date". Try something like this: format(myTable.ProcessingDate,"Short Date") AS ProcessingDate

Regards,
John
Regards,
John

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filtering dates not working on some PC's

Post by HansV »

Filtering with dates and searching for dates using Excel VBA is a PITA because of the way VBA sometimes recognizes only US date format, and sometimes recognizes only the local system date format.

With <>, this will hopefully work:

..., Criteria1:="<>" & CLng(dtThis)

It won't work in all circumstances, though... :hairout:
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Filtering dates not working on some PC's

Post by Rudi »

Thanks John, I have not considered making changes in the SQL side. Its worth investigating.
Hans, I have tried CLng and it did not work on the others PC's :sad:
Does VBA not use the serial number behind the date format? Or could I somehow force it to work on the serial number (even temporarily changing the date in column B to a serial? (Sorry, I'm really fishing here)
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filtering dates not working on some PC's

Post by HansV »

Try this then:

..., Criteria1:="<>" & Format(dtThis, Range('B5").NumberFormat)
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Filtering dates not working on some PC's

Post by Rudi »

TX.
Will give it a try on an offending PC when back at the office tomorrow.

EDIT
:groan: Both persons who's PC's are running the code faulty are on leave this week. Will provide feedback re you suggested changes when they return.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Filtering dates not working on some PC's

Post by Rudi »

Quick update Hans,

Seems your custom filter: ..., Criteria1:="<>" & Format(dtThis, Range('B5").NumberFormat) seems to be working. :cheers:
I have not have any negative feedback to date on the PC's where the initial filtering errors were reported. As they say; no news is good news, so...

Many TX for that advice.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filtering dates not working on some PC's

Post by HansV »

Thanks for the feedback!
Best wishes,
Hans