crystal report selection on Date/Time

msmeth
04-20-2004, 06:00 AM
I'd like to pass a selection formula from VB6 to crystal reports to select those records that are before 6am on a certain date. Crystal generates a selection formula like this:

"{CoreINVY.Date Made} <= DateTime (2004, 04, 15, 00, 00, 00) and " & _
"{CoreINVY.Time Made} < DateTime (1899, 12, 30, 06, 00, 00)"

Do I need to do this the same way thru VB? I've tried the following, but it doesn't work:


"{CoreINVY.Date Made} <= '2004/04/14' and " & _
"{CoreINVY.Time Made} < 07:00:00)"


Why is Crystal using this DateTime function....and what's with the 1899? :confused:

MKoslof
04-20-2004, 06:31 AM
1899 is the default value being passed. Are you using Access..if so, the database engine is converting any null dates into this default. So, you can write a formula to handle this...(create a suppression formula and set the suppression of the field equal to true if your condition is met (isnull, equal to 1899, etc.).

Or, within the Database connection Properties, I believe there is an option for "setting default values to null" for the ODBC Access driver, you could check or uncheck this option.

For a DateTime comparison try this. The problem with Crystal is, it actually evaluates DateTime stamps as strings, so first, cast it into a string and then compare against a valid DateTime field on your report...like so:



Dim startDate as string
Dim strString as string

'let's just say it is a recordset value for now

StartDate = Cstr(rs.fields("StartDate").value)
strString = "{myTable.Date1} > DateTime('" & StartDate & "')"

Rept.RecordSelectionFormula = strString



Now you are comparing your Date1 field (a DateTime field) to the variable being passed.

msmeth
04-20-2004, 06:42 AM
For a DateTime comparison try this. The problem with Crystal is, it actually evaluates DateTime stamps as strings, so first, cast it into a string and then compare against a valid DateTime field on your report...like so:



Dim startDate as string
Dim strString as string

'let's just say it is a recordset value for now

StartDate = Cstr(rs.fields("StartDate").value)
strString = "{myTable.Date1} > DateTime('" & StartDate & "')"

Rept.RecordSelectionFormula = strString



Now you are comparing your Date1 field (a DateTime field) to the variable being passed.

Merci mucho ;) . That worked.

EDIT: Forgot abt the Time.....
Do I need to change this 1899 bit (yes, it is Access) or can I just leave it? I'm not sure what you mean by the database connection....do u mean through Crystal?

MKoslof
04-20-2004, 06:48 AM
Yes...I believe if you go to database properties within Crystal, you can hunt for this option and either check it or uncheck. I tend to just write formulas to suppress this problem. You don't have to change anything in your fields. Basically right click the date field in question, go to format and code a suppression formula. Then, if the date comes in null or as a date within 1899 suppress it, if not, leave it alone.

msmeth
04-20-2004, 06:55 AM
Yes...I believe if you go to database properties within Crystal, you can hunt for this option and either check it or uncheck. I tend to just write formulas to suppress this problem. You don't have to change anything in your fields. Basically right click the date field in question, go to format and code a suppression formula. Then, if the date comes in null or as a date within 1899 suppress it, if not, leave it alone.

Does this look right?
In crystal,

If Isnull({Tbl.Date}) or ( {Tbl.Date} >= Datetime(1899,01,31) and {Tbl.Date} <= Datetime(1899,12,31) ) then true

No errors were found, but the report still prints the same stuff cuz everything is valid I guess.

MKoslof
04-20-2004, 07:05 AM
Where are you putting this code? In the suppress field event?



If Isnull({Tbl.Date}) Then True

If {Tbl.Date} >= CDate(01/31/1899) And <= CDate(12/31/1899) Then True

msmeth
04-20-2004, 07:22 AM
Where are you putting this code? In the suppress field event?



If Isnull({Tbl.Date}) Then True

If {Tbl.Date} >= CDate(01/31/1899) And <= CDate(12/31/1899) Then True



Yes in the suppress field event. Can't you combine those two statements?

MKoslof
04-20-2004, 07:24 AM
You could..sure. I just like putting these condition on seperate lines..easier to read. You could do an ElseIf.

msmeth
04-20-2004, 07:31 AM
You could..sure. I just like putting these condition on seperate lines..easier to read. You could do an ElseIf.

k, it's good...thank you.

MKoslof
04-20-2004, 07:33 AM
Always glad to help. Good luck.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum