Urgent Date Help Needed Please!!! 
Author Message
 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  
 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  
 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  
 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  
 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  
 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  
 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  
 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  
 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  
 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  
 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  
 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  
 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  
 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  
 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  
 
 [ 15 post ] 

 Relevant Pages 

1. Urgent Date Help Needed Please!!!

2. Please, Please, Please I need help working with dates

3. Need Urgent help please

4. Urgent help needed please help

5. URGENT help needed, please help

6. Urgent help needed please help

7. Urgent help needed please help

8. Newbies need help......Urgent please help!!

9. Please Help Urgent help needed!!!!

10. PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP,

11. Urgent help needed for crystal/vb5 and passing dates

12. Date validation script needed - Please help!!!

 

 
Powered by phpBB® Forum Software