Converting with LEN 1

[BPhilb]

I have what should be a simple query that has been killing me. I'm trying to convert a string time to group by the hour. I'm able to get the hours 12-23 fine, but the single digit numbers are still coming back as two digit numbers (e.g. 3 shows as 37). Can someone give me some gudience as to what I am missing with this query. Thanks in advance for your help.

SELECT IIf(Len([PRBGTI]=6),(Left([PRBGTI],2)),IIf(Len([PRBGTI]=5),(Left([PRBGTI],1)),IIf(Len([PRBGTI]=4),("0"),(" ")))) AS [Time], Count(CartonsbyTime.PRTXTP) AS CountOfPRTXTP
FROM CartonsbyTime
GROUP BY IIf(Len([PRBGTI]=6),(Left([PRBGTI],2)),IIf(Len([PRBGTI]=5),(Left([PRBGTI],1)),IIf(Len([PRBGTI]=4),("12"),(" "))));

 

[PHV]

The way you use the Len function is very strange.
what about this ?
SELECT IIf(Len([PRBGTI])=6,Left([PRBGTI],2),IIf(Len([PRBGTI])=5,Left([PRBGTI],1),IIf(Len([PRBGTI])=4,"0"," "))) AS [Time], Count(CartonsbyTime.PRTXTP) AS CountOfPRTXTP
FROM CartonsbyTime
GROUP BY IIf(Len([PRBGTI])=6,Left([PRBGTI],2),IIf(Len([PRBGTI])=5,Left([PRBGTI],1),IIf(Len([PRBGTI])=4,"0"," ")));

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

 

[BPhilb]

Worked like a charm. Did having the results closed make all of the difference? Thanks for your assitance with this.