# MS Access97 Concatenation

**URL:** <https://boards.straightdope.com/t/ms-access97-concatenation/169310>\
**Category:** Factual Questions\
**Created:** [April 17, 2003, 2:27pm UTC](https://boards.straightdope.com/t/ms-access97-concatenation/169310 "2003-04-17T14:27:42Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![drachillix](https://avatars.discourse-cdn.com/v4/letter/d/48db29/32.png) [@drachillix](https://boards.straightdope.com/u/drachillix)\
**Post date:** [April 17, 2003, 2:27pm UTC](https://boards.straightdope.com/t/ms-access97-concatenation/169310/1 "2003-04-17T14:27:42Z")

</div>

should or would there be any difference in the displayed value in a report between:

_16050501_ (the asterisks are charachters not wildcards)

and

TRIM("_" & [field with a text value of 16050501] & "_")

either way the field would be text

Its important to know if there is any difference because they are generating bar code for a bar code scanner system that is not yet in place.

I know the first way will work but it would involve manually adding asterisks to thousands of fields that should not have them except for this report.

Any bar code and access fluent dopers able to help?

---

<div class="post-metadata">

**Author:** ![grimpixie](https://avatars.discourse-cdn.com/v4/letter/g/ecb155/32.png) [@grimpixie](https://boards.straightdope.com/u/grimpixie)\
**Post date:** [April 17, 2003, 2:41pm UTC](https://boards.straightdope.com/t/ms-access97-concatenation/169310/2 "2003-04-17T14:41:20Z")

</div>

At my work I do a similar thing to your number two suggestion, and it seems to work just fine - although I don’t see that there should be any difference between the two, but the latter is better in case you ever need to use the number without the asterisks.

Although:

“_" & TRIM([field with a text value of 16050501]) & "_”

might be better to use than what you’ve suggested…

Grim

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [April 17, 2003, 2:56pm UTC](https://boards.straightdope.com/t/ms-access97-concatenation/169310/3 "2003-04-17T14:56:15Z")

</div>

If you are likely to use this expression more than a couple of times in your application, then I would define it as a function;

Add a module to your database and add code to it something like this:

```auto

Public Function Make3of9(Arg1 As String) As String
make30f9 = "*" & Trim(Arg1) & "*"
End Function

```

Then you can just use:

Make3of9([field with a text value of 16050501])

in your code, queries, reports, etc.

---

<div class="post-metadata">

**Author:** ![KellyM](https://avatars.discourse-cdn.com/v4/letter/k/57b2e6/32.png) [@KellyM](https://boards.straightdope.com/u/KellyM)\
**Post date:** [April 17, 2003, 3:04pm UTC](https://boards.straightdope.com/t/ms-access97-concatenation/169310/4 "2003-04-17T15:04:01Z")

</div>

You can also create a query that returns the same table with the field modified:

SELECT …, “_" & TRIM([FieldName]) & "_” as [FieldName3of9], … FROM …

And then use that query as the data source for your report. Note that such a query is not updatable, at least not for that field.

Note that using computed values in datasets in Access can be **extremely** slow when you’re using an ODBC datasource. (This is because Access sometimes has to synthesize the query by iterating the underlying datasource.) This has bit me several times when upsizing Access applications to a client/server model with a SQL back end. Keep this in mind if scalability is a concern in your product design.

---

<div class="post-metadata">

**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\
**Post date:** [April 17, 2003, 3:04pm UTC](https://boards.straightdope.com/t/ms-access97-concatenation/169310/5 "2003-04-17T15:04:42Z")

</div>

Mangetout is right on for the additional reason that as a general rule, calculated values should not be stored.

Also, a great resource for Access questions of all kinds can be found at:

[http://www.utteraccess.com/](http://www.utteraccess.com/)

---

<div class="post-metadata">

**Author:** ![KellyM](https://avatars.discourse-cdn.com/v4/letter/k/57b2e6/32.png) [@KellyM](https://boards.straightdope.com/u/KellyM)\
**Post date:** [April 17, 2003, 3:19pm UTC](https://boards.straightdope.com/t/ms-access97-concatenation/169310/6 "2003-04-17T15:19:16Z")

</div>

> [@](#):
>
> \*Originally posted by zoid \*  
> \*\*Mangetout is right on for the additional reason that as a general rule, calculated values should not be stored. \*\*

Except, of course, when the calculation is expensive. 🙂

---

<div class="post-metadata">

**Author:** ![drachillix](https://avatars.discourse-cdn.com/v4/letter/d/48db29/32.png) [@drachillix](https://boards.straightdope.com/u/drachillix)\
**Post date:** [April 17, 2003, 3:42pm UTC](https://boards.straightdope.com/t/ms-access97-concatenation/169310/7 "2003-04-17T15:42:36Z")

</div>

[QUOTE]  
\*Originally posted by Mangetout \*

```auto

Public Function Make3of9(Arg1 As String) As String
make30f9 = "*" & Trim(Arg1) & "*"
End Function

```

Could you give an example of what arg1 would be

I am assuming an argument but my access rookie brain isn’t connecting how to make this work.

FTR I have never messed with VB or anything very complex in Access.

I can see where custom function creation would be very valuble.

---

<div class="post-metadata">

**Author:** ![zoid](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/zoid/32/336_2.png) [@zoid](https://boards.straightdope.com/u/zoid)\
**Post date:** [April 17, 2003, 5:56pm UTC](https://boards.straightdope.com/t/ms-access97-concatenation/169310/8 "2003-04-17T17:56:49Z")

</div>

> [@](#):
>
> \*Originally posted by KellyM \*  
> \*\*Except, of course, when the calculation is expensive. 🙂 \*\*

You’re right of course. That’s why I stated it as a _general_ rule and not something that should be adhered to in a hard and fast way. 🙂

---

<div class="post-metadata">

**Author:** ![yoyodyne](https://avatars.discourse-cdn.com/v4/letter/y/a9a28c/32.png) [@yoyodyne](https://boards.straightdope.com/u/yoyodyne)\
**Post date:** [April 18, 2003, 12:05am UTC](https://boards.straightdope.com/t/ms-access97-concatenation/169310/9 "2003-04-18T00:05:12Z")

</div>

[QUOTE]  
\*Originally posted by drachillix \*  
\*\*

> [@](#):
>
> \*Originally posted by Mangetout \*
> 
> ```auto
> 
> Public Function Make3of9(Arg1 As String) As String
> make30f9 = "*" & Trim(Arg1) & "*"
> End Function
> 
> ```
> 
> Could you give an example of what arg1 would be
> 
> I am assuming an argument but my access rookie brain isn’t connecting how to make this work.
> 
> FTR I have never messed with VB or anything very complex in Access.
> 
> I can see where custom function creation would be very valuble. \*\*

Arg1 is just a variable name, the first line tells the function to accept a string value (the field with the number in it) and call it Arg1, then that value is used in the trim function. It doesn’t matter what you call it, as long as it’s the same in both places.