record selection date problems 
Author Message
 record selection date problems

Hello all,

I'm having problems using the following record selection formula:

({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
and {MyTable.DateStamp} >= Date(2000,6,16) and {MyTable.DateStamp} <=
Date(2000,6,16)

It returns no records, if I change it to:

({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
and {MyTable.DateStamp} >= Date(2000,6,15) and {MyTable.DateStamp} <=
Date(2000,6,15)

It also returns no records.

If I change it to:

({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
and {MyTable.DateStamp} >= Date(2000,6,15) and {MyTable.DateStamp} <=
Date(2000,6,16)

It returns 45 records.  I don't understand why I don't see some records when
I just check for one of these days.

--
Eric W. Goforth
Office Systems Integrator II
First Citizens Bank - 100 E. Tryon Rd. - Raleigh, NC



Tue, 10 Dec 2002 03:00:00 GMT  
 record selection date problems
Hi Eric, is MyTable.DateStamp a Date field or a DateTime field?  If it is a
Date field try using:

    ...
    and {MyTable.DateStamp} = Date(2000,6,16)

If it is a DateTime field, try using:

    ...
    and {MyTable.DateStamp} In DateTime(2000,6,16,0,0,0) To
                                              DateTime(2000,6,16,23,59,59)

HTH, Paul
--
Paul Little, Senior Programmer/Analyst
SVi Retail Systems
San Diego, CA


Quote:
> Hello all,

> I'm having problems using the following record selection formula:

> ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
> and {MyTable.DateStamp} >= Date(2000,6,16) and {MyTable.DateStamp} <=
> Date(2000,6,16)

> It returns no records, if I change it to:

> ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
> and {MyTable.DateStamp} >= Date(2000,6,15) and {MyTable.DateStamp} <=
> Date(2000,6,15)

> It also returns no records.

> If I change it to:

> ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
> and {MyTable.DateStamp} >= Date(2000,6,15) and {MyTable.DateStamp} <=
> Date(2000,6,16)

> It returns 45 records.  I don't understand why I don't see some records
when
> I just check for one of these days.

> --
> Eric W. Goforth
> Office Systems Integrator II
> First Citizens Bank - 100 E. Tryon Rd. - Raleigh, NC



Tue, 10 Dec 2002 03:00:00 GMT  
 record selection date problems


Fri, 19 Jun 1992 00:00:00 GMT  
 record selection date problems
Thanks, I tried:

({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
and {MyTable.DateStamp} = Date(2000,6,16)

I didn't get any matches, although I know that there are entries on June
16th.

Therefore, I tried:

({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
and {MyTable.DateStamp} >= DateTime(2000,6,16,0,0,0) and
{AppDetails.DateStamp} <= DateTime(2000,6,16,23,59,59)

I got an error message "A date is required here" with the insertion point
placed at the beginning of DateTime(2000,6,16,0,0,0).

I tried:

({AppDetails.Statusl} = "A" or {AppDetails.Statusl} = "a") and
{AppDetails.ID} = 9 and {AppDetails.DateStamp} In DateTime(2000,6,12,0,0,0)
and {AppDetails.DateStamp} To DateTime(2000,6,16,23,59,59)

I got a message "A date range is required here" with the insertion point at
the beginning of "DateTime(2000,6,12,0,0,0) ".

MyTable is in a MS SQL Server database.  I do not have SQL Server on my
computer, but I do have Access 97.  So I added the table to an Access
database using Get External Data > Link Tables and then look at MyTable in
design view; it says that DateStamp is a Date/Time field.

In addition, I see the following entries in the DateStamp field:

3/16/98 4:35:00 PM

3/31/99 11:12:00 AM

Does Crystal have some problem with the way that SQL Server stores dates,
perhaps?

I tried the following, just out of curiousity with the same error message.

({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
and {MyTable.DateStamp} = DateTime(2000,6,16,6,0,0)


Quote:
> Hi Eric, is MyTable.DateStamp a Date field or a DateTime field?  If it is
a
> Date field try using:

>     ...
>     and {MyTable.DateStamp} = Date(2000,6,16)

> If it is a DateTime field, try using:

>     ...
>     and {MyTable.DateStamp} In DateTime(2000,6,16,0,0,0) To
>                                               DateTime(2000,6,16,23,59,59)

> HTH, Paul
> --
> Paul Little, Senior Programmer/Analyst
> SVi Retail Systems
> San Diego, CA



> > Hello all,

> > I'm having problems using the following record selection formula:

> > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} =
9
> > and {MyTable.DateStamp} >= Date(2000,6,16) and {MyTable.DateStamp} <=
> > Date(2000,6,16)

> > It returns no records, if I change it to:

> > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} =
9
> > and {MyTable.DateStamp} >= Date(2000,6,15) and {MyTable.DateStamp} <=
> > Date(2000,6,15)

> > It also returns no records.

> > If I change it to:

> > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} =
9
> > and {MyTable.DateStamp} >= Date(2000,6,15) and {MyTable.DateStamp} <=
> > Date(2000,6,16)

> > It returns 45 records.  I don't understand why I don't see some records
> when
> > I just check for one of these days.



Sat, 14 Dec 2002 03:00:00 GMT  
 record selection date problems
With my deadline drawing near, I finally went with the following when trying
to select records from June 16th:

({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
and {MyTable.DateStamp} > Date(2000,6,15) and {MyTable.DateStamp} <
Date(2000,6,17)

I don't like it, but it works.  I'd still like to figure out why the other
stuff wasn't working.  I do the same type of logic when working with date
ranges.


Quote:
> Thanks, I tried:

> ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
> and {MyTable.DateStamp} = Date(2000,6,16)

> I didn't get any matches, although I know that there are entries on June
> 16th.

> Therefore, I tried:

> ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
> and {MyTable.DateStamp} >= DateTime(2000,6,16,0,0,0) and
> {AppDetails.DateStamp} <= DateTime(2000,6,16,23,59,59)

> I got an error message "A date is required here" with the insertion point
> placed at the beginning of DateTime(2000,6,16,0,0,0).

> I tried:

> ({AppDetails.Statusl} = "A" or {AppDetails.Statusl} = "a") and
> {AppDetails.ID} = 9 and {AppDetails.DateStamp} In

DateTime(2000,6,12,0,0,0)

- Show quoted text -

Quote:
> and {AppDetails.DateStamp} To DateTime(2000,6,16,23,59,59)

> I got a message "A date range is required here" with the insertion point
at
> the beginning of "DateTime(2000,6,12,0,0,0) ".

> MyTable is in a MS SQL Server database.  I do not have SQL Server on my
> computer, but I do have Access 97.  So I added the table to an Access
> database using Get External Data > Link Tables and then look at MyTable in
> design view; it says that DateStamp is a Date/Time field.

> In addition, I see the following entries in the DateStamp field:

> 3/16/98 4:35:00 PM

> 3/31/99 11:12:00 AM

> Does Crystal have some problem with the way that SQL Server stores dates,
> perhaps?

> I tried the following, just out of curiousity with the same error message.

> ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
> and {MyTable.DateStamp} = DateTime(2000,6,16,6,0,0)



> > Hi Eric, is MyTable.DateStamp a Date field or a DateTime field?  If it
is
> a
> > Date field try using:

> >     ...
> >     and {MyTable.DateStamp} = Date(2000,6,16)

> > If it is a DateTime field, try using:

> >     ...
> >     and {MyTable.DateStamp} In DateTime(2000,6,16,0,0,0) To

DateTime(2000,6,16,23,59,59)

- Show quoted text -

Quote:

> > HTH, Paul
> > --
> > Paul Little, Senior Programmer/Analyst
> > SVi Retail Systems
> > San Diego, CA



> > > Hello all,

> > > I'm having problems using the following record selection formula:

> > > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID}
=
> 9
> > > and {MyTable.DateStamp} >= Date(2000,6,16) and {MyTable.DateStamp} <=
> > > Date(2000,6,16)

> > > It returns no records, if I change it to:

> > > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID}
=
> 9
> > > and {MyTable.DateStamp} >= Date(2000,6,15) and {MyTable.DateStamp} <=
> > > Date(2000,6,15)

> > > It also returns no records.

> > > If I change it to:

> > > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID}
=
> 9
> > > and {MyTable.DateStamp} >= Date(2000,6,15) and {MyTable.DateStamp} <=
> > > Date(2000,6,16)

> > > It returns 45 records.  I don't understand why I don't see some
records
> > when
> > > I just check for one of these days.



Sat, 14 Dec 2002 03:00:00 GMT  
 record selection date problems
If that worked, then you should be able to use:

...
and {MyTable.DateStamp} In Date(2000,6,16) To Date(2000,6,16)

You might want to check File->Report Options... to see if DateTime fields
are being converted to Date fields.

-Paul
--
Paul Little, Senior Programmer/Analyst
SVi Retail Systems
San Diego, CA


Quote:
> With my deadline drawing near, I finally went with the following when
trying
> to select records from June 16th:

> ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
> and {MyTable.DateStamp} > Date(2000,6,15) and {MyTable.DateStamp} <
> Date(2000,6,17)

> I don't like it, but it works.  I'd still like to figure out why the other
> stuff wasn't working.  I do the same type of logic when working with date
> ranges.



> > Thanks, I tried:

> > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} =
9
> > and {MyTable.DateStamp} = Date(2000,6,16)

> > I didn't get any matches, although I know that there are entries on June
> > 16th.

> > Therefore, I tried:

> > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} =
9
> > and {MyTable.DateStamp} >= DateTime(2000,6,16,0,0,0) and
> > {AppDetails.DateStamp} <= DateTime(2000,6,16,23,59,59)

> > I got an error message "A date is required here" with the insertion
point
> > placed at the beginning of DateTime(2000,6,16,0,0,0).

> > I tried:

> > ({AppDetails.Statusl} = "A" or {AppDetails.Statusl} = "a") and
> > {AppDetails.ID} = 9 and {AppDetails.DateStamp} In
> DateTime(2000,6,12,0,0,0)
> > and {AppDetails.DateStamp} To DateTime(2000,6,16,23,59,59)

> > I got a message "A date range is required here" with the insertion point
> at
> > the beginning of "DateTime(2000,6,12,0,0,0) ".

> > MyTable is in a MS SQL Server database.  I do not have SQL Server on my
> > computer, but I do have Access 97.  So I added the table to an Access
> > database using Get External Data > Link Tables and then look at MyTable
in
> > design view; it says that DateStamp is a Date/Time field.

> > In addition, I see the following entries in the DateStamp field:

> > 3/16/98 4:35:00 PM

> > 3/31/99 11:12:00 AM

> > Does Crystal have some problem with the way that SQL Server stores
dates,
> > perhaps?

> > I tried the following, just out of curiousity with the same error
message.

> > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} =
9
> > and {MyTable.DateStamp} = DateTime(2000,6,16,6,0,0)



> > > Hi Eric, is MyTable.DateStamp a Date field or a DateTime field?  If it
> is
> > a
> > > Date field try using:

> > >     ...
> > >     and {MyTable.DateStamp} = Date(2000,6,16)

> > > If it is a DateTime field, try using:

> > >     ...
> > >     and {MyTable.DateStamp} In DateTime(2000,6,16,0,0,0) To

> DateTime(2000,6,16,23,59,59)

> > > HTH, Paul
> > > --
> > > Paul Little, Senior Programmer/Analyst
> > > SVi Retail Systems
> > > San Diego, CA



> > > > Hello all,

> > > > I'm having problems using the following record selection formula:

> > > > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and
{MyTable.ID}
> =
> > 9
> > > > and {MyTable.DateStamp} >= Date(2000,6,16) and {MyTable.DateStamp}
<=
> > > > Date(2000,6,16)

> > > > It returns no records, if I change it to:

> > > > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and
{MyTable.ID}
> =
> > 9
> > > > and {MyTable.DateStamp} >= Date(2000,6,15) and {MyTable.DateStamp}
<=
> > > > Date(2000,6,15)

> > > > It also returns no records.

> > > > If I change it to:

> > > > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and
{MyTable.ID}
> =
> > 9
> > > > and {MyTable.DateStamp} >= Date(2000,6,15) and {MyTable.DateStamp}
<=
> > > > Date(2000,6,16)

> > > > It returns 45 records.  I don't understand why I don't see some
> records
> > > when
> > > > I just check for one of these days.



Sat, 14 Dec 2002 03:00:00 GMT  
 record selection date problems


Fri, 19 Jun 1992 00:00:00 GMT  
 record selection date problems
I unchecked the  convert DateTime fields to Date fields checkbox.  Then I
tried the following record-selection formula:

({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
and {MyTable.DateStamp} In DateTime(2000,6,28,0,0,0) To
DateTime(2000,6,28,23,59,59)

I got the error message that "A string is required here" with the insertion
point at the beginning of DateTime(2000,6,28,0,0,0).  Puzzling....

I went back and rechecked the Convert DateTime fields to Date fields
checkbox and then tried the following record-selection formula:

({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
and {MyTable.DateStamp} In Date(2000,6,28) To Date(2000,6,28)

It returned no records

I then tried the following record-selection formula

({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} = 9
and {MyTable.DateStamp} > Date(2000,6,27) and {MyTable.DateStamp} <
Date(2000,6,29)

It returns four{*filter*} records.

Oh well, at least I have something that works, even if it is kind of an
inelegant solution.


Quote:
> If that worked, then you should be able to use:

> ...
> and {MyTable.DateStamp} In Date(2000,6,16) To Date(2000,6,16)

> You might want to check File->Report Options... to see if DateTime fields
> are being converted to Date fields.

> -Paul
> --
> Paul Little, Senior Programmer/Analyst
> SVi Retail Systems
> San Diego, CA



> > With my deadline drawing near, I finally went with the following when
> trying
> > to select records from June 16th:

> > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID} =
9
> > and {MyTable.DateStamp} > Date(2000,6,15) and {MyTable.DateStamp} <
> > Date(2000,6,17)

> > I don't like it, but it works.  I'd still like to figure out why the
other
> > stuff wasn't working.  I do the same type of logic when working with
date
> > ranges.



> > > Thanks, I tried:

> > > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID}
=
> 9
> > > and {MyTable.DateStamp} = Date(2000,6,16)

> > > I didn't get any matches, although I know that there are entries on
June
> > > 16th.

> > > Therefore, I tried:

> > > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID}
=
> 9
> > > and {MyTable.DateStamp} >= DateTime(2000,6,16,0,0,0) and
> > > {AppDetails.DateStamp} <= DateTime(2000,6,16,23,59,59)

> > > I got an error message "A date is required here" with the insertion
> point
> > > placed at the beginning of DateTime(2000,6,16,0,0,0).

> > > I tried:

> > > ({AppDetails.Statusl} = "A" or {AppDetails.Statusl} = "a") and
> > > {AppDetails.ID} = 9 and {AppDetails.DateStamp} In
> > DateTime(2000,6,12,0,0,0)
> > > and {AppDetails.DateStamp} To DateTime(2000,6,16,23,59,59)

> > > I got a message "A date range is required here" with the insertion
point
> > at
> > > the beginning of "DateTime(2000,6,12,0,0,0) ".

> > > MyTable is in a MS SQL Server database.  I do not have SQL Server on
my
> > > computer, but I do have Access 97.  So I added the table to an Access
> > > database using Get External Data > Link Tables and then look at
MyTable
> in
> > > design view; it says that DateStamp is a Date/Time field.

> > > In addition, I see the following entries in the DateStamp field:

> > > 3/16/98 4:35:00 PM

> > > 3/31/99 11:12:00 AM

> > > Does Crystal have some problem with the way that SQL Server stores
> dates,
> > > perhaps?

> > > I tried the following, just out of curiousity with the same error
> message.

> > > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and {MyTable.ID}
=
> 9
> > > and {MyTable.DateStamp} = DateTime(2000,6,16,6,0,0)



> > > > Hi Eric, is MyTable.DateStamp a Date field or a DateTime field?  If
it
> > is
> > > a
> > > > Date field try using:

> > > >     ...
> > > >     and {MyTable.DateStamp} = Date(2000,6,16)

> > > > If it is a DateTime field, try using:

> > > >     ...
> > > >     and {MyTable.DateStamp} In DateTime(2000,6,16,0,0,0) To

> > DateTime(2000,6,16,23,59,59)

> > > > HTH, Paul
> > > > --
> > > > Paul Little, Senior Programmer/Analyst
> > > > SVi Retail Systems
> > > > San Diego, CA



> > > > > Hello all,

> > > > > I'm having problems using the following record selection formula:

> > > > > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and
> {MyTable.ID}
> > =
> > > 9
> > > > > and {MyTable.DateStamp} >= Date(2000,6,16) and {MyTable.DateStamp}
> <=
> > > > > Date(2000,6,16)

> > > > > It returns no records, if I change it to:

> > > > > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and
> {MyTable.ID}
> > =
> > > 9
> > > > > and {MyTable.DateStamp} >= Date(2000,6,15) and {MyTable.DateStamp}
> <=
> > > > > Date(2000,6,15)

> > > > > It also returns no records.

> > > > > If I change it to:

> > > > > ({MyTable.Statusl} = "A" or {MyTable.Statusl} = "a") and
> {MyTable.ID}
> > =
> > > 9
> > > > > and {MyTable.DateStamp} >= Date(2000,6,15) and {MyTable.DateStamp}
> <=
> > > > > Date(2000,6,16)

> > > > > It returns 45 records.  I don't understand why I don't see some
> > records
> > > > when
> > > > > I just check for one of these days.



Sun, 15 Dec 2002 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Crystal Reports 8.5 date range parameter field - record selection that includes null date range

2. VB record selection by date

3. passing date to record selection formula

4. Problem passing date in selection formula

5. Problem applying record selection formula

6. Problem with selection of records, Help

7. Problem w/ Record Selection formula

8. Date problem and how to mark records

9. PROBLEM - Selecting records based on Date Range

10. Problem inserting record with date field

11. Problem accessing record from SQL Data base Date/Time Field

12. Problem inserting record with date field

 

 
Powered by phpBB® Forum Software