AccessVBA SQLでのNULL置換

Question

前提・実現したいこと

AccessVBAのSQL文の中で安値予想という項目で1日前のデータを参照しているのですが、日曜日が休みになるので、空欄ができます。この時は3日前のデータを参照したいです。
月曜日の予想は、金曜日の安値結果から

発生している問題・エラーメッセージ

SQLの４行目にあたる部分です。
ISNULL関数はエラー
Nz関数は余計に空欄が発生してしまい、うまく表示できない状態です。

該当のソースコード

Private Sub todays_Click()

    Dim strSQL As String
    strSQL = "INSERT INTO 本日の予想と結果(" & _
             "日付け,安値予想,高値予想,安値結果,高値結果,差,勢い) " & _
             "SELECT T1.日付け," & _
             "ISNULL((Cint(T2.安値*10*0.99) / 10),(Cint(T3.安値*10*0.99) / 10))) AS 安値予想, " & _
             "(Cint(T2.高値*10*1.01) / 10) AS 高値予想, " & _
             "T1.安値 AS 安値結果, " & _
             "T1.高値 AS 高値結果, " & _
             "(T1.高値 - T1.安値) AS 差, " & _
             "SWITCH(" & _
                " 差 >= 3 , '高' " & _
                ",差 >= 2 , '中' " & _
                ",差 >= 1 , '並' " & _
                ",差 < 1 ,'低' " & _
                ") AS 勢い " & _
             "FROM (ドル円 AS T1 LEFT JOIN ドル円 AS T2 " & _
                "ON T1.日付け = (T2.日付け + 1)) " & _
                "LEFT JOIN ドル円 AS T3 " & _
                "ON T1.日付け = (T3.日付け + 3)"
             
    CurrentDb.Execute (strSQL)

End Sub

試したこと

ISNULL関数、Nz関数を使っての確認

検索をかけるも、他のDBでは使えるISNULL関数もACCESSでは使えない？という情報も。
書き方がわからず、何が正しいのかわからなくなったのでこちらで質問させていただきました。
こちらをうまく表示するにはACCESSのSQLではどのように書いたらよいでしょうか？

詳しい方いらっしゃいましたら、ご教示ください。
よろしくお願いいたします。

それと、結合するときに前日のデータを参照するのですが、
LEFT JOIN の部分で T1.日付け -1 ではなくて、+1をすると標的には正しい状態になるのですがこちらについてわかればこちらについても教えていただきたいです。

よろしくお願いいたします。

Comments

[hatena19]

祝日や年末年始は考慮しなくていいのですか。

[ataru2222]

hatena様

まだAccess初めて経験が浅い為、データ分析というよりはAccessの使い方、NULLでの条件分岐を知りたいという想いが強い為、現状では祝日や年末年始については大丈夫です。

ありがとうございます。

[hatena19]

もし、今後、祝日や年末年始も考慮しようとしたとき、NUllなら三日前というロジックでは対応できなくなります。本来なら、自身の日付けより前のレコードで最大の日付けのレコードを取得するというロジックにすべきかと思います。これなら、日曜、祝日、年末年始で日付が連続していない場合でも対応可能です。

[ataru2222]

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

ちなみにどのように前のレコードを参照したら良いでしょうか？SQLは縦の情報の取得が非常に理解しにくい状態です。
日付けなら固定値なので取得できるのは分かるのですがその他の値は変動しているので参照できません。
ID的な項目を作ってそれを連結するという感じでしょうか？

Answer 1

修正依頼のコメントより

NULLでの条件分岐を知りたいという想いが強い為、

現状では祝日や年末年始については大丈夫です。

とのことなので、とりあえず、

  "ISNULL((Cint(T2.安値*10*0.99) / 10),(Cint(T3.安値*10*0.99) / 10))) AS 安値予想, " & _

上のコードを下記に修正してみてください。

   "Nz((Cint(T2.安値*10*0.99) / 10),(Cint(T3.安値*10*0.99) / 10)) AS 安値予想, " & _

あるいは、

   "Cint(Nz(T2.安値,T3.安値)*10*0.99) / 10 AS 安値予想, " & _

追記

修正依頼のコメントより

ちなみにどのように前のレコードを参照したら良いでしょうか？SQLは縦の情報の取得が非常に理解しにくい状態です。

日付けなら固定値なので取得できるのは分かるのですがその他の値は変動しているので参照できません。
ID的な項目を作ってそれを連結するという感じでしょうか？

いろいろな方法がありますが、自身よりの前の日付の最大値のレコードというロジックでSQLを組むと下記のような感じです。（予想値の計算等は省略してます。

SELECT T2.*, T3.安値, T3.高値 FROM 
 (SELECT T1.*, 
  (SELECT Max(日付け) FROM ドル円 WHERE 日付け < T1.日付け) AS 直近日
   FROM ドル円 AS T1) AS T2
 LEFT JOIN ドル円 AS T3
 ON T2.直近日 = T3.日付け;

ご推察の通りにSQLは縦の情報の取得は苦手で、複雑かつ重い処理になりがちです。
このような場合は、DAOかADOでレコードセットを取得して、レコード移動しながら処理するとシンプルかつ高速になります。

コード例

Dim rs1 As Dao.Recordset
Set rs1 = CurrentDB.OpenRecordSet("SELECT * FROM ドル円 ORDER BY 日付け;")

Dim rs2 As Dao.Recordset
Set rs2 = CurrentDB.OpenRecordSet("本日の予想と結果")

Dim 直近安値, 直近高値
Do Until rs1.EOF    
    rs2.AddNew
    rs2!日付け = rs1!日付け
    rs2!安値結果 = rs1!安値
    rs2!高値結果 = rs1!高値
    rs2!安値予想 = Cint(直近安値*10*1.01) / 10
    rs2!高値予想 = Cint(直近高値*10*1.01) / 10
    rs2.Update
    直近安値 = Nz(rs1!安値)
    直近高値 = Nz(rs1!高値)
    rs1.MoveNext
Loop

Comments

[ataru2222]

hatena19様

ありがとうございます。
今職場なので帰宅したら確認してみます。

[sazi]

@ataru2222さん
hatena19さんが示したような、縦の処理に該当する分析関数と言われるものが用意されていて、
SQLのみで高速に処理できます。
但し、残念ながらAccessには分析関数は実装されていませんので、余談ですけど。

[ataru2222]

hatena19様

"Cint(Nz(T2.安値,T3.安値)*10*0.99) / 10 AS 安値予想, " & _
こちらでばっちりでした。

やはりサブクエリを使うと処理が重くなるのですね。
これから計算式をもっと複雑にしていこうと考えており、SQLでおこなうと大変なことになりそうなのでレコードセットしていく方法に切り替えていこうと思います。

教えていただきまして、本当にありがとうございました。

[ataru2222]

sazi様
いつもありがとうございます。

高価なDBと無料のDBではこういう所に差が出てきていて、スピード差としては天と地ほどの違いが生まれてくるということですね。

そういう大枠な部分も入門者の知識は乏しいので、非常に勉強になりました。
今はAccessDBのみ使っておりますが、少しづつ使えるDBの種類を増やしていこうと思います。
ありがとうございました。

Answer 2

使い方が分からないなら、マニュアルを確認しましょう。

• Microsoftサポート IsNull 関数
• Microsoftサポート Nz 関数
• Access クエリで抽出条件として日付を使用する例

そもそも、T2.安値の値自体がnullならT2.安値*10*0.99の計算すらできないように思えますが。。。？

Comments

[ataru2222]

kaputaros様
回答ありがとうございます。

すみません。
まだSQL自体の経験が浅い為、テーブル結合を使ったりするとどこで間違っているのか？が分からない状態です。

極力調べた上で質問するようにはしておりますが、現状の自分のレベルの目線では公式ドキュメントを読んでも理解できない事の方が多いです。
ご理解くださいませ。