最終レコードの値の求め方について

質問

WinXP、Access2002にて以下のような納入先テーブルを作っています。
ここで、あるフォームから、「0002：株B」の新しい納入先を登録する場合
納入先コードは「03」としたいのですが、その場合の
最終の納入先コードの求め方がわかりません。
（「0001:株A」だと次に追加する場合は「04」になる）

DLastでは、フィールド内の最後の値になるため
用件を満たせません。。
「対象顧客IDの納入先コードの最終番号+1」を
求める命令文になると思うのですが・・

顧客ID　納入先コード　会社名　都道府県
-------------------------------------
0001　　01　　　　　　株A　　　大阪
0001　　02　　　　　　株A　　　東京
0001　　03　　　　　　株A　　　兵庫
0002　　01　　　　　　株B　　　北海道
0002　　02　　　　　　株B　　　京都

求める方法はおそらくSQLでも関数でもいけるような
気がしますが、どなたか教えてください。

回答No.2

noname#22222 · ベストアンサー

<納入先>

ID　　顧客ID　納入先コード　会社名　都道府県
1　　　1　　　　1　　　　　　A　　　XX
2　　　1　　　　2　　　　　　A　　　XX
3　　　1　　　　3　　　　　　A　　　XXX
4　　　2　　　　1　　　　　　B　　　XXXX
5　　　2　　　　2　　　　　　B　　　XX

というテーブルとデータが存在する場合、

? DBMax("納入先コード", "納入先", "顧客ID=1") + 1
4
? DBMax("納入先コード", "納入先", "顧客ID=2") + 1
3

と、イミディエイトウィンドウで確認すれば良いと思います。
なお、DMAX関数は非常に遅いので自作した方がいいです。

（意見）列[会社名]と[都道府県]は無用かと思います。

Public Function DBMax(ByVal strField As String, _
　　　　　　　　　　　ByVal strTable As String, _
　　　　　　　　　　　Optional strWhere As String = "") As Variant
On Error GoTo Err_DBMax
　　Dim N
　　Dim strQuerySQL As String
　　Dim rst　　　　 As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　strQuerySQL = "SELECT MAX(" & strField & ") FROM " & strTable
　　If Len(strWhere) > 0 Then
　　　　strQuerySQL = strQuerySQL & " WHERE " & strWhere
　　End If
　　With rst
　　　　 .Open strQuerySQL, _
　　　　　　　 CurrentProject.Connection, _
　　　　　　　 adOpenStatic, _
　　　　　　　 adLockReadOnly
　　　　 If Not .BOF Then
　　　　　　 .MoveFirst
　　　　　　 N = Nz(.Fields(0), 0)
　　　　 End If
　　End With
Exit_DBMax:
On Error Resume Next
　　 rst.Close
　　 Set rst = Nothing
　　 DBMax = N
　　 Exit Function
Err_DBMax:
　　 MsgBox "SELECT 文の実行時にエラーが発生しました。(DBMax)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBMax
End Function

回答No.1

bonaron

顧客ID、納入先コードがテキスト型だとして、

(1) DMax("納入先コード","納入先テーブル","顧客ID='" & [顧客ID] & "'")
　　を求める
(2) (1)の値が Nullだったら "0" に変換
(3) 文字列から数値に変換
(4) １を加算する。
(5) ２桁の文字列に変換。

以上のことは、書こうと思えば１つの式で書けます。
デバッグが大変なので、
途中の経過を変数に代入するほうがお薦めですが。
（特に初心者の場合には）

ヒント
(1) もし数値型なら 「'」はいらない。
(2) Nz関数
(3) "02" + 1 は、数値の 3 になるので実際は不要。
　　（暗黙の型変換です）
(4) Format(数値,"00")
　　もし数値型なら 処理不要