Peculiar Sequencing Problem

[jkfeagle]

I have a kind of peculiar sequencing issue with a query. I have several machine designations:
A1
A2
A3
....
A10
A11
A13
B1
B2
etc.

but the last group is designated as right or left...in other words

C1R
C1L
C2R
C2L
...

I am trying to get the query to put them in order such that they appear as:

A1
A2
...
A10
A11
A12
A13
...
C1L
C1R
C2L
C2R

This requires breaking the first letter out, sequencing it and then doing the same for the other part(s). The first letter sequencing works fine but I can't seem to get the second part right. For the 1-13 designations, I am multiplying it by 1 in order to convert it to a number and thus make it sequence correctly. This worked before I had to add the R and L designations. Now it doesn't. Here is the statement I'm using for the second sequncing portion in the query:

IIf(Left([Machine],1)="C",Mid([Machine],2,2),Mid([Machine],2,2)*1)

It doesn't seem to convert the 1-13 over to numbers and is sequencing as if they are text, meaning it's coming out A1, A10, A11, A12, A13, A2, .... Is the query limited to one data type as an output to an expression or something?

 

[Brianwarnock]

I suspect that you are correct in that an IF which evaluates both parts of the result will see that the TRUE part must be a string and output the False as a string.
Try
IIf(Left([Machine],1)="C",Mid([Machine],2,len(machine)-2,Mid([Machine],2,2)*1)

Brian

 

[jkfeagle]

Brian,

Thanks for the help. Unfortunately, it doesn't appear to work either. Any other ideas?

John

 

[Brianwarnock]

Not off hand I may have to create a test and fiddle.

Brian

 

[Brianwarnock]

Off course missed *1 off
IIf(Left([Machine],1)="C",Mid([Machine],2,Len([machine])-2)*1,Mid([Machine],2,2)*1)

Brian

 

[jkfeagle]

Bravo! Thanks. I had to add and additional field to make sure the L and R alternated but that did it. Cheers!

 

[Brianwarnock]

Bravo! Thanks. I had to add and additional field to make sure the L and R alternated but that did it. Cheers!

I guessed that you would work that out if wanted it, didn't want to insult you with hand holding

Brian