MS Access97 Concatenation

Factual Questions
1

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?

2

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

3

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:


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.

4

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.

5

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/

6

Except, of course, when the calculation is expensive. :slight_smile:

7

[QUOTE]
*Originally posted by Mangetout *


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.

8

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. :slight_smile:

9

[QUOTE]
*Originally posted by drachillix *
**

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.