Finding number based on alpha information 1

[kiwieur]

Hi people,

I am wondering if someone could help me get started with this problem please.

All off our production numbers include a "cycle" reference using alpha characters starting from "A" however there is a complication.

Please note i am only using the quotes to hopefully make my explanation easier to understand and they do nt actually appear in the production number

The "cycle" is 2 characters therefore when I say that they start from "A" what I really mean is that the first cycle is actually built up like this "<space>A" and displays as follows.

" A"

This is the same for the first 26 cycles through to " Z" then cycle 27 is as follows "AA" and so on, then cycle 53 is "BA" etc.

the cycle characters are always at postions 7 & 8 in the production number i.e. 123456 A or 123456AA etc.

My problem is how can i determine the the cycle as a number therefore

" A" would = 1
"AA" would = 27
"BA" would = 53

I want to run a query that shows me all production numbers in one field and then in the next field display the cycle as a number so that I can use this later for filtering purposes.

I am stumped as where to start with this and any help would really be appreciated







Regards

Paul

 

[PHV]

Cycle: IIf(Mid([ProductionNumber],7,1)<"A",0,26)*(Asc(Mid([ProductionNumber],7,1))-64)+Asc(Mid([ProductionNumber],8,1))-64

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kiwieur]

PHV,

Thank You so much for both the "warp speed" reply to my post and also code which works absolutely perfectly.

I don't think a "Star" does you justice but you get one anyway

Regards

Paul

 

[kiwieur]

PHV,

Sorry to bother you but I am always willing to learn.

Could you give me a brief description of how the statement is built up, I think understand the first part

(Mid([ProductionNumber],7,1)<"A",0,26)

but it's the second part that i am not quite clear on

*(Asc(Mid([ProductionNumber],7,1))-64)+Asc(Mid([ProductionNumber],8,1))-64

Regards

Paul

 

[PHV]

What is that do you not understand ?
In the VBA help have a look to:
IIf
Mid
Asc

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886