A Number, currency etc. error with range or multiple value parameter

[swabunn]

crystal 9:00
sql server 2003

I am getting 'A number,currency amount, date, time, date-time or string required' when I use range or multiple value in {?Location} parameter

My record selection is as below:

{IV00102.LOCNCODE} = {?Location} and
{?Location} in [{IV30300.TRXLOCTN},{IV30300.TRNSTLOC}]


Thanks

-swabunn

 

[kchaudhry]

What are your data types for the following?

{IV00102.LOCNCODE}
{?Location}
{IV30300.TRXLOCTN}
{IV30300.TRNSTLOC}


Kchaudhry

 

[swabunn]

datatype is string

Thx

 

[swabunn]

datatype: = char

 

[lbass]

I think the problem is because of the second clause, where you might have a range being compared with two values. Please show samples of each field and explain what you are trying to do by allowing multiple values/ranges.

-LB

 

[swabunn]

{IV00102.LOCNCODE}
{?Location}
{IV30300.TRXLOCTN}
{IV30300.TRNSTLOC}


{?Location} would pick out of 9 locations from site set up table i.e {IV00102.LOCNCODE}. These locations are existed in both of these fields cloumn {IV30300.TRXLOCTN},{IV30300.TRNSTLOC} of iv30300 table.

I am grouping the location field i.e {IV00102.LOCNCODE}. There are some transfer transactions for example from location a to location b. location a field is {IV30300.TRXLOCTN} and location b field is {IV30300.TRNSTLOC} in IV30300 Table.

I want to see (-) quantity in location 'a' and (+) quantity in location b.

Thanks

-swabunn

 

[lbass]

By samples, I meant instances of the fields, not the field names.

-LB

 

[elsenorjose]

You might need to use parentheses around the field names in your 'IN' clause. However, I don't think that's your problem. Using 'IN' typically requires the use of a list of literals, you can't refer to field names in an 'IN' statement. You would literally need to include all possible values in those fields:

{IV00102.LOCNCODE} = {?Location} and
{?Location} in (['location1', 'location2', 'location3', 'location4', etc...])

 

[swabunn]

{IV00102.LOCNCODE},{IV30300.TRXLOCTN},{IV30300.TRNSTLOC}
all these three fields contian nine locations i.e

VAN
TOR
MON
ETO
CAL
WINN
DART
EDM
PHO

 

[elsenorjose]

Did you try using the example I posted? I don't think you can use a field name in an 'IN' statement, you must specify the text you are looking for.

 

[lbass]

I think we are all confused because we don't understand what you are trying to do. You seem to want to look at quantities based on matching all three locations, but you talk about transfer between locations. Can you explain whether just one or both fields from the second table should match the field from the first table? Also, it would help to see some sample data.

Are you really trying to use a range parameter, e.g., all values between "E" and "K", or you do you really mean just to use multiple values?

-LB