Another formula problem

[grierfield]

I am trying to select {bl_exp.PERC_INJ} = “X” for {bl_exp.DEVICE}. there are 46 devices, I only want device 5,6,7,8 for the dates shown. Some how the report is picking up dates earlier than I am specifying. here is formula I am using:

{bl_exp.PERC_INJ} = "X" and
{bl_exp.D_EXP} > Date (1998, 10, 31) and {bl_exp.DEVICE} ="5" or bl_exp.DEVICE}="6"
or bl_exp.DEVICE}="7" or bl_exp.DEVICE}="8"

 

[Naith]

You need to include some parentheses here.

At the moment, any database will interpret your selection criteria as:

(
{bl_exp.PERC_INJ} = "X" and
{bl_exp.D_EXP} > Date (1998, 10, 31) and {bl_exp.DEVICE}="5"
)
(
OR bl_exp.DEVICE}="6"
)
(
OR bl_exp.DEVICE}="7"
)
(
OR bl_exp.DEVICE}="8"
)

which, I guess, is not what you want.

Try specifying your own parentheses, or better still go for something like this:

{bl_exp.PERC_INJ} = "X"
and {bl_exp.D_EXP} > Date (1998, 10, 31)
and {bl_exp.DEVICE} in ["5","6","7","8"]

All the best,

Naith

 

[grierfield]

Naith, I like your suggestion, however it did not work, but I am going to play with the concept and see what happens. Thanks

 

[Naith]

When you say it did not work, can you illustrate how it failed?

 

[grierfield]

Naith, I copied the formula exactly as u typed it and pasted it. There were no errors but no records were selected and I know for a fact that several meeting the criterias exists in the Database.

 

[Madawc]

If the formula is correct, then maybe the data isn't quite what you're expecting. Try removing some of the selections and finding out just what is there.

Madawc Williams
East Anglia, Great Britain

 

[bburnell]

Try this:

({bl_exp.PERC_INJ} = "X&quot and
({bl_exp.D_EXP} > Date (1998, 10, 31)) and
({bl_exp.DEVICE} ="5" or {bl_exp.DEVICE}="6" or {bl_exp.DEVICE}="7" or {bl_exp.DEVICE}="8&quot

Brett (smsforce)

--------------------------
Please visit my website!

http://www.smsforce.com

 

[grierfield]

{bl_exp.PERC_INJ} = "X" and
{bl_exp.D_EXP} > Date (1998, 10, 31)
The above formula segment works fine it pulls up the 870 records that meets those criteria. Now there is a column {Bl_exp.Device} that holds the device #s that causes the injuries devices numbered 1 thru 46. These all show up for each of the records shown above. It’s only when I say give me the records for device 5 or 6 or 7 that I get no records and I know that there are records with those device numbers.
One strange phenomenon is that the program (Crystal Report) strips the parenthesis from the formula and also rearranges the lines of codes in the formula. Strange?
And Bret I copied & pasted your sample, there were no error but i got zero record selections

 

[bburnell]

Can you copy and paste exactly what formula you are using? That seems strange that it stripped the parenthesis and rearranged the lines of code. Your problem is definitely with the parenthesis since it only sees the "or" conditions by themselves w/o the X and date formulas.
Brett (smsforce)

--------------------------
Please visit my website!

http://www.smsforce.com

 

[lbass]

What does "that holds the device #s that causes the injuries devices numbered 1 thru 46" mean? Does the column {Bl_exp.Device} show "5","6", or "7" , etc. when placed in the details? If so, Naith's suggestion is the appropriate syntax:

{bl_exp.PERC_INJ} = "X"
and {bl_exp.D_EXP} > Date (1998, 10, 31)
and {bl_exp.DEVICE} in ["5","6","7","8"]

Is this the one you tried? I think you should try it again.

Can you also verify that the results for {bl_exp.DEVICE} is a single integer and not a string of numbers like "5, 7, 9"? Maybe you should show a sample of the report before selecting the device #, so we can see what's going on.

-LB

 

[bburnell]

"What does "that holds the device #s that causes the injuries devices numbered 1 thru 46" mean?"

- I think LB is on the right track with this. Maybe the DEVICE field has a space in it that you are not seeing. A trim() command should help that out. To test this you could create a formula like this and put it in the detail line:
"*" + {bl_exp.DEVICE} + "*"

If anything other than *5* etc... shows up, you'll know what you're up against.

Hope that helps!
Brett (smsforce)

--------------------------
Please visit my website!

http://www.smsforce.com

 

[grierfield]

"*" + {bl_exp.DEVICE} + "*"

LB, this little formula did it for me, it showed that the Device field was a 3 digit field so once I changed my formula to reflect this all of u guys formulas worked. Thanks again very much.

 

[bburnell]

Glad to help!
Brett (smsforce)

--------------------------
Please visit my website!

http://www.smsforce.com