DateTime Record Selection

[suechamps]

I am trying to select records based on a date & time range:

The entire record selection formula:
{IP_ACCOUNT.REFERRAL1} = "Bank" and
{ACCOUNT.TYPE} in ["Sold", "Sold DDOS", "Sold Pending"]and
({HISTORY.MODIFYDATE}>=DATETIME(2005,07,01,10,00,00) and
{HISTORY.MODIFYDATE}<DATETIME(2005,07,08,10,15,00))and
{HISTORY.USERNAME} in ["BA", "LS", "PB, "VC"]

The records where the modify date is greater than 7/1/05 10:00:00 AM are selected. However, no records that are less than 7/8/2005 10:15:00 AM are selected. There are 8 records that are less than this date and I am not understanding why they are not being selected.

This seemed like a simple record selection formula and I'm obviously missing something...

Thanks,
Sue

 

[Madawc]

Try commenting out the selection and displaying the data, just to check if something isn't what you expect. Nulls are a common trap, though probably not in your case.

If that fails, try putting the individual selection commands in formula fields of their own, such as {HISTORY.MODIFYDATE}<DATETIME(2005,07,08,10,15,00). Add an extra detail line and place it there, to see if it displays 'true' or 'false'.

Madawc Williams (East Anglia, UK)

 

[synapsevampire]

Your post doesn't make sense:

"e modify date is greater than 7/1/05 10:00:00 AM are selected. However, no records that are less than 7/8/2005 10:15:00 AM are selected. "

If it's getting rows that are greater than 7/1, than it's getting rows less that 7/8.

Try posting technical information:

Crystal version
Database/connectivity
Example data
Expected output

You also might simplify your life and create a datetime range parameter to use in lieu of hardcoding the dates, then use:

(
{IP_ACCOUNT.REFERRAL1} = "Bank"
)
and
(
{ACCOUNT.TYPE} in ["Sold", "Sold DDOS", "Sold Pending"]
)
and
(
{HISTORY.MODIFYDATE} in {?MyDatetimeRangeParm}
)
and
(
{HISTORY.USERNAME} in ["BA", "LS", "PB, "VC"]
)

Use the Databsae->Show SQL Query to see what is being passed to the database, which you can then copy and paste for use in other query tools to help with the discovery process.

As Madawc indicated, I think that you misunderstand the data and that the rows are being returned acording to the rules.

-k

 

[suechamps]

I'm sorry if I did not explain this well.

Let me try this again:
I'm using Crystal 8.5
I believe the answer to the Database/Connectivity question is ODBC

What I'm trying to accomplish is the following:

A report listing accounts that were modified during a specific time frame. This time frame will change each week. I have not yet used a parameter field because I'm trying to get the report to work the long way first.

My report fields are:
{ACCOUNT.ACCOUNTID}
{ACCOUNT.CREATEDATE}
{IP_ACCOUNT.REFERRALDATE}
{SECCODE.SECCODEDESC}
{USERINFO.LASTNAME}
{ACCOUNT.TYPE}
{IP_ACCOUNT.PAYON}
{IP_ACCOUNT.CUSTNUM}
{IP_ACCOUNT.DATE1}
{HISTORY.MODIFYDATE}

I want the result of my record selection to give me all records that were modified between 7/1/05 at 10:00 AM and 7/8/05 at 10:15 AM by a specific group of users.

If I set my record selection to only
{HISTORY.MODIFYDATE}>=DATETIME(2005,07,01,10,00,00) the data returned is as expected - from 7/1/05 10:00 AM to current day/time. However, if I change the record selection to {HISTORY.MODIFYDATE}>=DATETIME(2005,07,01,10,00,00) and {HISTORY.MODIFYDATE}<DATETIME(2005,07,08,10,15,00) the data returned is not as expected - from 7/1/05 10:00 AM to 7/7/05 end of day. Nothing from 7/8 prior to 10:15 AM is included and there are 8 accounts with modified date/time of 7/8 8:15AM to 9:13AM.

There are no null values in the data I'm trying to pull - I have verified that by looking at the database itself.

As Madawc suggested, I placed a formula field: {HISTORY.MODIFYDATE}<DATETIME(2005,07,08,10,15,00)into the detail of the report to see if I got a TRUE or FALSE result. When the selection is set only to {HISTORY.MODIFYDATE}>=DATETIME(2005,07,01,10,00,00) TRUE is returned for those modified dates that are less than 7/8/2005 at 10:15AM. This leaves me a bit perplexed as to why a selection formula using both parameters is not returning the expected result.


I think that in this instance I do understand the data though I am fairly new to Crystal. I have basically had to teach myself so a site like this is a Godsend!

Thanks for the help (& patience)
Sue

 

[synapsevampire]

ODBC is a type of connectivity, not a type of databsae.

Perhaps your modifydate doesn't have a timestamp? Some databases have a unique field for time. Right click the field and select browse data, it will show you the data type, and it shoudl read datetime, not date.

Use the Database->Show SQL Query to determine exactly what Crystal is passing to the database, and post it here.

That sounds like the culprit to me, but the show sql will help us to identify the issue.

-k

 

[suechamps]

The field's data type is DateTime.

Here's the Show SQL Query:

SELECT
ACCOUNT."ACCOUNTID",
ACCOUNT."TYPE",
ACCOUNT."ACCOUNT",
ACCOUNT."CREATEDATE",
IP_ACCOUNT."REFERRAL1",
IP_ACCOUNT."REFERRALDATE",
IP_ACCOUNT."PAYON",
IP_ACCOUNT."CUSTNUM",
IP_ACCOUNT."DATE1",
SECCODE."SECCODEDESC",
USERINFO."LASTNAME",
USERINFO."FIRSTNAME",
HISTORY."USERNAME",
HISTORY."MODIFYDATE"
FROM
(((sysdba.ACCOUNT ACCOUNT INNER JOIN sysdba.IP_ACCOUNT IP_ACCOUNT ON
ACCOUNT."ACCOUNTID" = IP_ACCOUNT."ACCOUNTID")
INNER JOIN sysdba.SECCODE SECCODE ON
ACCOUNT."SECCODEID" = SECCODE."SECCODEID")
INNER JOIN sysdba.USERINFO USERINFO ON
ACCOUNT."ACCOUNTMANAGERID" = USERINFO."USERID")
INNER JOIN sysdba.HISTORY HISTORY ON
ACCOUNT."ACCOUNTID" = HISTORY."ACCOUNTID"
WHERE
HISTORY."MODIFYDATE" >= {ts '2005-07-01 10:00:00.000'} AND
HISTORY."MODIFYDATE" < {ts '2005-07-08 10:15:00.000'} AND
IP_ACCOUNT."REFERRAL1" = 'Fleet' AND
(ACCOUNT."TYPE" = 'Sold Pending' OR
ACCOUNT."TYPE" = 'Sold DDOS' OR
ACCOUNT."TYPE" = 'Sold') AND
(HISTORY."USERNAME" = 'VC' OR
HISTORY."USERNAME" = 'PB' OR
HISTORY."USERNAME" = 'LS' OR
HISTORY."USERNAME" = 'BA')
ORDER BY
ACCOUNT."ACCOUNTID" ASC



Thanks!
Sue

 

[synapsevampire]

You might also try changing your joins to LEFT OUTER joins, as it may not be returning these rows because it can't find links to the other tables.

If this fails, try the following:

As you can see, the query being passed does show the criteria you want, so I'm not sure why you aren't receiving the rows you expect.

Eliminate the other criteria from the Report->Edit Selection Formula->Record to only reflect the dates and see what you get.

If you start seeing the rows you expect, then you'll know that some other criteria in the filter is removing them, so add them in until you find the offending criteria.

-k

 

[suechamps]

Here's the update:

I removed all criteria from the Report->Edit Selection Formula and changed the selection from just < to <= to see if anything pulled:
{HISTORY.MODIFYDATE}<= DATETIME(2005,07,08,10,15,00)
and again nothing with a modified date prior to 10:15 am on 7/8/2005 was returned.

I altered the joins in both my database and in my crystal to Left Outer joins but with no change in the results. Though if this were the cause of the problem, wouldn't the first date parameter not pull correctly?

I then changed the Report Options->Convert Date-Time Field to "To Date" and tried running the report again using the same criteria:
{HISTORY.MODIFYDATE}<= DATE(2005,07,08)

This time I got the expected results - accounts with a modifiy date of 7/8/2005 were returned. This leads me to the conclusion that the time criteria is the culprit, yet I have no clue as to how or why??

If you have any suggestions, please post. Otherwise, thanks so much for all your help with this issue.

Sue

 

[MrBillSC]

Just for the heck of it try {HISTORY.MODIFYDATE}<=DATETIME(2005,07,08,10,14,59).
Since the time portion of the data seems to be the problem, maybe the "=" will help.
MrBill

 

[synapsevampire]

reverse the "I then changed the Report Options->Convert Date-Time Field to "To Date" " and see what happens.

It may be that you are returning rows, but they aren't for the time you think.

A simpler check would be to increase the date by one day and see what is returned and look at the time stamps.

-k

 

[suechamps]

I tried the suggestions of both MrBillSC & synapsevampire...but with no luck.

I going to try to rethink the report to see if I can somehow pull the data another way...

Just wanted to thank you both for trying to help me!

Sue

 

[synapsevampire]

If you want help, please post specifics.

Stating I tried...it didn't help... doesn't show what you tried nor where.

Did you do this "A simpler check would be to increase the date by one day and see what is returned and look at the time stamps."

As you must realize, people constantly use this functionality, so it's likely not an error with Crystal, more so your code or your understanding of the database.

If you respond to questions asked, such as the database type, and whether you've tried certain things, then someone will help you.

-k

 

[suechamps]

In answer to SV's question...yes I did try increasing the day by one day and the data returned included the date range that I am looking for. So I am at a loss because I do not understand why {HISTORY.MODIFYDATE}>=DATETIMEVALUE(2005,07,01,10,00,00) returns the expected result but
{HISTORY.MODIFYDATE}<DATETIMEVALUE(2005,07,08,10,15,00) does not. The database that I'm using is Saleslogix (and I'm not even sure if that's the correct answer!)

As stated in an earlier post, I am fairly new at Crystal (using 8.5), have had to learn most of what I know on my own, and am very new to this site. Keeping that in mind, I do not always know the correct answer to some questions. I am trying my best to post as much information as possible so that others may offer their help.

My apologies and thanks to those that have tried to help...

Sue

 

[synapsevampire]

Since the Show SQL Qeury is correct, it could be that the database or connectivity is faulty.

There is an integrity check for Saleslogix, though it takes a loooong time to run in it's entirety, you might give this a whirl as everything else seesm fine.

-k