trim!

[JohnLute]

I can never seem to get trim to work! I've got a text box with this control
source:

="Ingredient #: "+[Ingredient Number] & " Allergens: " &
[qryProfilesIngredientsAllergens.Allergens]

How can I trim “Allergens†when there’s no record for
[ryProfilesIngredientsAllergens.Allergens]?

Thanks!

 

[Ofer Cohen]

Insted of & use + ,like the first part

="Ingredient #: "+[Ingredient Number] & " Allergens: " +
[qryProfilesIngredientsAllergens.Allergens]

 

[Marshall Barton]

I can never seem to get trim to work! I've got a text box with this control
source:

="Ingredient #: "+[Ingredient Number] & " Allergens: " &
[qryProfilesIngredientsAllergens.Allergens]

How can I trim “Allergens” when there’s no record for
[ryProfilesIngredientsAllergens.Allergens]?

Nothing to do with Trim.

=("Ingredient #: "+[Ingredient Number]) & (" Allergens: "
+ Allergens)

 

[JohnLute]

That works perfectly! Thanks so much! Another "trick" I need to store in the
brain or at least remember where to find it again in my db next time I need
it. I just don't write enough code on a daily basis to remember everything!

--
www.Marzetti.com

Insted of & use + ,like the first part

="Ingredient #: "+[Ingredient Number] & " Allergens: " +
[qryProfilesIngredientsAllergens.Allergens]


--
Good Luck
BS"D

I can never seem to get trim to work! I've got a text box with this control
source:

="Ingredient #: "+[Ingredient Number] & " Allergens: " &
[qryProfilesIngredientsAllergens.Allergens]

How can I trim “Allergens†when there’s no record for
[ryProfilesIngredientsAllergens.Allergens]?

Thanks!

 

[JohnLute]

Thanks, Marshall.

I think I finally get it. Trim removes leading and trailing SPACES but NOT
characters.

I used Ofer's more "simple" solution regarding "+" instead of "&". The code
you have below actually didn't return as expected even though I corrected
"Allergens" to [qryProfilesIngredientsAllergens.Allergens]. Not sure what the
hiccup is but using the "+" did the trick.

Thanks again!

--
www.Marzetti.com

I can never seem to get trim to work! I've got a text box with this control
source:

="Ingredient #: "+[Ingredient Number] & " Allergens: " &
[qryProfilesIngredientsAllergens.Allergens]

How can I trim “Allergens†when there’s no record for
[ryProfilesIngredientsAllergens.Allergens]?

Nothing to do with Trim.

=("Ingredient #: "+[Ingredient Number]) & (" Allergens: "
+ Allergens)

 

[Marshall Barton]

I think I finally get it. Trim removes leading and trailing SPACES but NOT
characters.

I used Ofer's more "simple" solution regarding "+" instead of "&". The code
you have below actually didn't return as expected even though I corrected
"Allergens" to [qryProfilesIngredientsAllergens.Allergens]. Not sure what the
hiccup is but using the "+" did the trick.

Our expressions are the same except I added parenthesis to
make sure the + and & are done in the right order.

[qryProfilesIngredientsAllergens.Allergens]
is a ridiculous name for a field, so I assumed that you
really meant to use:
[qryProfilesIngredientsAllergens].[Allergens]
which, aside from the two fields same name issue, only needs
to be:
Allergens

You only need to qualify the field name with
qryProfilesIngredientsAllergens if you have two fields in
the record source that are named Allergens. Since that is
very unlikely (and if you do have two fields with the same
name, one of them should probably be aliased to a different
name), I thought I could eliminate your syntax error just by
eliminating the redundant qualifier.

 

[JohnLute]

Our expressions are the same except I added parenthesis to

make sure the + and & are done in the right order.

I see. That makes sense. I'm going to have to run this through a few more
times.

You only need to qualify the field name with
qryProfilesIngredientsAllergens if you have two fields in
the record source that are named Allergens. Since that is
very unlikely (and if you do have two fields with the same
name, one of them should probably be aliased to a different
name), I thought I could eliminate your syntax error just by
eliminating the redundant qualifier.

Likely it is! I actually have two "Allergens" one is for Ingredients and the
other is for Formulas. It's a quirky necessity. I'll look into making an
alias, too however it's all working OK as far as I can tell. Will this
current design create any issues?

 

[Marshall Barton]

I see. That makes sense. I'm going to have to run this through a few more
times.


Likely it is! I actually have two "Allergens" one is for Ingredients and the
other is for Formulas. It's a quirky necessity. I'll look into making an
alias, too however it's all working OK as far as I can tell. Will this
current design create any issues?

The only "issue" is the confusion associated with the name
Allergens. I would have recommended that you use two
different names such as IngredientAllergens and
FormulaAllergens back when you started, but it may be too
late to make that change without causing more disruption
than the confusion it removes.

Regardless of that, be sure to check that you do not put the
[ ] around the entire reference. In this case, because
there are only alphanumeric characters in the two names,
there is no need for the [ ] on either name, but watch out
for Access adding them incorrectly.