how to print the 3 number from the right 1

[MICKI0220]

I have a report that I need to Highlight, in larger font, and by itself, the third number from the right of a customer order number. Can someone tell me how to parse this number out? for example a number 07/28/11K-3/5. I would need to put the 3 somewhere as a text box or label, independently from the rest of the alpha numeric expression.

Any help is appreciated.

 

[dhookom]

I expect you could use a combination of Right() and Left().

Right() takes the rightmost X characters of a string (like 3) and Left() takes the leftmost X characters of a string (like 1). You can nest these to get the character you need.

Please try to work this out. If you have questions, feel free to come back and ask (unless someone feeds you the answer).

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

Can explain it a little bit more. Would I put this expression in a query. For example that number ( 07/28/11K-3/5) is the customer number. Would I query it like ......Truck #: (customer# (Right 93)))?

 

[dhookom]

Did you search any Help on the Left() and Right() functions? Click the Help? and enter
Left Function
or
Right Function
or

String Functions

Also, since you have chosen to include # in a field name, you must wrap it in []s.

Hint:
TruckNum: Right([Customer#], /your number here/)



Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

I checked it out with the Right function. That gives me three characters from the right, not just the third character only. Do you have information on what would isolate that third character from the right?

 

[Gammachaser]

How about

Left(Right([i]MyText[/i],3),1)

That work for you?

 

[dhookom]

I was hoping MICKI0220 could figure this out with some gentle prodding and prompting.

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

Thanks that did it......I looked through len, string manipulation and everything wasn't giving me anything to compare with one character without nesting.....Thanks for the help.

 

[judgeh59]

It looks like there is a MID function in AC just like Excel...is that better or worse than the Left(right(xx,3),1) solution?

Inquiring minds wanted to know...

Ernest

Be Alert, America needs more lerts

 

[MICKI0220]

I just realized that it did nest, but not as convoluted as I would have nested them.... Thanks again.

 

[dhookom]

Mid() would work but would probably require the use of Len(). I think the nested Left(Right()) is the easiest to implement and meets all the requirements.

Duane
Hook'D on Access
MS Access MVP

 

[Gammachaser]

I was hoping MICKI0220 could figure this out with some gentle prodding and prompting.
Duane

Yeah, I knew that, but the teacher in me just wouldn't shut up.

 

[judgeh59]

thanks Duane...make sense...either way you have to nest something...thanks again...

Ernest

Be Alert, America needs more lerts

 

[dhookom]

Gammachaser,
I had to fight from providing the answer in the first post.

I appreciate your regular posting regarding hard-coded values. I like the link Data belongs in your table -- not in your code

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

"the teacher" teaches by GUIDING the learner to discovery, rather than GIVING the answer to the learner.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gammachaser]

"the teacher" teaches by GUIDING the learner to discovery, rather than GIVING the answer to the learner.
Skip

OK, the impatient teacher in me couldn't shut up.

You are correct, I was being an instructor and not a teacher.

Learning took place. In this case the learning process came after the discovery, not before. Not the preferred method when trying to instill desire, but a valid Adult Learning method in the business world. No sense in prolonging the suspense unless you are trying to get students to want to learn by letting them have the 'Aha' moment by themselves, which works well for children and less so for adults. I was assuming the OP was an adult. First shots are free -- those that show a propensity for requiring spoon feeding get less attention.

 

[MICKI0220]

Gammachaser. Learning did take place and I am an adult....Unfortunately I don't have all day and night to spend searching for the knowledge we need to make something work in the workplace, that is why most of us ask for guidance and sometimes a straight answer. After seeing that the result worked, I did realize a different and simpler method of nesting, hence I learned something. Thank you again for the help, and I am sure my user appreciated a quicker result to her quandry.

 

[SkipVought]

Learning did take place ...

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]