Need help on formula

[okuser]

I am a new user in CR8.5 and interfacing with an ACCESS database.

Problem:

I have a table that has a transaction description column. That column consists of the following descriptions:

Sick, Vacation, Bereavement, Jury duty, Dock, Comp time, Personal Bus.,Regular, Clock time, Clock Time or Clock-In,

Client wants to receive a report that would put in one field all of the Sick, Vacation, Bereavement, Jury duty, Dock, Comp time,Personal Bus. And then all the Regular, Clock time, Clock Time or Clock-In in another field.

For example if it were sick then the report would look like
......,sick," " (where ... is other info and the " " is a blank field)


if it were regular then the report would look like
......, " ", regular (where ... is other info and the " " is a blank field)

I need some suggestions as I have only been able to generate this report in 2 separate reports doing a record formula on the transaction description. Would like to be able to send one report.

Any advise would be helpful.

 

[synapsevampire]

Typed descriptions are generally poor way to describe requirements, try posting example data and expected output.

Why is there a blank field?

Your description is unique...I'll be curious what this means in terms of technical information.

-k

 

[okuser]

The following is an example of the format that the client sent to me showing me the fields and the way the information needs to be layed out in order to import into their system.

Name,Employee ID,Location Code,Location Name,Department Code,Department Name,Date Worked/Absent,Number of Hours,Absence Type,Work Type

"Biglow, Troy","00063791","00000005","Windsor Hills Elementary","00000001","Janitorial","09/23/03","04:00","","Regular"

"Biglow, Troy","00063791","00000005","Windsor Hills Elementary","00000001","Janitorial","09/24/03","04:00","Sick",""

I have been able to acheive all of this except for the fact that the last two items, Absence Type and Work Type are stored in one column in Access as TransDescription. So, I need to "filter" out Absence versus Worked. Hope this explains in better detail.

 

[Grelber]

I am going to take a stab at this....ohh why not.

Try making a couple of Formula Fields:
#1 AbsenceType

If {TransDescription} in [ "Sick", "Vacation", "Jury Duty"...] Then {TransDescription} Else ""

#2 WorkType

If {TransDescription} in [ "Regular", "Clock Time", "Clock In".....] Then {TransDescription} Else ""

Put the formula fields in report in place of table fields.

Cross your fingers.

-G

 

[okuser]

Thanks Grelber, it worked! Like I said, I am a new user and have not had much experience yet with formulas. Thanks for the help!