เรียงบ้านเลขที่ บลท.

access แบบสอบถาม

ORDER BY Val([house].[villcode]), Val([house].[hno])+1000, IIf(Len([house].[hno])-Len(Val([house].[hno]))=0,0,Right([house].[hno],Len([house].[hno])-Len(Val([house].[hno]))-1))+100;

excel สูตร
=IF(ISERROR(FIND("/",B3)),B3*1,LEFT(B3,FIND("/",B3)-1)+RIGHT(B3,LEN(B3)-FIND("/",B3))/1000)

Visual basic - VB - VBA

Public Function FormatAdr(Adr)
If IsNull([Adr]) Then
FormatAdr = "0000000"
ElseIf Not IsNumeric(Val(Adr)) Then
FormatAdr = Adr
Else
FormatAdr = Format(Val(Adr), "0000") & IIf(InStr(Adr, "/") = 0, Format(0, "000"), Format(Right(Adr, IIf(InStr(Adr, "/") = 0, 0, Len(Adr) - InStr(Adr, "/"))), "000"))
End If
End Function

mySQL query
ORDER BY house.villcode
,length(if(instr(hno,'/')>0,substring(hno,1,instr(hno,'/')-1),hno))
,if(instr(hno,'/')>0,substring(hno,1,instr(hno,'/')-1),hno)
,hno

ORDER BY
house.villcode,
length(if(instr(hno,'/')>0,substring(hno,1,instr(hno,'/')-1),hno)),
if(instr(hno,'/')>0,substring(hno,1,instr(hno,'/')-1),hno),
if(instr(hno,'/')>0,length(hno)-instr(hno,'/'),''),
if(instr(hno,'/')>0,right(hno,length(hno)-instr(hno,'/')),'')

เขียนโดย นายปองพล