Text manipulation

[TeeSee]

Attempting to clean up an existing table and seem to have the need to
select a group of items as follows.

FG15120H and FG15050H are item part numbers and are in a text field

In the query grid I have the following two expressions

Expr1: Right([sisitemcode],InStr([sisitemcode],"h")-4)

Expr2: Left([Expr1],InStr([expr1],"h")-1)

This returns the last 3 digits before the "H" as 120 and 050. How can
I change these to values 120 and 50 and then set the parameter
correctly to select only the records <99 for instance?

Thanks

 

[Allen Browne]

Use Val() around your expression to convert the text to numeric value.

Val() doesn't cope with Nulls, so you may need Nz() as well, e.g.:
Val(Nz(Right([sisitemcode],InStr([sisitemcode],"h")-4), "0"))

(Not sure how your data is set up, but it sounds like you may have multiple
values in one field, which violates the normalization rule that each field
must be atomic.)

 

[TeeSee]

Use Val() around your expression to convert the text to numeric value.

Val() doesn't cope with Nulls, so you may need Nz() as well, e.g.:
    Val(Nz(Right([sisitemcode],InStr([sisitemcode],"h")-4), "0"))

(Not sure how your data is set up, but it sounds like you may have multiple
values in one field, which violates the normalization rule that each field
must be atomic.)

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Attempting to clean up an existing table and seem to have the need to
select a group of items as follows.

FG15120H and FG15050H are item part numbers and are in a text field

In the query grid I have the following two expressions

Expr1: Right([sisitemcode],InStr([sisitemcode],"h")-4)

Expr2: Left([Expr1],InStr([expr1],"h")-1)

This returns the last 3 digits before the "H" as 120 and 050. How can
I change these to values 120 and 50 and then set the parameter
correctly to select only the records <99 for instance?

Thanks- Hide quoted text -

- Show quoted text -

Allen ... Thanks as always for responding. I would like to more fully
understand your comment about normalization. I have read about and
thought I was following the rules for this.
my field "SISitemCode" is a text field and primary key field so all
records will be unique. to my way of thinking "FG15120H" and
"FG15050H" are diferent "values" in one field and they would have to
be.

What am I not getting please?

 

[TeeSee]

Use Val() around your expression to convert the text to numeric value.

Val() doesn't cope with Nulls, so you may need Nz() as well, e.g.:
    Val(Nz(Right([sisitemcode],InStr([sisitemcode],"h")-4), "0"))

(Not sure how your data is set up, but it sounds like you may have multiple
values in one field, which violates the normalization rule that each field
must be atomic.)

news:88113d89-99bd-4500-bb32-ac44a28cae35@v57g2000hse.googlegroups.com....

Attempting to clean up an existing table and seem to have the need to
select a group of items as follows.
FG15120H and FG15050H are item part numbers and are in a text field
In the query grid I have the following two expressions
Expr1: Right([sisitemcode],InStr([sisitemcode],"h")-4)
Expr2: Left([Expr1],InStr([expr1],"h")-1)
This returns the last 3 digits before the "H" as 120 and 050. How can
I change these to values 120 and 50 and then set the parameter
correctly to select only the records <99 for instance?
Thanks- Hide quoted text -

- Show quoted text -

Allen ... Thanks as always for responding. I would like to more fully
understand your comment about normalization. I have read about and
thought I was following the rules for this.
my field "SISitemCode" is a text field and primary key field so all
records will be unique. to my way of thinking "FG15120H" and
"FG15050H" are diferent "values" in one field and they would have to
be.

What am I not getting please?- Hide quoted text -

- Show quoted text -

Allen ... Your "one liner" works as expected ... Would you please
explain what the "0" part is doing??

Thanks again

 

[Douglas J. Steele]

The 0 is the second argument for the Nz function. If sisitemcode is Null,
the Right function will return Null. The Val function will fail if it's
passed a Null value (Runtime error 94: Invalid Use of Null). Allen's just
forcing the Val call to return 0 for Null strings.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

On Aug 20, 11:54 am, "Allen Browne" <[email protected]>
wrote:

Allen ... Your "one liner" works as expected ... Would you please
explain what the "0" part is doing??

Use Val() around your expression to convert the text to numeric value.

Val() doesn't cope with Nulls, so you may need Nz() as well, e.g.:
Val(Nz(Right([sisitemcode],InStr([sisitemcode],"h")-4), "0"))