Right function

[vgersghost]

Hello:

I have a number letter combination I trying to use the last 5 digits. Example
Right( "1234E5678",5) which returns E5678 just fine. The problem is I'm converting this to a barcode and only need numbers. I tried Val in front but it returns the same value when the numbers are close. Like E5678 or E5679 both return 69 How do I strip the E only convert it to a number add it back to the end 4 numbers. Mid does not work because part numbers vary in length.


Thank you.

 

[Rabbie]

Hello:

I have a number letter combination I trying to use the last 5 digits. Example
Right( "1234E5678",5) which returns E5678 just fine. The problem is I'm converting this to a barcode and only need numbers. I tried Val in front but it returns the same value when the numbers are close. Like E5678 or E5679 both return 69 How do I strip the E only convert it to a number add it back to the end 4 numbers. Mid does not work because part numbers vary in length.


Thank you.

I am not quite sure what you are trying to do here. I can't see how you are getting 69 ftom Val(E5678) or Val(E5679). It would help if you could define the full format of your part number.

 

[vgersghost]

Thanlk you

Thanks for your response, but I'm not sure what your asking me. "It would help if you could define the full format of your part number." They are just numbers. store as text in the database. Examples: ade12345e4567, ade1234r5678 and etc. Maybe I just confused.

 

[Rabbie]

Thanks for your response, but I'm not sure what your asking me. "It would help if you could define the full format of your part number." They are just numbers. store as text in the database. Examples: ade12345e4567, ade1234r5678 and etc. Maybe I just confused.

Firstly they are not numbers they are text strings containing letters and numbers. Your examples now are different from the ones in your first post. What number do you want for your barcode. Is it the last 4 digits or just the last digits of partnumber. If you can define what you want on paper then you can do it in Access. if you can't define it on paper you won't be able to do it in Access,

 

[vgersghost]

Thank you

I what I'm looking for is to use the last five digits of the part number converted into a barcode. They all text strings. The strings could be all numbers or a combination of text and numbers. So if the part number is like A234E5678 I would like to see the results of be the E conveted to a number. Example say 4 and then added back to the last 4 digits, so you have 45678. Or if they are all numbers 123456789 the result would be 56789.
Hope this clears this up.

 

[DCrake]

Simple Software Solutions

If you want to replace a known letter with a known number then the simplest way is to use the replace() function

as you suggested if it finds the letter E replace it with the number 4, therefore

MyString = replace(MyString,"E","4")

In real time "1234E4356EF3489" becomes "1234443564F3489"

CodeMaster:

 

[Mike375]

You could do your Right for 5 characters and say the following examples:

E4564
76893
A3890
64934



Asc() will give you the ansi number for the first character and so tell you if the first character from the Right() is a letter or a number.

You could then do as DCrake suggested and replace the letter on all those that had the ansi number for letters. You would need to do it for both upper and lower case as they are two sets of numbers.

 

[Rabbie]

If you want to replace a known letter with a known number then the simplest way is to use the replace() function

as you suggested if it finds the letter E replace it with the number 4, therefore

MyString = replace(MyString,"E","4")

In real time "1234E4356EF3489" becomes "1234443564F3489"

CodeMaster:

I think he is really looking for a numeric answer he can feed into the barcode generator based on the last 5 characters. Since he has not yet confirmed the layout of the number.

What happens if a number is abc1234efg123

It is a programming truism that if you cant do it manually you don't know enough to do it programmatically. So we really need to have a clearer definition of the partnumber and how he wants the barcode number calculated.

 

[vgersghost]

Rabbie is correct

I'm trying to produce a barcode from the last five items of the string. Right now the part numbers are like this . 4 numbers, a letter , 4 numbers EXAMPLE 1234 A 5678 or 4 numbers, 1 number, 4 numbers EXAMPLE 1234 5 6789. (No spaces examples for clarity) The barcode needs to be the last five digits. Mutiple part numbers could all have an A as the fifth number. Then I guess this leads to another problem. If letter is converted there is a posiblity the last four digits could be the same, making the barcodes the same. I need them to all be different, but still have the part of the part number in the bar code.

Sorry for the confusion. Sometimes I know what I want in my head, but cannot explain it.

 

[Mike375]

What do you want the letter converted to?

After your Right () you have

E4456
66377
56780
F4343
G3545

What do you want E, F and G changed to?

 

[georgedwilkinson]

What do you want E, F and G changed to?

And why, since barcodes are alpha-numeric?

 

[vgersghost]

Answer both questions pose.

The letters would have to be numbers, I guess they could correspond to the alpahbet a=1, b=2 and etc. Yes barcodes can be alpha-numeric, but they are being scanned into an AS-400 system, that gets confused with the letters.

 

[Mike375]

The letters would have to be numbers, I guess they could correspond to the alpahbet a=1, b=2 and etc. Yes barcodes can be alpha-numeric, but they are being scanned into an AS-400 system, that gets confused with the letters.

Then if your Right() gives you

E4456
66377
56780
F4343
G3545

Asc() will tell you which ones start with a letter or a number. Probaly better ways but that is one way. That would allow you to filter them. The numbers for 0 to 9 are 48 to 57. Here is a link that will give you all the characters

http://www.alanwood.net/demos/ansi.html

 

[Mike375]

Here is a little db to let you see Asc() and Right() splitting them.

 

[vgersghost]

Thank you Mike375 for the sample.

It explains allot and gives me something to work with. Plus the link to the table earlier. I will now try to figure out how to put it into my coding for the barcode.

thank you

 

[Mike375]

One problem I just realised you have is if you have a=1, b=2, c=3 etc you have double digits which will mean a lot of your final numbers will have 6 instead of 5 digits

Of course if there are only 9 or less letter variations no problem. Although Asc() gives a different number for a and A, c and C etc it is easy to make a little table to take care of that.