Trim

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I trim the "-" in the following:

Liner: [OutsideLinerWeight] & "-" & [1MediumWeight] & "-" & [2LinerWeight] &
"-" & [2MediumWeight] & "-" & [3LinerWeight] & "-" & [3MediumWeight] & "-" &
[4LinerWeight]

Thanks!
 
How can I trim the "-" in the following:

Liner: [OutsideLinerWeight] & "-" & [1MediumWeight] & "-" & [2LinerWeight] &
"-" & [2MediumWeight] & "-" & [3LinerWeight] & "-" & [3MediumWeight] & "-" &
[4LinerWeight]

Thanks!
Click to expand...

What do YOU mean by "Trim the "-"?
Do you wish to have each of the fields simply run together?
i.e. Doyouwishtohaveeachofthefieldssimplyruntogether?
Write the expression as
Liner: [OutsideLinerWeight] & [1MediumWeight] & [2LinerWeight] &
etc...

If you mean something else, post back.
 
Hi, Fred.

For records that contain null values it returns:
------

For records that have partial valuse it returns:
-26-----
42-26-42----
35-26-26-26-35--

I'd like for the "-" to appear only when the following values are not null.

--
www.Marzetti.com


fredg said:
How can I trim the "-" in the following:

Liner: [OutsideLinerWeight] & "-" & [1MediumWeight] & "-" & [2LinerWeight] &
"-" & [2MediumWeight] & "-" & [3LinerWeight] & "-" & [3MediumWeight] & "-" &
[4LinerWeight]

Thanks!
Click to expand...

What do YOU mean by "Trim the "-"?
Do you wish to have each of the fields simply run together?
i.e. Doyouwishtohaveeachofthefieldssimplyruntogether?
Write the expression as
Liner: [OutsideLinerWeight] & [1MediumWeight] & [2LinerWeight] &
etc...

If you mean something else, post back.
Click to expand...
 
How can I trim the "-" in the following:

Liner: [OutsideLinerWeight] & "-" & [1MediumWeight] & "-" & [2LinerWeight] &
"-" & [2MediumWeight] & "-" & [3LinerWeight] & "-" & [3MediumWeight] & "-" &
[4LinerWeight]

Thanks!
Click to expand...

Could you explain what you mean by "trim"?

If you want the hyphen prior to a Weight field to be omitted if the
field is NULL, you can use the fact that the + operator concatenates
strings, but - unlike & - propagates NULL values. Try

Liner: [OutsideLinerWeight] & ("-" + [1MediumWeight]) &
("-" + [2LinerWeight]) &
("-" + [2MediumWeight]) &
("-" + [3LinerWeight]) &
("-" + [3MediumWeight]) &
("-" + [4LinerWeight])

Note that storing data in fieldnames as you are doing is a *serious*
violation of good database design rules. If you have a 4LinerWeight,
someday you will have a 5LinerWeight value to enter, and you'll be in
trouble when you need to redesign your tables, queries, forms and
reports to handle it! If you have a one to many relationship from
whatever this table represents to multiple weights, *model it as a one
to many relationship*. "Fields are expensive, records are cheap"!

John W. Vinson[MVP]
 
Thanks, John. That works fine!

Thanks for the warning. It may appear that I have a 1-Many relationship
however it's not the case.

These fields are relative to corrugated box designs. One box can have up to
3 Mediums and 4 Liners. This may seem to call for a 1-Many relationship but
doing so would actually complicate the design in many more ways than there is
space here to describe.

In this instance I'm managing the data in a more reasonable manner. Yes -
there's always a possibility to add a 5LinerWeight however that would mean a
Quadruple Wall corrugated box which is very rare in our industry. In fact, we
don't even have one Triple Wall box in our system (which would call for a
value in the 4LinerWeight field) so the database is actually doing more than
currently necessary.

Thanks again for the help!!!

--
www.Marzetti.com


John Vinson said:
How can I trim the "-" in the following:

Liner: [OutsideLinerWeight] & "-" & [1MediumWeight] & "-" & [2LinerWeight] &
"-" & [2MediumWeight] & "-" & [3LinerWeight] & "-" & [3MediumWeight] & "-" &
[4LinerWeight]

Thanks!
Click to expand...

Could you explain what you mean by "trim"?

If you want the hyphen prior to a Weight field to be omitted if the
field is NULL, you can use the fact that the + operator concatenates
strings, but - unlike & - propagates NULL values. Try

Liner: [OutsideLinerWeight] & ("-" + [1MediumWeight]) &
("-" + [2LinerWeight]) &
("-" + [2MediumWeight]) &
("-" + [3LinerWeight]) &
("-" + [3MediumWeight]) &
("-" + [4LinerWeight])

Note that storing data in fieldnames as you are doing is a *serious*
violation of good database design rules. If you have a 4LinerWeight,
someday you will have a 5LinerWeight value to enter, and you'll be in
trouble when you need to redesign your tables, queries, forms and
reports to handle it! If you have a one to many relationship from
whatever this table represents to multiple weights, *model it as a one
to many relationship*. "Fields are expensive, records are cheap"!

John W. Vinson[MVP]
Click to expand...
 
Back
Top