VBScript, Urgent Date Help Needed Please!!!

Urgent Date Help Needed Please!!!

Author	Message
MDJ #1 / 15	Urgent Date Help Needed Please!!! I am really not the kind of person who comes straight to a newsgroup to palm his problems off on other people, but I have really tried to solve this problem and on many occasions thought I had, but things keep coming back to haunt me! The problem is with filtering a recordset by date. There are two fields in the Periods table: StartDate and EndDate - StartDate is the first day of the month, EndDate is the last. Very simply, the following filter needs to pick out record(s) for this month, where the start date is equal or less than today, and the end date is equal or greater than today. Periods.Filter = "StartDate <= #" & Date & "# And EndDate >= #" & Date & "#" Basically, if we're on the first of the month, the filter doesn't find any records, even though I have an "=" in there. The two fields are Medium Date in Access (to try to get around an already existent problem with US and UK dates!!...........I HATE DATES!). As you can see, this is making me unstable, any help greatly appreciated! Thanks, Martin
Sun, 14 Mar 2004 02:25:10 GMT

Aaron Bertrand [MVP #2 / 15	Urgent Date Help Needed Please!!! Quote: > Periods.Filter = "StartDate <= #" & Date & "# And EndDate >= #" & Date & "#" Is this code in ASP? If so, try using ' instead of # within the string... # is needed at the database level in Access, but I believe it would use ' once it's in the ASP page. Not sure since I can't stand Access, and don't use rs.filter, but it's worth a try. (Of course, you could just write your query to do that filter in the first place, using a WHERE clause, eliminating a good chunk of data you're needlessly sending across the wire.) www.aspfaq.com
Sun, 14 Mar 2004 02:40:55 GMT

MDJ #3 / 15	Urgent Date Help Needed Please!!! Nope, just tried it (sure i've tried it b4 too). changing the # to ' behaves the same way as a filter trying to do that in the initial WHERE statement give a data type mismatch removing the ' in the WHERE statement just misbehaves as originally. and having # in the WHERE statement again, misbehaves. This is not the isses, I feel it's something to do with date format or the way i'm using <= Please keep them coming!!! Quote: > > Periods.Filter = "StartDate <= #" & Date & "# And EndDate >= #" & Date & > "#" > Is this code in ASP? If so, try using ' instead of # within the string... # > is needed at the database level in Access, but I believe it would use ' once > it's in the ASP page. Not sure since I can't stand Access, and don't use > rs.filter, but it's worth a try. > (Of course, you could just write your query to do that filter in the first > place, using a WHERE clause, eliminating a good chunk of data you're > needlessly sending across the wire.) > www.aspfaq.com
Sun, 14 Mar 2004 02:55:46 GMT

Aaron Bertrand [MVP #4 / 15	Urgent Date Help Needed Please!!! Quote: > and having # in the WHERE statement again, misbehaves. What does "misbehaves" mean? Show your SQL statement, AFTER substitution (instead of conn.execute(sql), do response.write(sql): response.end), and the data that's in your table that you believe should be retrieved. www.aspfaq.com
Sun, 14 Mar 2004 03:05:17 GMT

yabb #5 / 15	Urgent Date Help Needed Please!!! Try surrounding all of your various dates with dateValue() so they are all interpreted exactly the same. I was experiencing similar problems till I started doing this. Quote: >I am really not the kind of person who comes straight to a newsgroup to palm >his problems off on other people, but I have really tried to solve this >problem and on many occasions thought I had, but things keep coming back to >haunt me! >The problem is with filtering a recordset by date. >There are two fields in the Periods table: StartDate and EndDate - StartDate >is the first day of the month, EndDate is the last. Very simply, the >following filter needs to pick out record(s) for this month, where the start >date is equal or less than today, and the end date is equal or greater than >today. >Periods.Filter = "StartDate <= #" & Date & "# And EndDate >= #" & Date & "#" >Basically, if we're on the first of the month, the filter doesn't find any >records, even though I have an "=" in there. >The two fields are Medium Date in Access (to try to get around an already >existent problem with US and UK dates!!...........I HATE DATES!). >As you can see, this is making me unstable, any help greatly appreciated! >Thanks, >Martin
Sun, 14 Mar 2004 03:17:26 GMT

MDJ #6 / 15	Urgent Date Help Needed Please!!! StartDate <= #1/10/2001# And EndDate >= #1/10/2001# OK, i've taken the above from a Response.Write, so that's exactly what it's working from (I know it isn't 1st October (International Date Format), but that's the problem i'm trying to get around, it's a 1st of the month problem). I've tried the above with and without the #, and with ' The above is not finding records where StartDate = 1/10/2001 and EndDate = 31/10/2001, when it looks like it should be. Thanks, Martin. Quote: > > and having # in the WHERE statement again, misbehaves. > What does "misbehaves" mean? Show your SQL statement, AFTER substitution > (instead of conn.execute(sql), do response.write(sql): response.end), and > the data that's in your table that you believe should be retrieved. > www.aspfaq.com
Sun, 14 Mar 2004 03:59:50 GMT

MDJ #7 / 15	Urgent Date Help Needed Please!!! Just tried it, no difference. Thanks, Martin. Quote: > Try surrounding all of your various dates with dateValue() so they are all > interpreted exactly the same. I was experiencing similar problems till I > started doing this. > >I am really not the kind of person who comes straight to a newsgroup to palm > >his problems off on other people, but I have really tried to solve this > >problem and on many occasions thought I had, but things keep coming back to > >haunt me! > >The problem is with filtering a recordset by date. > >There are two fields in the Periods table: StartDate and EndDate - StartDate > >is the first day of the month, EndDate is the last. Very simply, the > >following filter needs to pick out record(s) for this month, where the start > >date is equal or less than today, and the end date is equal or greater than > >today. > >Periods.Filter = "StartDate <= #" & Date & "# And EndDate >= #" & Date & "#" > >Basically, if we're on the first of the month, the filter doesn't find any > >records, even though I have an "=" in there. > >The two fields are Medium Date in Access (to try to get around an already > >existent problem with US and UK dates!!...........I HATE DATES!). > >As you can see, this is making me unstable, any help greatly appreciated! > >Thanks, > >Martin
Sun, 14 Mar 2004 04:02:26 GMT

Edwar #8 / 15	Urgent Date Help Needed Please!!! Hi I use similar filters and they work okay, but I have become aware of one issue with the filter property, you must put spaces either side of the operators. rstA.filter="ADate<=#1 Jan 2001#" will not always work rstA.filter="ADate <= #1 Jan 2001#" does work Also, just to make things easier to debug, if you're simply ANDing clauses (but not if you're ANDing and ORing because that have limitations), put them in brackets: (field1 >= #date1#) AND (field2 <= #date2#) Edward Quote: > I am really not the kind of person who comes straight to a newsgroup to palm > his problems off on other people, but I have really tried to solve this > problem and on many occasions thought I had, but things keep coming back to > haunt me! > The problem is with filtering a recordset by date. > There are two fields in the Periods table: StartDate and EndDate - StartDate > is the first day of the month, EndDate is the last. Very simply, the > following filter needs to pick out record(s) for this month, where the start > date is equal or less than today, and the end date is equal or greater than > today. > Periods.Filter = "StartDate <= #" & Date & "# And EndDate >= #" & Date & "#" > Basically, if we're on the first of the month, the filter doesn't find any > records, even though I have an "=" in there. > The two fields are Medium Date in Access (to try to get around an already > existent problem with US and UK dates!!...........I HATE DATES!). > As you can see, this is making me unstable, any help greatly appreciated! > Thanks, > Martin
Sun, 14 Mar 2004 04:11:31 GMT

yabb #9 / 15	Urgent Date Help Needed Please!!! One other thing I can think of is perhaps there is a null value in your access date fields. This has happened to me occasioally. Quote: >Just tried it, no difference. >Thanks, >Martin. >> Try surrounding all of your various dates with dateValue() so they are all >> interpreted exactly the same. I was experiencing similar problems till I >> started doing this. >> >I am really not the kind of person who comes straight to a newsgroup to >palm >> >his problems off on other people, but I have really tried to solve this >> >problem and on many occasions thought I had, but things keep coming back >to >> >haunt me! >> >The problem is with filtering a recordset by date. >> >There are two fields in the Periods table: StartDate and EndDate - >StartDate >> >is the first day of the month, EndDate is the last. Very simply, the >> >following filter needs to pick out record(s) for this month, where the >start >> >date is equal or less than today, and the end date is equal or greater >than >> >today. >> >Periods.Filter = "StartDate <= #" & Date & "# And EndDate >= #" & Date & >"#" >> >Basically, if we're on the first of the month, the filter doesn't find >any >> >records, even though I have an "=" in there. >> >The two fields are Medium Date in Access (to try to get around an already >> >existent problem with US and UK dates!!...........I HATE DATES!). >> >As you can see, this is making me unstable, any help greatly appreciated! >> >Thanks, >> >Martin
Sun, 14 Mar 2004 04:13:40 GMT

Aaron Bertrand [MVP #10 / 15	Urgent Date Help Needed Please!!! Use yyyy-mm-dd format which will always work. The database probably is confusing October for January (or vice-versa). www.aspfaq.com Quote: > StartDate <= #1/10/2001# And EndDate >= #1/10/2001# > OK, i've taken the above from a Response.Write, so that's exactly what it's > working from (I know it isn't 1st October (International Date Format), but > that's the problem i'm trying to get around, it's a 1st of the month > problem). > I've tried the above with and without the #, and with ' > The above is not finding records where StartDate = 1/10/2001 and EndDate = > 31/10/2001, when it looks like it should be. > Thanks, > Martin. > > > and having # in the WHERE statement again, misbehaves. > > What does "misbehaves" mean? Show your SQL statement, AFTER substitution > > (instead of conn.execute(sql), do response.write(sql): response.end), and > > the data that's in your table that you believe should be retrieved. > > www.aspfaq.com
Sun, 14 Mar 2004 04:17:45 GMT

MDJ #11 / 15	Urgent Date Help Needed Please!!! I'm already doing this. the parenthesis had occurred to me but I tried it and no difference. Thanks, Martin. Quote: > Hi > I use similar filters and they work okay, but I have become aware of one > issue with the filter property, you must put spaces either side of the > operators. > rstA.filter="ADate<=#1 Jan 2001#" will not always work > rstA.filter="ADate <= #1 Jan 2001#" does work > Also, just to make things easier to debug, if you're simply ANDing clauses > (but not if you're ANDing and ORing because that have limitations), put them > in brackets: > (field1 >= #date1#) AND (field2 <= #date2#) > Edward > > I am really not the kind of person who comes straight to a newsgroup to > palm > > his problems off on other people, but I have really tried to solve this > > problem and on many occasions thought I had, but things keep coming back > to > > haunt me! > > The problem is with filtering a recordset by date. > > There are two fields in the Periods table: StartDate and EndDate - > StartDate > > is the first day of the month, EndDate is the last. Very simply, the > > following filter needs to pick out record(s) for this month, where the > start > > date is equal or less than today, and the end date is equal or greater > than > > today. > > Periods.Filter = "StartDate <= #" & Date & "# And EndDate >= #" & Date & > "#" > > Basically, if we're on the first of the month, the filter doesn't find any > > records, even though I have an "=" in there. > > The two fields are Medium Date in Access (to try to get around an already > > existent problem with US and UK dates!!...........I HATE DATES!). > > As you can see, this is making me unstable, any help greatly appreciated! > > Thanks, > > Martin
Sun, 14 Mar 2004 04:21:11 GMT

Edwar #12 / 15	Urgent Date Help Needed Please!!! Incidently, I never use nn/nn/nn as this gives me grief, I always use d mmm yyyy as this never fails. Edward Quote: > I'm already doing this. > the parenthesis had occurred to me but I tried it and no difference. > Thanks, > Martin. > > Hi > > I use similar filters and they work okay, but I have become aware of one > > issue with the filter property, you must put spaces either side of the > > operators. > > rstA.filter="ADate<=#1 Jan 2001#" will not always work > > rstA.filter="ADate <= #1 Jan 2001#" does work > > Also, just to make things easier to debug, if you're simply ANDing clauses > > (but not if you're ANDing and ORing because that have limitations), put > them > > in brackets: > > (field1 >= #date1#) AND (field2 <= #date2#) > > Edward > > > I am really not the kind of person who comes straight to a newsgroup to > > palm > > > his problems off on other people, but I have really tried to solve this > > > problem and on many occasions thought I had, but things keep coming back > > to > > > haunt me! > > > The problem is with filtering a recordset by date. > > > There are two fields in the Periods table: StartDate and EndDate - > > StartDate > > > is the first day of the month, EndDate is the last. Very simply, the > > > following filter needs to pick out record(s) for this month, where the > > start > > > date is equal or less than today, and the end date is equal or greater > > than > > > today. > > > Periods.Filter = "StartDate <= #" & Date & "# And EndDate >= #" & Date & > > "#" > > > Basically, if we're on the first of the month, the filter doesn't find > any > > > records, even though I have an "=" in there. > > > The two fields are Medium Date in Access (to try to get around an > already > > > existent problem with US and UK dates!!...........I HATE DATES!). > > > As you can see, this is making me unstable, any help greatly > appreciated! > > > Thanks, > > > Martin
Sun, 14 Mar 2004 04:27:43 GMT

MDJ #13 / 15	Urgent Date Help Needed Please!!! I had tried this, substituting Date for strDate where strDate is a manually built string like 1/October/2001 But it would appear that this is not the problem! Any more ideas? Thanks, Martin. Quote: > Incidently, I never use nn/nn/nn as this gives me grief, I always use d mmm > yyyy as this never fails. > Edward > > I'm already doing this. > > the parenthesis had occurred to me but I tried it and no difference. > > Thanks, > > Martin. > > > Hi > > > I use similar filters and they work okay, but I have become aware of one > > > issue with the filter property, you must put spaces either side of the > > > operators. > > > rstA.filter="ADate<=#1 Jan 2001#" will not always work > > > rstA.filter="ADate <= #1 Jan 2001#" does work > > > Also, just to make things easier to debug, if you're simply ANDing > clauses > > > (but not if you're ANDing and ORing because that have limitations), put > > them > > > in brackets: > > > (field1 >= #date1#) AND (field2 <= #date2#) > > > Edward > > > > I am really not the kind of person who comes straight to a newsgroup > to > > > palm > > > > his problems off on other people, but I have really tried to solve > this > > > > problem and on many occasions thought I had, but things keep coming > back > > > to > > > > haunt me! > > > > The problem is with filtering a recordset by date. > > > > There are two fields in the Periods table: StartDate and EndDate - > > > StartDate > > > > is the first day of the month, EndDate is the last. Very simply, the > > > > following filter needs to pick out record(s) for this month, where the > > > start > > > > date is equal or less than today, and the end date is equal or greater > > > than > > > > today. > > > > Periods.Filter = "StartDate <= #" & Date & "# And EndDate >= #" & Date > & > > > "#" > > > > Basically, if we're on the first of the month, the filter doesn't find > > any > > > > records, even though I have an "=" in there. > > > > The two fields are Medium Date in Access (to try to get around an > > already > > > > existent problem with US and UK dates!!...........I HATE DATES!). > > > > As you can see, this is making me unstable, any help greatly > > appreciated! > > > > Thanks, > > > > Martin
Sun, 14 Mar 2004 04:47:39 GMT

Edwar #14 / 15	Urgent Date Help Needed Please!!! Why '1/October/2001' instead of '1 October 2001' ? - The latter will work (it does for me, anyway) Edward Quote: > I had tried this, substituting Date for strDate where strDate is a manually > built string like 1/October/2001 > But it would appear that this is not the problem! > Any more ideas? > Thanks, > Martin. > > Incidently, I never use nn/nn/nn as this gives me grief, I always use d > mmm > > yyyy as this never fails. > > Edward > > > I'm already doing this. > > > the parenthesis had occurred to me but I tried it and no difference. > > > Thanks, > > > Martin. > > > > Hi > > > > I use similar filters and they work okay, but I have become aware of > one > > > > issue with the filter property, you must put spaces either side of the > > > > operators. > > > > rstA.filter="ADate<=#1 Jan 2001#" will not always work > > > > rstA.filter="ADate <= #1 Jan 2001#" does work > > > > Also, just to make things easier to debug, if you're simply ANDing > > clauses > > > > (but not if you're ANDing and ORing because that have limitations), > put > > > them > > > > in brackets: > > > > (field1 >= #date1#) AND (field2 <= #date2#) > > > > Edward > > > > > I am really not the kind of person who comes straight to a newsgroup > > to > > > > palm > > > > > his problems off on other people, but I have really tried to solve > > this > > > > > problem and on many occasions thought I had, but things keep coming > > back > > > > to > > > > > haunt me! > > > > > The problem is with filtering a recordset by date. > > > > > There are two fields in the Periods table: StartDate and EndDate - > > > > StartDate > > > > > is the first day of the month, EndDate is the last. Very simply, the > > > > > following filter needs to pick out record(s) for this month, where > the > > > > start > > > > > date is equal or less than today, and the end date is equal or > greater > > > > than > > > > > today. > > > > > Periods.Filter = "StartDate <= #" & Date & "# And EndDate >= #" & > Date > > & > > > > "#" > > > > > Basically, if we're on the first of the month, the filter doesn't > find > > > any > > > > > records, even though I have an "=" in there. > > > > > The two fields are Medium Date in Access (to try to get around an > > > already > > > > > existent problem with US and UK dates!!...........I HATE DATES!). > > > > > As you can see, this is making me unstable, any help greatly > > > appreciated! > > > > > Thanks, > > > > > Martin
Sun, 14 Mar 2004 05:14:24 GMT

Scott Musac #15 / 15	Urgent Date Help Needed Please!!! Quote: >I am really not the kind of person who comes straight to a newsgroup to palm >his problems off on other people, but I have really tried to solve this >problem and on many occasions thought I had, but things keep coming back to >haunt me! >The problem is with filtering a recordset by date. >There are two fields in the Periods table: StartDate and EndDate - StartDate >is the first day of the month, EndDate is the last. Very simply, the >following filter needs to pick out record(s) for this month, where the start >date is equal or less than today, and the end date is equal or greater than >today. >Periods.Filter = "StartDate <= #" & Date & "# And EndDate >= #" & Date & "#" >Basically, if we're on the first of the month, the filter doesn't find any >records, even though I have an "=" in there. >The two fields are Medium Date in Access (to try to get around an already >existent problem with US and UK dates!!...........I HATE DATES!). >As you can see, this is making me unstable, any help greatly appreciated! >Thanks, >Martin Doing >= or <= on Dates inevitably leads to problems because equality seldom happens. Why? Because a date field is a Date/Time field. The Quote: > and < work fine because the time portion of the field isn't computed. But 01/01/2001 09:30 AM != 01/01/2001 09:31 AM So, you can either fiddle with the data parameters, or extract the date parts from them using DatePart. Example (using MSSQL): select * from table where (year(reccreated)>=2001 AND month(reccreated)>=1 and day(reccreated)>=1) AND (month(reccreated)<=1 and day(reccreated)<=2) Returns all records on the 1st and 2nd of Jan 2001 HTH Scott
Sun, 14 Mar 2004 12:52:56 GMT

Page 1 of 2

[ 15 post ]