Help with record selection of a payroll report

[agreen10]

The following is the record selection formula:
{students.application_status} in ["Active", "Approved", "Withdrawn"] and
{students.year} = 2005 and
{students.semester} = "School Year" and
({bssp_tuition.apr_check}= {?CheckNo.};
{bssp_tuition.aug_check} = {?CheckNo.};
{bssp_tuition.dec_check} = {?CheckNo.};
{bssp_tuition.feb_check} = {?CheckNo.};
{bssp_tuition.jan_check} = {?CheckNo.};
{bssp_tuition.mar_check} = {?CheckNo.};
{bssp_tuition.may_check} = {?CheckNo.};
{bssp_tuition.nov_check} = {?CheckNo.};
{bssp_tuition.oct_check} = {?CheckNo.};
{bssp_tuition.sep_check} = {?CheckNo.}) and
{@join two fields} and
{sites.program_id} = "1" and
{students.program_id} = 1

I have a report in crystal 10 that I need to only display records based on a certain check number. the check number has to look up all the fields above and if it finds a match, then it will print the details for that record. However, when I enter a check number in the parameter field, I get the check number and others. What am I doing wrong. for example, if I enter check number 6855, it gaves records for 6855 6855 and 9999. which is not correct.

Thanks for your help in advance.

 

[lbass]

Try:

{students.application_status} in ["Active", "Approved", "Withdrawn"] and
{students.year} = 2005 and
{students.semester} = "School Year" and
(
{?CheckNo.} in [{bssp_tuition.apr_check},bssp_tuition.aug_check},
{bssp_tuition.dec_check},{bssp_tuition.feb_check},
{bssp_tuition.jan_check},{bssp_tuition.mar_check},
{bssp_tuition.may_check},{bssp_tuition.nov_check},
{bssp_tuition.oct_check},{bssp_tuition.sep_check}]
) and
{@join two fields} and
{sites.program_id} = "1" and
{students.program_id} = 1

-LB

 

[agreen10]

It does not like that parameter field being there as it is giving me a data type error. the field is in face a number but it is not recognizing it. Any other ideas!

The crazy thing about the database design is that each month check number field is stored seperately hence those monthly field names. so what I am trying to do is look up those fields using the parameter field of checkno. and if there is a match, then display them.

Any other suggestions.

 

[lbass]

Please verify the datatypes of both the parameter and the check months, by letting your mouse float over the fields in the report.

-LB

 

[agreen10]

the check months are strings and the parameter is a string also.

 

[synapsevampire]

Post the actual error message.

Right click the field and select browse data, it will tell you what crystal is interpreting it as.

LB's formula makes sense, so it's probably that you misinterpret what crystal believes the data type to be.

And what is this supposed to do?:

and
{@join two fields} and

-k

 

[agreen10]

I cannot right click on the {?checkno.} field as it is not returning any data. It is returning an error message

" A number, currency amount, date, time, date-time, or string is required here" and it highlights the {?Checkno.} field in the formula editor.

When I created the checkno paramaeter, I created it as a string as the months fields are in fact strings.

I really appreciate this assistance and hope we can get it resolved.

 

[synapsevampire]

I didn't ask you to right click on a parameter, I asked you to right click on the field. So since {bssp_tuition.apr_check} is the unknonw, try right clicking it...

-k

 

[agreen10]

I answered above and said it is a STRING.

 

[lbass]

If both the parameter and the fields are strings, there is no reason my earlier suggestion would not work, that I am aware of. Is your parameter really named {?Checkno.} with a period? You should be adding fields and parameters by double-clicking on them, not typing them in.

Please copy the exact formula you are now using into the thread.

-LB

 

[agreen10]

{students.application_status} in ["Active", "Approved", "Withdrawn"] and
{students.year} = 2005 and
{students.semester} = "School Year" and
(
{?CheckNo} in [{bssp_tuition.apr_check}, {bssp_tuition.aug_check},
{bssp_tuition.dec_check},{bssp_tuition.feb_check},
{bssp_tuition.jan_check},{bssp_tuition.mar_check},
{bssp_tuition.may_check},{bssp_tuition.nov_check},
{bssp_tuition.oct_check},{bssp_tuition.sep_check}]
) and
{@join two fields} and
{sites.program_id} = "1" and
{students.program_id} = 1

I changed the name of the parameter field to exclude the period and still got the same.

Thanks

 

[synapsevampire]

Yeah, the formula says that it is NOT a string, hence I suggested that you right click it and see what Crystal said. You didn't state that you had checked what Crystal interpreted as by right clicking it and selecting browse.

Since you though I might have been suggesting you do so with a parameter, it seems clear that you don't understand Crystal well, and I suspect something isn't understood.

As LB stated, the formula was fine, this appears to be a user concern, and a frustrated user at that.

I also asked what this was:

{@join two fields}

Which you ignored as well.

Not answering questions, and getting upset because you don't understand something doesn't help.

-k

 

[agreen10]

Mr. K,

I did not get upset. No need to get an attitude with me please.

It is not a matter of understanding crystal well, It seems to be a communication issue. I am trying to explain exactly what I see and trying to answer your questions. Maybe what you explained was not clear.

{@join two fields} is not relevant as I took it out of the formula. it does not relate to the problem.

 

[synapsevampire]

I asked you to right click the field and state what Crystal says the data type is, this is several posts later and you simply stated in CAPS that it was a string.

Not how you arrived at this.

Reread the thread if you think that I've given YOUA attitude, you ignored questions.

Try simplifying:

(
{?CheckNo.} = {bssp_tuition.apr_check}
)
or
(
{?CheckNo.} = {bssp_tuition.aug_check},
)
or
... you get the idea

Nothing else in the formula.

-k

 

[agreen10]

Your suggestions worked to eliminate the error but I am back where I started.

I got results for check number 6350 but also got all the checked 9999. Not sure how crystal is interpreting that field data.

thanks K

 

[lbass]

Try laying the check month fields in the detail section without using a parameter for check number and then show us a sample of the results. Are there nulls for those months when there are no checks?

-LB

 

[synapsevampire]

Not sure what also the the checked 9999 means.

Crystal, as with any database tool, will return ALL rows which meet the criteria.

If one months checks have the one you want, yet anotehr month has 9999, you'll get that data.

The design of the table isn't ideal for this reason.

Anyway, please state specifics, forinstance what checked 9999 means.

Lay all of the fields out in the details and see what's returned.

If you want to show which month has the check number, create a formula to determine this, as in:

if {bssp_tuition.apr_check}= {?CheckNo.} then
"apr"
else
if {bssp_tuition.aug_check} = {?CheckNo.} then
Aug
etc...

If you need help displaying, you need to demonstrate what you're getting, and what you want.

-k

 

[agreen10]

Here is a sample of the data it gaves the name of the school, name of the student, the check number that I asked for the months that were paid with the checks and the amount that were paid each month. the months that did not match, were supressed.

I asked for check number 6855 but still the check nomber 9999 came up. That is a dummy number that was put in the field for lack of a check number.

I welcome any other assistance or ideas you may have.

Great Lakes Adventist Academy
6356 Nov $ 749.58 Joseph Rivera
6855 Dec $ 486.63
9999 Jan $ 594.83
6356 Oct $ 575.00 Lydia Weiso
6855 Dec $ 1,091.67
9999 Jan $ 516.66
6356 Sep $ 500.00 Adrian Willauer
6356 Nov $ 514.49
6855 Dec $ 434.79
9999 Jan $ 449.27
6356 Nov $ 1,039.86 Daphenie Wilson
6855 Dec $ 445.65

 

[lbass]

There is also another check number here: 6356. Why is it that multiple students are associated with the same check number?

If you had chosen only 6855, then only that would have appeared in the report. Please show a sample of data before applying the record selection criteria that shows the months going across and the check number field.

-LB

 

[agreen10]

here is a sample as you requested:

Ridge School
6767 Nov $ 593.13 Dominick Brumfield Blue Ridge School
6568 Sep $ 593.13 Jeremy Buckson Blue Ridge School
9999 Jan $ 738.11
6629 Sep $ 593.13 Luis Caro Blue Ridge School
6767 Nov $ 65.90
9999 Jan $ 1,370.78
6629 Sep $ 487.50 Gregory Davis Blue Ridge School
6767 Nov $ 328.09
9999 Jan $ 18.58
6629 Oct $ 487.50 Benjamin Herbert Blue Ridge School
9999 Jan $ 379.17
6629 Oct $ 593.13 Steven Lewis Blue Ridge School
9999 Jan $ 13.18
6629 Sep $ 593.13 Tony Lewis Blue Ridge School
6767 Nov $ 399.17
9999 Jan $ 1,100.55
6629 Sep $ 550.00 Christian Pagan Blue Ridge School
6767 Nov $ 370.15
9999 Jan $ 1,020.53
6568 Sep $ 593.12 Dominic Walker Blue Ridge School
6767 Nov $ 171.35
9999 Jan $ 355.88
Page 5 of 56 06-February-2006

no specific check number requested.