Selecting two or more from database 1

[Saint1234]

This could be a bit confusing, I have been trying to think of a way round this for ages, hope someone can help. Here goes. I have a db field called worktype1, the work type can be 1 off 10 items (e.g. gas, oil, sick, holiday). Now the report I have designed is done by workType (It selects all engineers with that work type). Problem is I need it to select all where workType is equal to Gas and Sick (more than 1 worktype). When I change the select expert the report appears blank
{Details.EngineerName} = {?Engineer Name} and
{Details.DateNotKeyword} = {?Date Parameter} and
{Details.WorkType1} = {?Work Type} and
{Details.WorkType1} = "sick"
I am wondering does anyone have any ideas of a way around this??? After this is solved my life in wok will be soooooo much better. Many Thanks in advance.

 

[Reebo99]

{Details.EngineerName} = {?Engineer Name} and
{Details.DateNotKeyword} = {?Date Parameter} and
{Details.WorkType1} in [{?Work Type}, "sick"]


Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[Saint1234]

Thanks for the post, when I try that I get the error, this array must be subscripted. For example: Array

 

[Reebo99]

Ok, I probably needed a bit more information as I was assuming a few things.

If you change the parameter {?Work Type} to allow multiple values, you can choose various workTypes then use the following in record selection formula :

{Details.EngineerName} = {?Engineer Name} and
{Details.DateNotKeyword} = {?Date Parameter} and
{Details.WorkType1} in {?Work Type}

If you want to select a WorkType AND have all sick in the results use :

{Details.EngineerName} = {?Engineer Name} and
{Details.DateNotKeyword} = {?Date Parameter} and
({Details.WorkType1} = {?Work Type} or
{Details.WorkType1} = "sick&quot

Let me know how you get on...

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[Saint1234]

Thanks reebo99, That is it working now. Super mate, thanks

 

[lbass]

Since "sick" and "gas" are from the same field {table.worktype1}, you get no results--they are never in the same record. I think what you really are trying to do is to identify engineers who have at least one record of each type. I would use the following record select:

{Details.EngineerName} = {?Engineer Name} and
{Details.DateNotKeyword} = {?Date Parameter} and
{Details.WorkType1} = {?Work Type}

Then create a formula:

if {Details.WorkType1} = "sick" then 1 else
if {Details.WorkType1} = "gas" then 1000 else 0

Group by {table.engineer} and then go to report->edit selection formula->GROUP and enter:

sum({Details.WorkType1},{table.engineer}) >= 1001 and
remainder(sum({Details.WorkType1},{table.engineer}), 1000) <> 0

This will select those engineers who have at least one record of each. It assumes that there are no more than 999 records of each type per engineer.

-LB

 

[lbass]

I guess I misinterpreted the problem.

-LB

 

[Reebo99]

No problem, and thanks for the star.

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.