New SQL software, new string, need to group 1

[BHERNANDEZ]

I am using CR 7 with a SQL database. MY old program was Btreive and had separate fields for customer orders for the CITY, STATE, and ZIP. The new SQL program has combined the three into one field called {Addr Line 3}. I have a report that selects only certain states. How can I group these states? My selection criteria looks like this:

{OEHDRHST_SQL.ship_to_addr_3} like ["*** AK ***", "*** AL ***", "*** AR ***"]

If I need to explain a little more, let me know. I tried this formula but receive the message that I need a field name between the paren ( and {oe}. I have tried various field names and I then receive the error that I can not group on a condition. I am sure this whole formula is not what I am looking for anyway. Help!!!

WHILEPRINTINGRECORDS;
STRINGVAR STATE := {OEHDRHST_SQL.ship_to_addr_3};

groupname ({OEHDRHST_SQL.ship_to_addr_3} like ["*** AK ***", "*** AL ***", "*** AR ***"])


BH

 

[MColeman]

You could create a formula called state and put it in the detail section:

If {OEHDRHST_SQL.ship_to_addr_3} like "*** AK ***" then "AK" else
If {OEHDRHST_SQL.ship_to_addr_3} like "*** AL ***"
then "AL" else
If {OEHDRHST_SQL.ship_to_addr_3} like "*** AR ***"

Then use the state formula to group on.

 

[BHERNANDEZ]

Thanks! All I needed was that and now everything is flooding back to memory.

B

 

[synapsevampire]

You might be better served to explicitly locate data, in either case, one asterisk will suffice.

And swat the dba around for joining these fields into one, that's a very poor design and will always cause problems going forward.

-k