Extract part of existing text

[BobBrooks]

I have an Access 2000 Datbase that is used to create Barcode labels for raw matrial received. I need to update this database for shipping the end product.
We scan the label into a form and from the form, a report is created for shipping the end product. There are six fields per record on the form.
I need to extract the last 8 characters from one field - the last 3 characters from each of 3 fields - and the last 5 to 7 characters from 1 field. the one field with 6 - 8 characters is the product name with a single AS400 field identifier (a letter C) at the start.
5 of the 6 fields that comprise the record have the AS400 field identifier ( either a single character ( E, C, Q, T, or another Letter) or a number followed by a character (3S)
This may be alot to ask but, I appreciate all response from the experts that may read this.
Bob

 

[awarnica]

OK, I'll give you the functions that would be used in Visual Basic, not sure where to put them in Access though.

The LEFT(string, integer) and RIGHT(string, integer) functions return the number (integer) of characters from the left or right of the given string.

So one of the fields would be RIGHT(name_of_field, 8), another would be RIGHT(name_of_field, 3).

When you have all of the information, you can concatenate strings in visual basic with the '&' character.

I dont know how this would work in access, but somewhere I would guess you would have code to concatenate the fields appropriately. Perhaps someone more versed in access will know where to put this.

 

[67peshawar294]

Bob
Getting the "last 8 characters" and the "last 3 characters"
from the fields is pretty easy;
=right([charfield],8)& right([1st3field,3)& right([2nd3field],3).
The other one, where the size varies 6 to 8 is more cumbersome.
IF the indentifier always began with a certain letter and there was never an instance of the letter appearing elsewhere in the string it would be;
=Mid$([yourtxtfield],InStr([yourtextfield],Chr(asciinumberofletter)),Len([yourtextfield]))
Then concantate with rest of number.
jim

 

[BobBrooks]

I have previously tried to use for example:
"Private Sub Net_Enter()"
"[Tare] = Right([Tare], 3)"
"End Sub
When it clears the leading character, it no longer will automatically advance to the next field. I use an RF scanner that sends an ENTER as a suffix to the data to advance, there is no one at the pc to hit enter. I am still trying to find a solution and hoping someone has attempted something similar to my project.
Thanks for offering.
Bob

 

[67peshawar294]

Bob, I use a 000000000000;0;@ for mine, UPC-A 12 digits, but yours for three, 000;0;@ should work in the inputmask of the textbox. When the code is scanned it auto advances to the next field. (same field in my case.)
jim

 

[BobBrooks]

To xaf294
I tried the 000;0;@ and it truncates the string right to left and drops the first right character.

I also tried the previous suggestions in VB, the Table input mask and the Form as an expression, none of which works. There may be something about the Font used (Code 3 of 9 aka Code 39)and the hidden "*" character required by the AS400 to read the Font.

Thanks for your suggestions
Bob

 

[67peshawar294]

Bob,
Have you tried increasing the zeros to account for the chars?
jim

 

[BobBrooks]

Yes, I have tried to increase the zeros to as many as 7, with no effect. I have tried to use the @, the # and the L in various manners with no success. I appreciate the help offered as there many methods to resolve my problem. I am leaning toward some varity of Visual Basic Sub Routine using "If - then - Else". My biggest problem is having enough time to work on it without interuption from my other responsibilities at work.
Bob

 

[67peshawar294]

bob,
How about if you block any sensitive info from a screen shot of your form and email me. I have a code 3of9 font on my system and perhaps I could see the problem more clearly.

jpen_10@hotmail.com
jim