Access 文字＋年ごとの自動採番

Accessは全くの初心者です。
プログラマーさんのサイトや質問サイトを参考に、初めてデータベースを作っているのですが、
どうしても文字＋年ごとの自動採番がうまくいきません。

挿入前処理で「営250001」のように漢字1文字＋和暦2桁＋4桁連番にして、
年が変わるごとに4桁連番を0001に戻したいのです。

今入力してあるコードは以下の通りです。

-----------------------------------------------------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)

　Dim vDt As Variant

　vDt = DMax("番号", "営テーブル", "番号 Like '" & Format(Date, "ee") & "*'")
　If (IsNull(vDt)) Then
　　番号 = "営" & Format(Date, "ee") & "0001"
　Else
　　番号 = "営" & Left(vDt, 2) & Format(Val(Right(vDt, 4)) + 1, "0000")
　End If

End Sub
-----------------------------------------------------------------------------
この状態では、0001のまま採番が進まず、行き詰ってしまいました。

他の質問者さんが(Date,"ee")を(Now,"nn")に置き換えて、分ごとの採番を試していたのを参考に、
「"営"&」を取って、採番が進むところまでは確認できました。

使っているのはAccess2003です。

どうか、よろしくお願いします。

No.1 ベストアンサー 回答者： m3_maki 回答日時： 2013/08/26 13:17

> vDt = DMax("番号", "営テーブル", "番号 Like '" & Format(Date, "ee") & "*'")

これでは、先頭の 「漢字1文字」 が無視されていますね。

vDt = DMax("番号", "営テーブル", "番号 Like '営" & Format(Date, "ee") & "*'")

とか、

vDt = DMax("番号", "営テーブル", "番号 Like '?" & Format(Date, "ee") & "*'")

さらに
> 番号 = "営" & Left(vDt, 2) & Format(Val(Right(vDt, 4)) + 1, "0000")
を

番号 = Left(vDt, 3) & Format(Val(Right(vDt, 4)) + 1, "0000")


上記は
"営テーブル"の "番号"フィールドには , 「営250001」 のように保存されているものとします。
１桁目は"営"固定の場合です。
そうでない場合は、また変わってきます。

この回答へのお礼

できました！
vDtで定義した中にも1桁目の漢字が入らなければいけなかったんですね…
「"番号 Like '"」の部分が理解できていなかったので、
どこに1桁目の漢字を定義していいのかがわかっていませんでした。
教えていただいたとおりに直し、あらためてヘルプ等でLike演算子を調べ、
なんとなくですが、理解できた気がします。
テーブルごとに1桁目の漢字決め、固定して使うので教えていただいたコードで
進めたいと思います。

ありがとうございました。

お礼日時：2013/08/26 23:18

No.3 回答者： nicotinism 回答日時： 2013/08/26 15:55

主キーに複数の意味を持たせるな

http://hatenachips.blog34.fc2.com/blog-entry-201 …

絶対に・・・というわけではないと考えますが
人間にとって分かりやすい ≒ データベースの処理に適切
だと思います。

この回答へのお礼

作成中、いろいろ検索した中で、リンクしていただいたページも見てました。
たしかに、このままでは主キーとしては不安定ですよね。
この自動採番は、以前から使用していた番号の形式を使い、
何かの時にはこの番号で検索もしたいとのことだったので、
この番号以外に、オートナンバーの主キーを設定しています。
教えていただき、ありがとうございました。

お礼日時：2013/08/27 00:14

No.2 回答者： 30246kiku 回答日時： 2013/08/26 14:28

以下でどうなりますか

Private Sub Form_BeforeInsert(Cancel As Integer)
　　Dim vDt As Variant
　　Dim sS As String

　　sS = "営" & Format(Date, "ee")
　　vDt = DMax("番号", "営テーブル", "番号 Like '" & sS & "*'")
　　If (IsNull(vDt)) Then
　　　　番号 = sS & "0001"
　　Else
　　　　番号 = sS & Format(Val(Right(vDt, 4)) + 1, "0000")
　　End If
End Sub

※ "営25*" で探すので、"事250123" とかあったら、
　"営" / "事" それぞれでの採番になると思います。

※ "営25" を作るところは１箇所にします。
　ほぼないと思いますが、前後で年を越したら・・・・
　もしかして　http://oshiete.goo.ne.jp/qa/5749336.html　とかを見られました？
　そこでは２箇所に記述していましたが・・・


なお、以下のような書き方もあるかも（未検証）
（DMax の戻り値の時に数値にしておく）

Private Sub Form_BeforeInsert(Cancel As Integer)
　　Dim sS As String

　　sS = "営" & Format(Date, "ee")
　　番号 = sS & Format( _
　　　　　　　　　　Nz( _
　　　　　　　　　　　　DMax("Val(Right(番号, 4))" _
　　　　　　　　　　　　　　, "営テーブル" _
　　　　　　　　　　　　　　, "番号 Like '" & sS & "*'") _
　　　　　　　　　　) + 1 _
　　　　　　　　, "0000")
End Sub


余談）

挿入前処理での採番ですが、更新前処理で採番する事もあると思います。
タイミング的にどちらが良いのか、それは判断してください。
その時には、新規レコードか判別して・・・・以下雰囲気で

Private Sub Form_BeforeUpdate(Cancel As Integer)
　　Dim vDt As Variant
　　Dim sS As String

　　If (Me.NewRecord) Then
　　　　sS = "営" & Format(Date, "ee")
　　　　vDt = DMax("番号", "営テーブル", "番号 Like '" & sS & "*'")
　　　　If (IsNull(vDt)) Then
　　　　　　番号 = sS & "0001"
　　　　Else
　　　　　　番号 = sS & Format(Val(Right(vDt, 4)) + 1, "0000")
　　　　End If
　　End If
End Sub


不都合あれば修正してください。

この回答へのお礼

そうです！
その質問ページを参考に、今の自動採番を作っていました。
30246kiku様が丁寧に回答されていたので、全くの初心者の私にも
だいぶ理解できるものでした。
私の質問にも回答いただけて、なんかちょっと嬉しい気が…(^^)

本題ですが、
「漢字＋年」と「連番」で分けるという方法もあるんですね。
見た目がシンプルで、わかりやすいかも…
肝心の動作確認ですが、実はまだできてません^^;
職場へのデータの持込・持出厳禁なのと、自宅PCにAccessが入っていないのとで、
仕事の合間に作っている現状なのです。
とりあえずなお礼で申し訳ありませんが、この方法の動作確認ができたら、
最初の方法とどちらがいいか、検討したいと思います。

ちなみに、未検証で書いていただいたコードは、
すいません！Nz関数を調べてみても、初心者すぎる私には理解が難しく。。。
処理のタイミングも、どこか入力し始めたら番号が出るようにしたいので、
挿入前処理で進めたいと思います。

たくさんの回答例をいただき、ありがとうございました。

お礼日時：2013/08/27 00:00