Parameter Range Problem

[ncchish]

I'm using CE11. I have a parameter in my record selection.

(

If Minimum({?AgencyRange}) = "All"

Then True

Else {DB.AGY_AGCY_NBR} = {?AgencyRange}

)

The field on the database is string and has the numbers 112ZZ, 112TT, 11211, etc. When I run the report
it doesn't pick up the characters only the numbers like 11201 to 11299, etc. If I put in 11200 to
112ZZ then I get no records. Any help is appreciated.

Thanks

 

[lbass]

Would you only be trying to select all values that have the same three starting numbers? If so, you could use:

left({DB.AGY_AGCY_NBR}) = {?Agency}

...without using a range parameter. Better yet, use a SQL expression:

[{%first3}]
{fn left(DB.AGY_AGCY_NBR)}

Then use a record selection formula of:

{%first3} = {?Agency}

If this is not the case, you might need to do something like:

{%first3} = {?first3} and
{%last2} in {?last2}

...where {?last2} is set for multiple range values which can be entered separately for letters and numbers, e.g., "AA" to "CC" or "11" to "99".

-LB

 

[ncchish]

Thanks, LB. However, they want us to use only 1 parameter for the range. They can choose any range of numbers. For example, they can enter 13601 to 13655 or 11800 to 118TT, etc.

 

[lbass]

What values do "they" think are included in the range 11800 to 118TT?

-LB

 

[ncchish]

Any number that falls within that range. It can be any combination of numbers/letters that fall between 11801, 11802...11899, 118AA, 118BB,...118ZZ, etc. Thanks.

 

[kskid]

I may be out of line but I usually code mine in the following manner

I set my parameter default value to "ALL"

and my formula to

(
If minimum({?AgencyRange})) = "ALL"
Then True
Else {DB.AGY_AGCY_NBR} in {?AgencyRange}
)

 

[kskid]

Forgot one important item

(
If minimum({?AgencyRange})) = "ALL"
Then True
Else {DB.AGY_AGCY_NBR} in uppercase({?AgencyRange})
)

I translate all user input to uppercase for consistency.

 

[ncchish]

It flags {?AgencyRange} with the error "A string is required here". It's set up as a string. Any ideas?

 

[lbass]

There is an extra paren in the formula. I just tested your original formula and it worked perfectly, picking up the correct values--both numeric and alpha, so I'm wondering what we are missing. I think you need to check the variation in your field. Can this field ever be null? Is kskid onto something and the agency number can sometimes be in lower case?

-LB

 

[ncchish]

It flags the second instance of ?AgencyRange with the string error. The default is "All" or the range entered and on the database it's all caps. I'm not sure why I'm getting the string error.

(
If minimum({?AgencyRange}) = "ALL"
Then True
Else {DB.AGY_AGCY_NBR} in uppercase({?AgencyRange})
)

 

[lbass]

Remove the uppercase and you can use = instead of "in" which takes you back to your original formula. You can't have the uppercase, because the parameter is referring to an array, not one string value.

-LB

 

[ncchish]

That takes me back to no records if I enter a range containing an alpha character. I get records with this if I only enter numeric. Thanks for trying.

 

[lbass]

There is something you are not telling us, since it worked for me when I tested it. Again, can the field be null or contain some other characters? I think you need to check the variation in the field.

-LB