VB Crystal Report, record selection date problems

record selection date problems

Author	Message
Eric Gofort #1 / 8	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

Paul Littl #2 / 8	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

#3 / 8	record selection date problems
Fri, 19 Jun 1992 00:00:00 GMT

Eric Gofort #4 / 8	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

Eric Gofort #5 / 8	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) - Hide quoted text - - 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) - Hide quoted text - - 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

Paul Littl #6 / 8	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

#7 / 8	record selection date problems
Fri, 19 Jun 1992 00:00:00 GMT

Eric Gofort #8 / 8	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

Page 1 of 1

[ 8 post ]