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.
Filtering dates not working on some PC's
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Filtering dates not working on some PC's
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- GoldLounger
- Posts: 2630
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: Filtering dates not working on some PC's
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
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
John
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filtering dates not working on some PC's
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...
With <>, this will hopefully work:
..., Criteria1:="<>" & CLng(dtThis)
It won't work in all circumstances, though...
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Filtering dates not working on some PC's
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
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)
Hans, I have tried CLng and it did not work on the others PC's
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filtering dates not working on some PC's
Try this then:
..., Criteria1:="<>" & Format(dtThis, Range('B5").NumberFormat)
..., Criteria1:="<>" & Format(dtThis, Range('B5").NumberFormat)
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Filtering dates not working on some PC's
TX.
Will give it a try on an offending PC when back at the office tomorrow.
EDIT
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.
Will give it a try on an offending PC when back at the office tomorrow.
EDIT
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Filtering dates not working on some PC's
Quick update Hans,
Seems your custom filter: ..., Criteria1:="<>" & Format(dtThis, Range('B5").NumberFormat) seems to be working.
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.
Seems your custom filter: ..., Criteria1:="<>" & Format(dtThis, Range('B5").NumberFormat) seems to be working.
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands