INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Selection Criteria Formula that references database fields

Selection Criteria Formula that references database fields

(OP)
I am trying select records where only the following condition exists:

The second, third and fourth characters of the ‘ModelNo’ are not found anywhere within the ‘PartNo’.
(Both ‘ModelNo’ and ‘PartNo’ are fields found within each record of the database table used by the report. There is one only database table used by this report.)


I first created a formula called ‘@Model_234’, where:

@Model_234 = (Mid({tbl_ ModelNo},2,3))

I then set up the Selection Criteria using the following formula:

not ({tbl_PartNo} like [*{@Model234}*])


However, this Selection Formula does not filter the records at all.

Any thoughts on how to accomplish this task would be appreciated.

RE: Selection Criteria Formula that references database fields

Please show a sample of part numbers and model numbers as they would appear in a set of data. Then show what you would expect for results. It is unclear whether you want to eliminate part numbers that contain any of a set of partial model numbers or whether you just want to eliminate rows where the partial model number appears in the part number. For example:

Data set:
formula result part number

abc 12abc456
abc 345def78
def 9234xyz1
jkl 17mno94

Would you expect the result set to be (result 1):

abc 345def78
def 9234xyz1
jkl 17mno94

Or, only (result 2):

jkl 17mno94

In other words, do you want to eliminate all part numbers that contain ANY of the model number formula results, i.e., search all part numbers for the presence of ANY partial model number? Or do you just want to eliminate those that appear in the same row?

-LB

RE: Selection Criteria Formula that references database fields

(OP)
I only wish to select records where :

Upon examination of each record in succession...

Select only if : the set of characters in position 2 3 4 of the 'ModelNo' field ( i.e. 550) is not found anywhere within the 'PartNo' field of that same record.


Record1 : ModelNo - C5503445548 PartNo - xa33234qA45rt6 550 56ty [ Skip ]
Record2 : ModelNo - D5506858437 PartNo - 25w434e3t22222243 555rt [Select]
Record3 : ModelNo - E6578934455 PartNo - 4ewr134w56465745 6tyutt [Skip]







RE: Selection Criteria Formula that references database fields

Change your formula to:

Not({tbl_partno} like '*'+{@Model234}+'*')

-LB

RE: Selection Criteria Formula that references database fields

(OP)
With the NOT function I get no results and removing the NOT function I get all records. So no luck yet.

I tried pasting the formula into Notepad and replacing the 's with "s to see it that would work but same results.
(Are these intended to be single quotes ? I have not seem used single quotes used in this way in CR... so was not sure)

I added {@Model234} to the Details Section and it is generating the correct values.


(What are the "+" symbols designed to do in CR ? I have not used these before.)

Thanks Ospery

RE: Selection Criteria Formula that references database fields

Please post exactly what you entered in the selection formula.

Single or double quotes don't matter in the selection expert. The "+" is like an "&" but is specific to strings. Use whichever you like. In a command or in the SQL expression editor it would matter, but not in the selection expert.

-LB

RE: Selection Criteria Formula that references database fields

I don't see any reason why the solution offered by lbass would not have worked.

However, out of curiosity, I thought I would try it with a different approach and achieved the desired result with the following Selection Formula (you will need to substitute your table/column names):

CODE --> @Record_Selection

INSTR({Table.PartNo}, MID({Table.ModelNo}, 2, 3)) = 0 

Hope this helps.

Regards
Pete

RE: Selection Criteria Formula that references database fields

(OP)
I ran Pete's formula and 4 records were selected. I checked this and found that there was a Text Case issue so I used the Uppercase function and that had the formula retuning zero records.


I then re-examined the data in the PartNo. and found that there was information within that contained part of the Modoel No. in all cases (this is a large data field) so that as was reason that both formulae were coming back with zero records. Both formulae were working correctly .... my oversight of the data field contents was the real issue.

I have found another field that seems like it will work to filter the data as needed.... so far it is working correctly.

Thanks you both for your input.... you helped me located the real issue and things are ok now. ! Merci !

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close