Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Selection Formula Post Reply Post New Topic
Author Message
Bryan
Newbie
Newbie
Avatar

Joined: 14 Aug 2008
Location: United States
Online Status: Offline
Posts: 10
Quote Bryan Replybullet Topic: Selection Formula
     Posted: 05 Sep 2008 at 12:34pm
Hi,
 

  Still new to this but everything that you guys have given me has worked great. I am now looking for a formula to select certain item numbers from our item table. Below is some sample data. What I am trying to get is a list of only barstools, which start with a unique 3 digit number xxx, and end in either 040 or 041. eg... 323-040 and 323-041 these are the same group name one has a leather seat and one has a fabric seat. Any ideas? Thanks Here is some sample data of some items in the list there are approx 14,700 items in the list

 

 

{IMINVLOC_SQL.item_no}

 

*DONATION

*FLOOR

*REBATE

021-148-10

104-220-24

104-040          This is one I need to select

104-041          This is one I need to select

106-551

11111-MISC

114-021-11 (1)

326-959

323-040
323-041
 

 

There is also a description column in the table and there is a B/S in every item that I need to show so I could also filter it that way if need be. Thanks again

 

 


Edited by Bryan - 05 Sep 2008 at 8:35pm
IP IP Logged
ezeney
Newbie
Newbie
Avatar

Joined: 05 Sep 2008
Location: United States
Online Status: Offline
Posts: 14
Quote ezeney Replybullet Posted: 05 Sep 2008 at 4:31pm
I'm not sure what you're looking for but if I am thinking what you thought, use either one of the following two in your WHERE clause.
 
{IMINVLOC_SQL.item_no} IN ["104-040", "104-041"]
 
or
 
{IMINVLOC_SQL.item_no} LIKE "104-04?"
IP IP Logged
Bryan
Newbie
Newbie
Avatar

Joined: 14 Aug 2008
Location: United States
Online Status: Offline
Posts: 10
Quote Bryan Replybullet Posted: 05 Sep 2008 at 8:37pm
Thanks I will try that on Monday when I get to work. As I said though there are some 14,000 items in our list, and I am only looking for the barstools. The barstools a;; start with a unique 3 digit code, but they all end in either a -040 or -041. We have about 12 or 13 styles of barstools in our line, so that is what I am trying to filter anything ending in 040 or 041. Thanks again.

Bryan
IP IP Logged
ezeney
Newbie
Newbie
Avatar

Joined: 05 Sep 2008
Location: United States
Online Status: Offline
Posts: 14
Quote ezeney Replybullet Posted: 07 Sep 2008 at 9:00am
Now I understand what you are looking for.  You can use regular expression.  If you know for sure that there will be three characters all the time before -040 or -041, try to use the following:
 
{IMINVLOC_SQL.item_no} LIKE "???-040" OR {MINVLOC_SQL.item_no} LIKE "???-041"
 
If you think there can be more than three characters but don't know how many, try this instead:
 
{IMINVLOC_SQL.item_no} LIKE "*-040" OR {MINVLOC_SQL.item_no} LIKE "*-041"
 
Using '*' will be slower than '?' since it will search all the possible combinations but won't be a big problem to search 14,000 items.  However, it depends on your database.  So check your database manual to use the correct characters for the regular expressions.


Edited by ezeney - 07 Sep 2008 at 9:17am
IP IP Logged
Savan
Senior Member
Senior Member
Avatar

Joined: 14 Dec 2007
Location: India
Online Status: Offline
Posts: 162
Quote Savan Replybullet Posted: 08 Sep 2008 at 5:24am
you can try this formula also :
 
mid(itemcode,1,1) = '3'
   and 
(
   mid(itemcode,length(itemcode) - 2,3) = '040'  or
   mid(itemcode,length(itemcode) - 2,3) = '041'
)
 
where itemcode is  your field name
Thanks
Savan
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.