ACCESSで、DMax関数の条件の書き方

質問

ACCESS2002を使用しています。
自動的に連番を採番するコードを書きましたが、DMax関数でうまく最大値を取得できません。
条件式がうまく書けていないと思うのですが、どうぞお知恵をお貸しください。

テーブル[t_受注]にあるフィールド[受注コード]は、「yyyymm000」のように9桁表示です。
「yyyymm」の部分は現在の年月から取得し、「000」の部分は年月ごとに001～連番を採番しています。
しかし年月が変わるとうまく最大値を取得できず、次のように連番が採番されてしまいました。

[受注コード]
200705001
200705002
200705003
200705004
200706001
200706005(本当は200706002と自動採番したいのです)

最大値(MaxID)取得するコードはこのように書いております。
よろしくお願いします。
-------------------------------------------------------------------------------------------
Dim NowYearMonth As String
Dim MaxYearMonth As String
Dim MaxID As String

NowYearMonth = Format(Date, "yyyymm")
MaxYearMonth = Nz(DMax("Mid$([受注コード],1,6)", "t_受注"), "")
MaxID = Nz(DMax("Mid$([受注コード],7,3)", "t_受注"), "NowYearMonth=MaxYearMonth")
-------------------------------------------------------------------------------------------

回答No.2

x0000x · ベストアンサー

今晩は。
1>MaxYearMonth = Nz(DMax("Mid$([受注コード],1,6)", "t_受注"), "")
2>MaxID = Nz(DMax("Mid$([受注コード],7,3)", "t_受注"), "NowYearMonth=MaxYearMonth")

1>では、格納中の最大年月を取得し、
2>では、格納中の最大番号(年月を除いた中での最大）しかもとまりません。
1>と2>の関連が無いのです。

以下で如何でしょう。
※"[受注コード]"は文字列フィールドとして定義しています。

Dim nowMax As Variant
Dim newMax As String
Dim NowYearMonth As String
NowYearMonth = Format(Date, "yyyymm")

nowMax = Nz(DMax("[受注コード]", "t_受注", "Mid([受注コード],1,6)='" & NowYearMonth & "'"), "")
If nowMax = "" Then
newMax = NowYearMonth & "001"
Else
newMax = NowYearMonth & Format(Val(Mid$(nowMax, 7, 3)) + 1, "000")
End If

お礼

私のコードの何が間違っているのかの説明までして頂き、よく理解することができました。
そもそも連番部分のみの最大値を取得しなくてもよいのですね。
サンプルで試してみたところ、思い通りの結果となりました！
数時間悩んでいたものが、あっという間に解決でき嬉しいです。
回答本当にありがとうございました。

補足

nowMaxはStringではなくVariantにすべきでしょうか？

回答No.3

x0000x

こんにちは。＃２です。

>nowMaxはStringではなくVariantにすべきでしょうか？

YYYYMMnnnの数値のみであること。
nz(Dmax(),"")の様にNULL値を補正しているのであれば
Stringで問題ないです。

※ご提示したコードのNZ()未対応の際にVariantとした名残でした。

お礼

なるほど。理由もよく理解することができました。今回はStringでいきたいと思います。
補足への回答ありがとうございました。

回答No.1

AlexSuns

うーん、試してないですけど下記じゃないですかね？

×MaxID = Nz(DMax("Mid$([受注コード],7,3)", "t_受注"), "NowYearMonth=MaxYearMonth")
↓
○MaxID = Nz(DMax("Mid$([受注コード],7,3)", "t_受注"), "Mid$([受注コード],1,6)=" & MaxYearMonth)

おせっかいですが、DMax関数を使用した簡易的なものを書いてみました
※たぶんできるはず(^^;)

Dim NowMaxID As String
Dim NewMaxID As String
Dim Counter As Integer

NowMaxID = Nz(DMax("受注コード", t_受注), "")
NewMaxID = Format(Date, "yyyymm")
Counter = 0
If NowMaxID <> "" Then
　　If Left(NowMaxID, 6) = Format(Date, "yyyymm") Then
　　　　Counter = CInt(Mid(NowMaxID, 7, 3))
　　End If
End If
NewMaxID = NewMaxID & Format(Counter + 1, "000")

お礼

DMax関数での条件式ばかりで考えていたので、IF文でも記述できることに気がつきませんでした。
教えて頂いたサンプルで試してみたところ、思い通りの結果が出てきました！
回答本当にありがとうございました。