時間を10分きざみに丸めるVBA。Accessで使えるCeiling関数を使わない方法。

先日、美容院用顧客管理システムで予約時間をボタンひとつで、10分単位の時間で入力されるようにしました。この時、参照設定でExcel関数を読み込み、ExcelではよくつかわれるCeiling関数をつかったわけですが・・・。

作ったアプリをベータテストしてくれているアロマサロンさんで試してもらったところ思わぬ落とし穴。AccessRuntimeで動作させているPCにExcelがインストールされていなかったのです。

Excel関数つかえないのでINT関数を代わりに使う。

というわけで、代替案。INTを使います。INTはご存じの通り引数に小数が入ると、整数を返します。日付型は24時間を1と表し、時刻は小数として表します。試しにエクセルで表示させてみました。

見ての通り、0:00はゼロ。10分加えるごとに0.0069444444となります。この数字は、ほぼ1/144(144分の1)です。同じ数で割ると1になります。(ほぼ)

こんどは0:10から1分ごとの変化です。1/144で割った値を比べると、1分増えるごとに0.1増えていることがわかります。0:20になった時点で2に繰り上がっています。

INT関数は小数点以下を切り捨てて整数にしますから、0.1ずつ増える部分は全部無視されて、10分刻みの整数だけが残りました。

ひとつ前の画像のように割った数字をもう一度かけると元に戻ります。1分単位の部分は切り捨てているので、もちろん1分毎で変化はありません。これをコードで表すと以下の通り。

Private Function intまるめ(t)
    intまるめ = (Int(t / (1 / 144)) * (1 / 144)) 
End Function

このままだと切捨てなので、切り上げにするには10分を表す1/144を足してみました。もうお気づきでしょうが、大体大丈夫なのですが、0分ちょうどだと、10分進みます。うーん。

まあいっか

Private Sub btn_excel_Click()
    Me.txt_marume = excelまるめ(Me.txt_moto)
End Sub

Private Sub btn_intmarume_Click()
    Me.txt_marume = intまるめ(Me.txt_moto)
End Sub

Private Function excelまるめ(t)
     excelまるめ = Excel.Application.Ceiling(t, 1 / 144)
End Function

Private Function intまるめ(t)
    intまるめ = (Int(t / (1 / 144)) * (1 / 144)) + (1 / 144)
End Function

今回の用途の場合、0分単位で10分進んでしまうことは特に大きな問題でなかったのでこのまま使っちゃいました。Accessで以下のように簡単なテストを作って比べてみると、やはり10分単位ぴったりの時は差がでますがご愛敬。というわけで、いま焦って脱Excel参照設定を進めてます。ENCODEURL関数も使っちゃっているので、道のりは長い・・。

誤差はいろいろ出てきそうな方法ですが、アバウトな用途でどうぞ。

MSAccessでボタンで入力する現在時刻を10分単位でまるめてすっきりするVBA。

ご予約登録画面で時間の入力を簡単にするためにボタンをつけるってことはあると思うのですが、単純に現在時刻だとどうにも不格好です。そこで、時刻をまるめられないかと探していたらこちらのサイトで方法が紹介されていました。

Excelでお仕事! http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_050_030.html

エクセルのFloor関数で切り下げ、Ceiling関数で切り下げ。その際に10分刻みなら10分=1/144日、15分=1/96日 というのを利用すれば実現できそうです。

AccessでもExcelの関数を参照設定でExcelのオブジェクトライブラリを選択すれば使えます。早速以下のようにしてました。まるめFuncitonで10分単位の切り上げが実現しました。

Private Sub btn_from1hourAfter_Click()
    Me.予約時刻 = まるめ(DateAdd("h", 1, Time()))
End Sub

Private Sub btn_from2hourAfter_Click()
    Me.予約時刻 = まるめ(DateAdd("h", 2, Time()))
End Sub

Private Sub btn_FromNow_Click()
    Me.予約時刻 = まるめ(Time())
End Sub

Private Function まるめ(t)
    まるめ = Excel.Application.Ceiling(t, 1 / 144)
End Function

1/144のところはコンボボックスなどで差し替えれば、15分単位などにもできますし、切り上げ、切り下げをどちらにするかもオプション画面つくってユーザーが選択できるようにしてもよいかもしれません。

Accessで指定したURLを既定のブラウザで開くVBA。

AccessはIEを使ってWEBページを開くことは簡単にできますが、Chromeなど指定したブラウザで開くのにいい方法がおもいつかず。ChromeのPATHを書いて引数で開くなどしていましたが、画期的な数行のコードをみつけたので自分のメモとして書いておきます。

紹介してくれたのはこちらのサイトです。

513号室 http://bardiel-of-may.blogspot.com/2012/06/url.html

Sub testOpenUrl()
    Dim objWSH As Object
    Dim URL As String
    
    URL = "https://mocabrown.com/blog"
    
    Set objWSH = CreateObject("WScript.Shell")
    objWSH.Run URL, 1
End Sub

.Run “notepad.exe” のような感じでアプリを起動できるのは知っていたが、URLを渡すと既定のブラウザで開くってのは魔法のよう。情報ありがとうございます。

Accessでクエリは破損していますと出てしまう対策について_2

前記事ではウイザードで作った更新クエリの場合について紹介しましたが、こちらでは、VBAで「クエリは破損しています」と出てしまった場合の一時的な回避方法を実例で紹介します。

先ほどのテーブルを利用して、簡単な人事異動アプリをフォームで作ってみました。見たまんまですが、社員番号であるIDと移動先部署を入力して「更新」を押すと、社員テーブルの該当するIDの部署が更新されます。メッセージボックスで表示されているのが、作成されるSQL文字列です。

クエリが破損するAccess

実行すると・・・・でました。クエリは破損しています。

クエリは破損しています対策

デバッグを押して問題個所を見てみましょう。 テキストボックスを参照しながら変数に代入したSQL文をDoCmd.RunSQL で実行しようとしたときにエラーが出ています。この時に参照しているのは、UPDATEの後ろにある「社員」という名前のテーブルです。

SQL実行でエラーになっちゃう
ビフォー

そこでこの「社員」テーブルを、まるまる参照したクエリを作成して、文字列にはそのクエリに対してUPDATEするようにします。

クエリの作り方は前記事であるこちらを参考にしてください。

クエリが作れたら、SQL文のテーブルの部分を書き換えます。WHEREなどでもテーブルを指定している場合はそちらもクエリの名前に差し替えるとよいでしょう。

テーブルをクエリに差し替えた
アフター 社員という名前のテーブルを、新たに作ったwao_社員 クエリに書き換えた。

メッセージボックスに表示されたのは書き換え後のSQL文です。 さて、こんどはどうでしょう?

仮で名前をつけたクエリを参照している

無事に田中さんは「すぐやる課」へ人事異動できました。

更新クエリできた

同じように、更新に失敗する箇所で「デバッグ」を押して、実行しようとしているSQL文のテーブル部分をまるごと参照するクエリを作り、SQLの名前を差し替え続けると、とりあえず動く対策アプリを用意できると思います。

対策方法についてはマイクロソフトの本家ISSUEに公開されています。https://support.office.com/en-us/article/access-error-query-is-corrupt-fad205a5-9fd4-49f1-be83-f21636caedec

Microsoftの対策済み更新予定は?

こちらの アザースblog さんによれば、2019年11月24日予定とのこと。

Accessのクエリは破損していますエラーは[自動更新を個別に停止するツール]で防げる

Microsoftの更新予定は、パソコンドック24さんの紹介によれば、2019年12月10日のようです。 https://www.pcdock24.com/blog/?p=43320