Accessのテーブル内に値を入れる方法を知りたい

Question

お世話になっております。

こんな質問は本当に情けないのですが
とっかかりが不明なのでお問い合わせ失礼します。

テーブルを上から見ていって
もし項目「購入日」が空だったら
本日の日付を入れて項目「シリアルNO」を返す
との処理をしたいと思っております。

csvで表記すると下記のようなテーブルで

管理ID,シリアルNO,購入日
123456,1111111111,2018/10/01
123457,2222222222,2018/10/03
123458,3333333333,2018/10/08
123459,4444444444,2018/10/26
123460,5555555555,
123461,6666666666,

上記の場合

sub writePurchaseSerialDay("TBL_SERIAL")
：
：
：
End Sub

で表記出来るのでしょうか？

初心者の質問大変申し訳無いのですが上記お問い合わせいたします。

本当に情けないのですがとっかかりが不明なため
どのような検索すればよろしいのかお教え頂けますと嬉しく思います。

【【【追記】】】

Private Sub コマンド86_Click()
    Call print_sirial("TBL_SERIALNO", "serialno", "TBL_SERIAL", "RPT_SERIALNO", "QRY_SERIAL1")
End Sub

上記で呼んでいる

Public Sub print_sirial(tbl_name As String, teigi As String, csv_name As String, rpt_name As String, qry_name As String)
    'TBL_SERIALを開いて上から見て顧客IDが空なヤツに
    '顧客ID、購入日、を入れる。
    Dim rData As String
    rData = writePurchaseSerialDay()
    MsgBox rData
End Sub

上記で呼んでいる

Function writePurchaseSerialDay() As String
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Set DB = CurrentDb
    Set RS = DB.OpenRecordset("SELECT TOP 1 シリアルNO, 購入日 FROM TBL_SERIAL " & _
                              "WHERE 購入日 IS NULL ORDER BY 管理番号;")

    If RS.EOF Then
        writePurchaseSerialDay = ""
    Else
        RS.Edit
        RS!購入日 = Date
        RS.Update
        writePurchaseSerialDay = RS!ｼﾘｱﾙNO
    End If

    RS.Clone: Set RS = Nothing
    Set DB = Nothing
End Function

で構文エラーとなってしまうのです……
writePurchaseSerialDay = RS!ｼﾘｱﾙNO
にフォーカスが当たって……

以上追記します。

Comments

[sazi]

「シリアルNO」を返す　というのは返されたシリアルNOをどうしたいのですか？

[moitaro]

return シリアルテーブル.そのシリアル番号　　と返してその値を渡せればと思っております。

[sazi]

複数のデータを読んで複数のデータを返却するんですか？またそれをどう利用するのですか？

[seastar3]

質問文からは先頭１レコードのみとは読み取れないですね。厳密に説明していただいていれば、そのような処理を示せたのですが。

[moitaro]

申し訳ございません。おっしゃる通り先頭1レコードだけと読めなくて大変失礼しました。

Answer 1

クエリの基本をマスターしましょう。

最大・最小を求めるクエリ

Accessで更新クエリを作成する方法

そして、最小NOの条件で日付を更新するクエリを作れば完成です。
基本が分かれは、さらに便利に使えます。頑張って下さい。

Comments

[moitaro]

seastar3様
ありがとうございます。

Accessで更新クエリを作成する方法の方で
```
デザイングリッドに[レコードの更新]行が表示された。
```
の部分が出来なくて不明ですが
情報ありがとうございます。
私が使用しているのはAccess2013なのです……

Answer 2

もう一つの更新クエリを紹介しておきます。最初は目論見のシリアルＮＯを検出するクエリを示すのを忘れておりまして、その点を補足した訂正判の回答です。
まず、Min関数を使って日付がnullの先頭のシリアルＮＯを検出するクエリ 日付空欄先頭シリアルＮＯ選択クエリ を用意します。

日付空欄先頭シリアルＮＯ選択クエリ

SELECT Min([シリアルＮＯ]) AS 指定シリアルＮＯ FROM 購入記録テーブル WHERE [購入記録テーブル].購入日 Is Null;

続いて上記の 日付空欄先頭シリアルＮＯ選択クエリ を１隊多の結合を組み込んで 日付空欄先頭更新クエリ２ を用意します。

日付空欄先頭更新クエリ２

UPDATE 購入記録テーブル LEFT JOIN 日付空欄先頭シリアルＮＯ選択クエリ ON 購入記録テーブル.[シリアルNO] = 日付空欄先頭シリアルＮＯ選択クエリ.指定シリアルＮＯ SET 購入記録テーブル.購入日 = Date();

これも検証済みですので、空のクエリにSQLビューで貼り付けて確かめてみて下さい。もちろんテーブル名は対応したものにしましょう。
LEFT JOIN がでてくるので、難しいと思い、回答を控えておりました。動いたらデザインピューで表示してみるとＥ－Ｒ図とフィルタの形式がわかるでしょう。
SQLを勉強していけば、リレーショナルデータベースの有効性が分かってきますから、徐々に勉強していきましょう。

Comments

[moitaro]

Seastar3様
度々ありがとうございます。
```
JOIN操作の構文エラーです。
```
と表示されていましたがシリアルNOのNOが半角としていました……

これをフォームのボタンが押されたら動作するようにしてみますっ
ありがとうございました。

[moitaro]

と思っていましたが
'日付空欄先頭シリアルNO選択クエリ'が見つかりませんでした。

で上手く動作させる事は出来ませんでした……

[seastar3]

これは胡乱なことで申し訳ありません。リレーションシップ先の"日付空欄先頭シリアルＮＯ選択クエリ "を示していませんでしたね。これは、私の２つ目の回答の２つ目のSQL文です。テーブル名とクエリ名を正確に指定した上で当てはめて下さい。

[moitaro]

デザインビューで
日付空欄先頭シリアルＮＯ選択クエリ
の所を
SELECT Min([シリアルＮＯ]) AS 指定シリアルＮＯ FROM 購入記録テーブル WHERE [購入記録テーブル].購入日 Is Null)
とした所
UPDATEステートメントの構文エラーです……
と出てSELECTが悪いのかと思って削除したら
カッコで
UPDATEステートメントの構文エラーです……
と出てしまいました……

私のSQL操作が下手過ぎで申し訳ございません……

[moitaro]

UPDATE TBL_SERIAL LEFT JOIN Min([シリアルNO]) AS 指定シリアルNO FROM TBL_SERIAL WHERE [購入記録テーブル].購入日 Is Null) ON TBL_SERIAL.[シリアルNO] = Min([シリアルNO]) AS 指定シリアルNO FROM TBL_SERIAL WHERE [購入記録テーブル].購入日 Is Null).指定シリアルNO SET TBL_SERIAL.購入日 = Date();　としているのです……

[seastar3]

私の二つの回答を混ぜていますね。別々の名前で更新クエリを作ってみてください。日付空欄先頭シリアルＮＯ選択クエリはそのままでいいです。

[moitaro]

seastar3様
度々ありがとうございます。
```日付空欄先頭シリアルＮＯ選択クエリ
SELECT Min([シリアルNO]) AS 指定シリアルNO FROM TBL_SERIAL WHERE [TBL_SERIAL].購入日 Is Null
```
として
```
UPDATE TBL_SERIAL LEFT JOIN 日付空欄先頭シリアルNO選択クエリ ON TBL_SERIAL.[シリアルNO] = 日付空欄先頭シリアルNO選択クエリ.指定シリアルNO SET TBL_SERIAL.購入日 = Date();
```
としたところ
開くと
このクエリのテーブルっぽい所に購入日が2018/10/29
と入ってしまい
TBL_SERIALに入りませんでした……
VBA勉強してそちらでやった方が良いのかな……と迷い中ですが
現在の状況をご連絡いたします。

Answer 3

購入日が空欄のシリアルＮＯの先頭のレコードだけ日付を入れるクエリは、

UPDATE 購入記録テーブル SET [購入記録テーブル].購入日 = Date() where シリアルＮＯ = (SELECT Min([シリアルＮＯ])  FROM 購入記録テーブル WHERE [購入記録テーブル].購入日 Is Null);

です。

補足として日付が空欄の先頭レコードのシリアルＮＯを求める選択クエリは、

SELECT Min([シリアルＮＯ]) AS 指定シリアルＮＯ  FROM 購入記録テーブル WHERE [購入記録テーブル].購入日 Is Null)

です。シリアルＮＯフィールドの代わりに、管理IDフィールドを当てはめても矛盾しないでしょう。

Comments

[hatena19]

Accessでは、サブクエリを含むクエリは更新できないので、その UPDATE文は実行できないはず。
日付が空欄の先頭レコードのシリアルＮＯを求めるSQLから取得したシリアルＮＯで抽出して更新する UPDATE文にすればいいでしょう。

[seastar3]

hatena19さん、検証済みですよ。私はよくSQLビューでリレーショナルデータベースの仕組みを説明したりしているのですが、意外と融通が利くものです。VBSよりシンプルでしょう。

[hatena19]

私の勘違いだったようです。実行できますね。

[seastar3]

なお、チューニングの観点から言えば、シリアルＮＯフィールドにインデックス設定をしていないとしたら、管理IDフィールドで最小値を採った方が高速になります。

Answer 4

購入日のNullが複数あるときは、上からみて最初（管理IDが最小）のレコードのみ現在の日付を入力して、
そのシリアルNOを返すということでしょうか。
「シリアルNOを返す」とはFunction（関数）の戻り値として返すということでよろしいですか。

その場合、SQLでは面倒ですので、VBA（DAO）で操作した方が楽でしょう。

Function writePurchaseSerialDay() As String
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Set DB = CurrentDb
    Set RS = DB.OpenRecordset("SELECT TOP 1 シリアルNO, 購入日 FROM テーブル名 " & _
                              "WHERE 購入日 IS NULL ORDER BY 管理ID;")

    If RS.EOF Then
        writePurchaseSerialDay = ""
    Else
        RS.Edit
        RS!購入日 = Date
        RS.Update
        writePurchaseSerialDay = RS!シリアルNO
    End If
    
    RS.Clone: Set RS = Nothing
    Set DB = Nothing
End Function

「シリアルNO」のデータ型はテキスト型と仮定してます。
また、「購入日」がNullのレコードがない場合は、"" (空文字列)を返す仕様にしてます。

Comments

[seastar3]

こちらのコードもチューニングの観点から言えば、最小の管理ＩＤのレコードを選択して日付を埋める手順にすれば、メモリー的にも速度的にも効率化が図れますね。

[hatena19]

ですね。
その点を考慮してコード修正してみました。

[moitaro]

hatena19様
ありがとうございます。
SQLよりVBAの方が慣れているのでこちらで実装しようとしているのですが
```
writePurchaseSerialDay = RS!シリアルNO
```
の部分で
コンパイルエラー：　構文エラー
と表示されます……

TBL_SERIALは
管理ID,シリアルNO,購入日
とあるのに
RSには購入日が無いのですがどのようにして確認すれば良いのでしょうか？

[hatena19]

回答のコードは実際にサンプルのテーブルを作成して動作確認済みです。
当然、コンパイルエラーにもなりません。
質問者さんの書いたコードが私のものとは違っているのでしょう。
見比べて間違いがないか確認してください。
間違いが分からない場合は、質問を編集して現状のコードを追記してください。

[moitaro]

hatena19様
度々申し訳ございません。
質問に追記をしました。
もう右も左も分からなくて申し訳ございません。
ご助力頂けないでしょうか？

[sazi]

writePurchaseSerialDayの属性はstringなので、シリアルNOの属性も文字型である必要があります。
名称から察すると数値型になってないでしょうか？それだと型変換が必要です。
また、StringだとNullは許容されないので、writePurchaseSerialDayの型はVariantの方が良いのではないかと思います。

[hatena19]

本当に「構文エラー」ですか。提示のコードを当方の環境にコピーしてコンパイルしてみましたが、構文エラーにはなりません。
「シリアルNO」にNullがあるのなら、「Nullの使い方が不正です」という実行時エラーが出る可能性はありますが。ごのコードは、「シリアルNO」がテキスト型、Nullはない、という前提のコードです。
数値型で、Nullがある場合は、saziさんが指摘ささるようにVariant型に変更したほうがいいでしょう。

[moitaro]

コメントありがとうございます。
writePurchaseSerialDay = RS!ｼﾘｱﾙNO
で構文エラーと表示されるのでコメントアウトした上手く行きます……

TBL_SERIAL　をデザインビューで開いた所
シリアルNO　は　短いテキスト　でした。

データ型にはVariant型が無いのですが
これは使用しているAccessが2013と古いからでしょうか？？？
今現在は短いテキスト　としております……

[sazi]

関係ないかもしれませんが
>RS!ｼﾘｱﾙNO
の「ｼﾘｱﾙNO」部分をSQLでの名称をコピーしてしたものにしてみて下さい。
NOが№だったりしてないでしょうか。

[sazi]

Variant型はVBAでの汎用的な型で、相手の型に合わせることができ、NUllを許容します。
テーブルのデザインでは存在しません。

[sazi]

シリアルNOの名称で解決しないなら、可能性としてはNUllになるので関数の型をVariant型にするとエラーは解消するかもしれません。
解消したらしたで、エラーメッセージが違うのは解せないということにはなりますが・・・

[moitaro]

sazi様
コメントありがとうございます。
RS!ｼﾘｱﾙNO
の”シリアル”部分は全角なのですが
何故か半角になってしまうのです……
これはAccessの仕様なのでしょうか？

また
Function writePurchaseSerialDay() As Variant
とした所
出来ましたっっっっ

ありがとうございます。

Answer 5

以下のクエリが基本でしょう。

UPDATE 購入記録テーブル SET [購入記録テーブル].購入日 = Date()
WHERE ((([購入記録テーブル].購入日) Is Null));

Comments

[moitaro]

seastar3様
ありがとうございます。
本当に助かりました。

お手数でなければどのような調査方法で上記のやり方を見つけられたのでしょうか？
やっぱり慣れなのでしょうか？

こんな事も自分で調査出来なかったので
お時間がありましたら上記調査方法をお教え頂けますと嬉しく思います。

[moitaro]

未だにQRY_の作成方法すら分からないのです……

そのためVBAで組もうとしておりました……

[seastar3]

とりあえずACCESS上でこのテーブルをつくり、4件ほどデータを入れてから、実際にデザインビューで更新クエリを作りました。SQLビューで表示すればこの形になります。一応、素でSQL文を打ち込んでも作れる操作です。

[moitaro]

クエリデザインで作成するとの情報ありがとうございます。
頑張ります。

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

[moitaro]

アレ？？？　仰ったSQL文だと全て現在の日付になってしまうので調査中です……

[seastar3]

検証済みですが、クエリデザインの購入日の条件の枠にnullを入れましたか？

[moitaro]

NULLが複数あった場合　その複数全てに購入日が入ってしまうのです……