incorrect data being returned

[SeeBee]

Still have problems with a record from year 2001 being returned in my report.
I'm using a PC-style database ('tents') but manually linking the JobNo with Job Number. When I looked at the Links Option dialog box the Equal Join was grayed,indicating a left join.
But as suggested I went into the formula editor and typed in {Job Detail (History).JobNo} = {Job History.Job Number}
Here is how my codes appears now:

{Job Detail (History).JobNo}={Job History.Job Number} and
{Job Detail (History).Operation} = 301.00 and
{Job History.Ship Via} = "P" and
{Job History.Customer Number} <> &quot;&quot; and
{Job History.Close Date} in date(2002, 02, 01) to date(2002, 02, 28) and
{Job Detail (History).Cost Center} = 13.00

There are no date fields I can link the two tables to either - JobNO & Job Number is it. Have even tried keeping all the table types together in the formula, and that doesn't make any difference - {Job Detail (History). ?????}.
Can you think of anything else to try? Thanks in advance.

 

[dgillz]

Of coursse you ar having problem with the year 2001 appearing, becasue you line of code:

{Job History.Close Date} in date(2002, 02, 01) to date(2002, 02, 28)

will EXCLUDE ANY record with a year 2001 date.

What are you trying to accomplish, and then lets write the record selection formula from there. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[kenhamady]

It sounds like the record is appearing and it shouldn't be.
You are sure that this 2001 date is a CloseDate? If a record was opened in 2001 and closed in 2002 it should show up. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SeeBee]

I know this sounds complicated, but I'll try my best to explain.
Job Numbers begin each January with number one - Job Number 138045 was completed in year 2000, 2001, and 2002. Year 2001 the task of Operation 301 was completed (which is identified above in my formula), but NOT in Feb2002.
This job (number) was completed and has a Close Date 2-28-2002.
Two reasons why this Job Number should not be returning with the data is because of the End Date being 5-01-2001 and the fact that the Job Number that was closed 2-28-02 did not have the Operation 301 completed. I don't know why it is showing a Close Date of 2-28-02 with this operation 301. (Case of right operation & wrong year or right year & wrong operation!)
I thought maybe it was a fluke, so I attempted to run the March02 report and again there was a record return that should not have been.
How can I be more specific about this Operation 301 must be in between the date range of 2002, 02, 01 to 2002, 02, 28?
Hope this helps, if not I'll try a different approach.
Again, thanks in advance for the help.
SeeBee

 

[kenhamady]

Sounds like completing and closing are different, and you may have separate dates stored for each. I would guess that for one JOB you might &quot;complete&quot; several tasks or operations. Eventually you close the job. Hard to say without studying the system but you have to make sure you are looking at the correct field.

One way to trouble shoot is to select all records for one JOB and list all of the possible fields on the report to see what they contain. By comparing to the screens you may be able to figure out what is going on. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com