【Access】ORDER BYが正しく並ばない原因｜Val関数で解決

AccessでNoなどの番号で一覧表示した際、

「1,2,3…」と並ぶはずが「1,10,11,2,3…」となって困ったことはありませんか？

1の次が2じゃなくて10になるんだけど…なんでこうなるの？

私もまったく同じ状況にハマり、原因が分からずかなり時間を無駄にしました。

実はこの現象フィールドのデータ型が「テキスト型」になっていることが原因で起こります。

そこで本記事ではORDER BY が正しく並ばない原因とVal関数を使った解決方法を実例つきで分かりやすく解説します。

ORDER BYが正しく並ばない原因

ORDER BYを指定したのに「1,10,11,2,3…」となってしまう原因は、

並び替え対象のフィールドが「テキスト型」になっていることです。

Accessでは、テキスト型のフィールドは数値ではなく「文字列」として並び替えが行われます。

そのため並び順は次のような文字コード順（辞書順）で処理されます。

1 → 10 → 11 → 2 → 3 → 4 → …

これは、先頭の「1」「2」「3」だけを比較して並び替えを行うためです。

つまり数値としての大小比較ではなく文字列としての比較が行われていることが正しく並ばない根本原因になります。

SQLの書き方を疑いましたが、原因はデータ型でした。

正しく並び替えるには

原因が分かったところで、実際にどうすれば正しく並び替えできるのかを解説します。

フィールドの型を「数値型」に変更する

フィールドの型をテキスト型から数値型に変更することで容易に解決することができます。

フィールドの型を変更する手順は次の通りです。

STEP

テーブルをデザインビューで開く

フィールドの型を変更したいテーブルを右クリックし「デザインビュー」をクリックします。

ナビゲーションウィンドウから対象テーブルを選択してください。

STEP

フィールドの型を変更する

型を変更したいフィールドの「データ型」をドロップダウンから選択します。

今回は例として名簿テーブルのNoのデータ型を「短いテキスト」から「数値型」に変更します。

STEP

設定内容を保存する

名簿テーブルタブを右クリックし「上書き保存」をクリックします。

私も最初はSQLの書き方ばかり疑っていましたが、
実際にはこの方法で一瞬で解決しました！

Val関数を使ってSQL側で数値変換する

システムの仕様や他の処理との連携の関係で、

フィールドの型をどうしても「テキスト型」で運用しなければならない場合もあります。

そのような場合は、Val関数を使ってSQL側で一時的に数値型として扱うことで、

正しい並び替えを行うことができます。

 正しく並ばない例（テキスト型のまま並び替え）

SELECT * FROM T名簿 ORDER BY No;

この場合、「1,10,11,2,3…」のように正しく並びません。

 正しく並び替えられる例（Val関数で数値変換）

SELECT * FROM T名簿 ORDER BY Val(No);

Val関数を使うことで、テキスト型の数値を一時的に数値型へ変換できるため、

「1,2,3,4,5…10,11」と正しい順序で並び替えされます。

業務の都合で「テキスト型」で運用する必要があったため、
Val関数には本当に救われました！

Val関数を使った実際の使用例

ここからは実務でよく使う処理にVal関数を組み込んだ使用例をご紹介していきます。

検索処理 × Val関数

Private Sub cmd_検索_Click()

    Dim str_SQL As String
    Dim str_where As String

    str_SQL = "SELECT * FROM T名簿 "
    str_where = ""

    If Nz(Me.txt氏名, "") <> "" Then
        str_where = str_where & " AND 氏名 Like '*" & Me.txt氏名 & "*'"
    End If

    If Nz(Me.cbo部署, "") <> "" Then
        str_where = str_where & " AND 部署 = '" & Me.cbo部署 & "'"
    End If

    If str_where <> "" Then
        str_SQL = str_SQL & " WHERE " & Mid(str_where, 6)
    End If

    str_SQL = str_SQL & " ORDER BY Val(No);"

    Me.RecordSource = str_SQL
    Me.Requery

End Sub

検索結果をNo順で正しく表示する仕様は、業務アプリではほぼ必須レベルで使います！

採番処理 × Val関数

Dim newNo As Long

newNo = Nz(DMax("Val(No)", "T名簿"), 0) + 1

Me.No = newNo

このコードは、名簿テーブルの最大Noに1を加算して新しい番号を採番する処理です。

Noフィールドがテキスト型のままDMax("No", "T名簿")を使用すると、

文字列順で評価されるため誤った最大値が取得され、採番ミスの原因になります。

そこで Val関数を使って数値として評価 することで、テキスト型のままでも正しい採番処理が可能になります。

私もこのトラブルにハマりなぜ採番がおかしくなるのか分からずかなり悩みました。

まとめ

今回は、ORDER BY が正しく並ばない原因と、Val関数を使った解決方法を解説しました。

並び順がおかしい場合は、まずフィールドのデータ型を確認することが重要です。

Accessの実務でよく起こるトラブルなのでぜひ本記事の内容を活用してください。

👇当サイトでは他にもAccessの実務で役立つVBA・SQLノウハウを解説しています。

あわせて読みたい

【Access】DLookup関数の使い方まとめ｜基本テクニック解説 AccessではDLookup関数を使うことで、テーブルやクエリから条件に一致するデータを簡単に取得できます。 一方で「書き方が分からない」「条件指定でエラーになる」と悩…