Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

Crystal report request to show all part_id's with product-code and an alpha character in Eng_master_id

I have  a request to write a report that will return all Part_Id's that have both a product_Code and an alpha character in the Eng_Master_Id field. using the select expert how do i do this? This is what i got now and it no work:

({PART.ENGINEERING_MSTR}) LIKE '^[A-Za-z0-9]+$' and
NOT ISNULL ({PART.PRODUCT_CODE})
0
wallis34
Asked:
wallis34
  • 2
  • 2
1 Solution
 
mlmccCommented:
When you say it has an ALPHA character in the Eng_Master_ID field are you saying that it not a number or that it has at least 1 character that is A-Z?

Can it have special characters? or are they alphanumeric fields (made from A-Z (either case) and 0-9)?

You can try

Not IsNumeric({PART.ENGINEERING_MSTR}) 
AND
NOT ISNULL ({PART.PRODUCT_CODE})

Open in new window


mlmcc
0
 
wallis34Author Commented:
Thanks, that looks good, but there are still many records with blank product codes and actually blank engineering mstr fields. I have atached a couple screen shots please let me know if you want to see other stuff.  TIA!
report-01.jpg
select-expert.jpg
sql.rtf
0
 
mlmccCommented:
Are you sure the fields are NULL and not the empty string or one or more blanks?

Try

(
    NoT IsNull(PART.ENGINEERING_MSTR}) 
    AND
    Length(Trim(PART.ENGINEERING_MSTR}) > 0
    AND
    Not IsNumeric({PART.ENGINEERING_MSTR}) 
)
AND
(
    NOT ISNULL ({PART.PRODUCT_CODE})
     AND
    Length(Trim({PART.PRODUCT_CODE})) > 0
)

Open in new window


mlmcc
0
 
wallis34Author Commented:
This got it, your awesome, thanks!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now