The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Recordset date filtering problem
Posted: 16 Mar 06 7:16 AM
|
imurel udtrapningimurel 90 go imurel 90
I'm having some issues filtering a datagrid recordset when it comes to dates. Done this before without problem. Having big problems this time.
Simple filter string:
CLOSEDATE >= #"& dteDate.DateTime &"# AND CLOSEDATE < #"& DateAdd("d",29,dteDate.DateTime) &"#
I've used both # and ' to wrap. Same results.
The grid was construct at run time, the CLOSEDATE column is:
col.FormatType = ftDateTime
col.FormatString = "mm/dd/yyyy"
Results:
Most of the data is within the range, but I keep getting other dates with the correct month, but the wrong days and year. Any ideas would be appreciated.
MikeB
P.S. Don't suggest using the SQL prop  |
|
|
|
Re: Recordset date filtering problem
Posted: 16 Mar 06 10:30 AM
|
Hi Mike.
The route I would take is wrapping the dates with #, but not sure why that is not working. Maybe output the filter string to a MsgBox to see exactly what it looks like, maybe you'll see something strange. 
Also, why not suggest the SQL prop? Sure the performance will be better with a filter, but really unless we're talking a whole lot of data is it really going to make that much of a difference?
Oh, one other idea, does the filter work if you just use one side of the range (filter on just one date)? Something to try, but I doubt that will make a difference.
-Ryan |
|
|
|
Re: Recordset date filtering problem
Posted: 16 Mar 06 11:15 AM
|
CLOSEDATE = #3/31/2006#
THis is the string when I tested a single date. It returned CORRECT data.
(CLOSEDATE >= #3/31/2006# AND CLOSEDATE < #5/29/2006#)
THis is what a range looks like. It DOES NOT work.
|
|
|
| |
|
Re: Recordset date filtering problem
Posted: 16 Mar 06 11:35 AM
|
So it worked with the single condition? Weird.
What if you grouped the conditions (again, doubt it will change anything, just grasping at ideas)
ie:
(CLOSEDATE >= #3/31/2006#) AND (CLOSEDATE < #5/29/2006#) ???
Admittedly I don't work as often with old-skool ADO, but does the filter prop allow syntax using "BETWEEN"? Something like
"CLOSEDATE BETWEEN #9/8/2000# AND #10/8/2000#" ???
I might be way off there, but something like that sounds familiar.
Anyway, I'd just modify the SQL and be done with it 
-Ryan |
|
|
|
Re: Recordset date filtering problem
Posted: 16 Mar 06 12:22 PM
|
fiogf49gjkf0d I'm done with it.
I converted the column and the date I'm comparing to yyyymmdd format and it worked like a charm. Since I was comparing strings, had to get them in the proper order.
Eugenio got me on the right path....as frustrating as it is to resort to this
MikeB |
|
|
| |
|
Re: Recordset date filtering problem
Posted: 16 Mar 06 4:43 PM
|
fiogf49gjkf0d Glad it is working now. If the string conversion thing works then I suppose that is not too bad.
Hehe. I cringe at the thought of not using those .NET managed data objects. |
|
|
|
Re: Recordset date filtering problem
Posted: 17 Mar 06 3:28 AM
|
I know you say you've fixed / worked-around this now but I had a similar issue yesterday with a date criteria in Access 2003 and just for everyone's info, it may be the same issue.
The SQL string in the query builder showed quite correctly as StartDate <= #07/04/2004# however in the "design view" the Criteria box showed as "<=#04/07/2004#". I checked every setting in XP (UK dates in Regional Settings) and every possible setting in Access (I think!) but it kept reversing the dd/mm values. In the end I had to resort to using he syntax Format(myDate,"dd/mm/yyyy") to get it to work.
Also, another strange issue was that if I used Format(StartDate,"dd/mm/yyyy") <= Format(myDate,"dd/mm/yyyy") it DIDNT work but omitting the first format and keeping the syntax as "StartDate <= Format(myDate,"dd/mm/yyyy") worked fine !!!
The rule is: for dates, work with strings where possible (YYYYMMDD) and dont trust the background logic to work "as you think it should" !!! |
|
|
|
Re: Recordset date filtering problem
Posted: 18 Mar 06 1:57 AM
|
FormatDateTime(Now, vbShortDate) is a function I use a LOT. I mainly use it when I want to use this as dateTimeEdit.Text properties, as it doesn't do much good in SQL statements. It gives me a nice mm/dd/yyyy format, perfect for entering today's date when you're adding a record to a datagrid.
I found during our upgrade to 6.2.2 (SP3 may be different) that this statement fails:
stringSQL = "SELECT NOTEDATE, NOTE FROM TABLE WHERE NOTEDATE = '" & FormatDateTime(dateTimeNote.Text, vbShortDate) & "' AND NOTE = '" & textBoxNote.Text & "'"
I had to use Application.BasicFunctions.DateToISO(dateTimeNote.Text) and the only time I've had to use this is if I do some sort of compare in SQL directly. A good reason why you would use this is it converts all dates to one format so mm/dd/yyyy compared to dd/mm/yyyy would be the same ISO format and compare without any ADO clairvoyance. Personally I don't like DateToISO or ISOToDate but there are some small cases where it's absolutely necessary to use them. I believe another such case is scripted insert or updates, which hopefully no one in the ADO forum uses since .AddNew and .Update work just as well on a nice SQL select statement.
I can't remember exactly why I had to convert it but I do remember MsgBox debugs in both Query Analyzer and Execute SQL to try and narrow down the provider versus a general SQL problem. Working backwards led me to use DateToISO and it magically working where it was constantly failing. We don't even have people from multiple locales so mm/dd and dd/mm is really not an issue for us though having this there will take care of that in the future. |
|
|
| |
|