三流君 ＶＢＡで楽しくプログラミング(Excel/Access VBAの解説/サンプルです)
[VBA系のバックナンバー]
[VBA系 TOP]
[三流君 TOP]
＜Access クエリーで演算フィールドを使ってみる＞
どうも、三流プログラマーのＫｅｎ３です。
今回は、
Accessのクエリーで演算フィールドを使ってみます。
演算フィールドって言っても、
たんなる計算結果のフィールドなんだけど。
/*
* １．今回のキッカケ
*/
http://www.ken3.org/vba/
に設置してた、感想質問受付で、下記の質問が来ました。
---------------
>このたび、犯罪事件の時効完成の色々なデータをアクセスで
>纏めたいと思っています。
>
>※質問の内容なのですが。
>
>テーブルにあるフィールドが２つあります。
>その１つには日付が全部記載されております。（これをAとします）
>もう１つには日付が記載されていたり、無記入だったりします。（これをBとします）
>
>その２つの日付フィールドをクエリに持ってきまして、
>Ｂのフィールドに日付が入っていたら、そのＢの日付を違う新たなフィールドに
>持ってくる。
>Ｂのフィールドに日付が入っていなかったならば、Ａの日付を新たなフィールド
>に持ってくる。
>そして、その新たなフィールドに持ってきた日付を３年後の１日前にする。
>例えば、１９９８年５月２５日を２００１年５月２４日という感じです。
>（時効が三年物の場合です）
>
>きっと、こんなことはKen3さんにかかれば、簡単なのでしょうが、私には
>本を見ても分からず、途方に暮れております。
>
>なにとぞお教え願います。よろしくお願いいたします。
>
>もしよろしかったら、自分のような何も分からない者は、まず最初に
>どのような本を読めば良いのか教えてもらえましたら幸いです。
---------------
日付系の操作
クエリーで項目を作りたい
って感じの質問ですね。
/*
* ２．クエリーの演算フィールドって？
*/
勝手なテーブルをまず作ります、
想像しやすいように、
みなさんが利用しているレンタルビデオ店の会員管理で、
ID
入会日（新規の時セット）
更新日（データを更新した日）
Point (ご利用ポイント)
HFlg (Ｈビデオを借りたか？（うそです、こんな項目ありません）)
名前
住所
電話番号
なんて、項目があった時、
会員には有効期限があって、有効期限は１年です。
有効期限切れ１ヶ月前にハガキを出力したいと思いました。
※今は、経費節減でそんなことしない？
あっ、でも○○カメラからあと１ヶ月でポイントが消滅します、
買い物に来てお金使ってねとハガキ来たっけ。
ポイント４５４円、使いに行くか、捨ててもいいか微妙だった。
そんな話は置いといて、
テーブルには、有効期限の項目がありません。
設計ミスだ！誰だこんな欠陥テーブル作ったのは？
決め付けんなよ三流プログラマー、
オレ様が設計したＤＢだぜ、
ＤＢってのは、計算で求められる項目は普通は持たないんだよ。
有効期限は１年後って決まってんだろ、そしたらＤＢに項目を持つ必要ないね。
あっ、そうなんですか？
※余談：計算で求められる項目、別に持っててもいいとは思うけど、
設計潔癖症のＳＥ／ＰＧと組んで仕事すると、うるさいよね
このへんの話は、長くなるので機会があったらまた今度。。。
クエリーには、
演算フィールドってのがあって
~~~~~~~~~~~~~~
合計:[単価]*[数量]
なんて書き方をすると、テーブルに合計ってフィールドが無くても、
計算結果をクエリーのフィールドとして使えます。
これを演算フィールドと言ってます（で、通じると思います）
ポイントは、
合計と名前を付けて、その後に:とコロンを付け、計算式を書きます。
フィールドは[]と囲って普通の式を書きます
演算フィールド名: 式 [単価]*[数量] （フィールドは[]で囲む）
固定の消費税は怒られるけど、
合計:[単価]*[数量]*1.05
なんて、式なので自由にできます。
↑演算フィールドの設定例。
式が長いと、枠が小さくて書きにくいときあります、
そんな時は、Shift+F2を押すと、別窓でズームした形で入力できます。
※私みたいに長い式をメモ帳に書いて、貼り付けなくてもＯＫです（笑）
↑ズームした例
/*
* ３．日付の計算式を演算フィールドで使う
*/
テーブル内のフィールドの値を使用して、
加工した演算フィールドが作成可能、そんなことがわかりました。
日付の計算する便利な関数として、
VBAにDateAdd関数があります。
詳しくは、ヘルプを参照してほしいけど、
DateAdd(単位,増減値,基準となる日付・時刻)
で
単位は、
yyyy 年
m 月
d 日
h 時
n 分
s 秒
で（他にも四半期などあります）。
増減値の指定をプラス、マイナスの値で計算ができます。
有効期限が１年なので、
有効期限:DateAdd("yyyy", 1, [入会日])
と演算フィールドを作成します。
↑DateAdd関数を使って、日付の演算をしてみた。
/*
* ４.IIFを使用して、式の中で分岐した値を使用してみた
*/
なんとなく、わかってきたけど、
日付が入っている場合は、こっち、入っていないときは、こちら、
と切り替えて、使用したいんだけど。
入会日 2003/04/01 2002/03/01
更新日 (空白) 2003/03/01
だったら、
有効期限 2004/04/01 と 2004/03/01
みたく、条件によって基準のフィールドを変えて、
さらに、１年後の期限を求めたいんだよね私は。
更新日が空白なら、入会日を使用,入っていたら更新日を使用。
これは、IIfって関数があって、それを使用してみます。
IIF(条件, 条件が真の時, 条件が偽の時)
なので、
IIf([更新日] Is Null,[入会日],[更新日])
とすると、更新日がＮＵＬＬなら入会日、そうでない時は更新日を返すので、
DateAddと組み合わせて、
有効期限: DateAdd("yyyy",1,IIf([更新日] Is Null,[入会日],[更新日]))
としてみました。
↑IIf,DateAdd関数を使って、日付の演算
質問は、
>そして、その新たなフィールドに持ってきた日付を３年後の１日前にする。
>例えば、１９９８年５月２５日を２００１年５月２４日という感じです。
>（時効が三年物の場合です）
---
三年後の１日前だったよね。
時効: DateAdd("yyyy",3,IIf([B日付] Is Null,[A日付],[B日付])) - 1
※−１で楽したけど、きちんと書くなら、
DateAdd("d", -1, DateAdd("yyyy",3,IIf([B日付] Is Null,[A日付],[B日付])))
と３年後を計算後、１日引く、そんな感じかなぁ。
あと、余談だけど、ものによって（事件の性質によって）
時効が決まっていて、マスター化されているなら、
DateAdd("yyyy",[時効年数],IIf([B日付] Is Null,[A日付],[B日付]))
みたいに、３年と固定じゃなく、使えそうですね。
/*
* ５．終わりの挨拶
*/
ポイントは、
演算フィールドの作り方
と
時刻・日付の計算はDateAdd関数を使う
って感じかなぁ。
>もしよろしかったら、自分のような何も分からない者は、まず最初に
>どのような本を読めば良いのか教えてもらえましたら幸いです。
裁判の優秀な弁護士さんじゃないけど、
まずは、基本の六法全書を覚え、過去の判例を検索、今回の事例と比較。。。
なんてのをプログラムに置き換えた時、
一番初めに見る本かぁ、、、この質問が一番答えにくかったりします正直。
入門書を買って、まずは、
住所録などのサンプルで、
入力、抽出、出力を行う。
それから、データベース系とＶＢＡ系の本を購入。
データベース系は、複数のテーブルをつなげながら や 設計の考え方など
VBA系は、今回の関数や少し複雑な処理をやる時などのために。
その後、ヘルプのサンプルやネットで事例を検索します。
そのものズバリの例が載ってたり、アレンジしないと出来なかったり、
掲示板で経験者からのアドバスを聞いてみたり、
今回みたいに、メルマガ作者に質問を送ってみたり・・・
いろいろとあるのですが。
※ヘルプとネット検索だけで出来てしまう人も中には居るけど。
入門書、立ち読みしてよさそうな本、あったら、紹介します。
※小金稼ぎのバナー広告だけど、
http://www.ken3.org/etc/book.html
に、コンピュータ関係のネット書店のバナー載せてます。
最近、整備されてきて、表紙のほかに目次が見れます。
And 出版社のページに行くと、サンプルが落とせたりするので、
本買ってないので改版のサンプルをダウンロードするのは気が引けるけど、
目次で気になったサンプルを落とせる場所もあります。
拾い読みして、
１つでも何かの参考となれば幸いです。
Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。
※評価は↓で投票してね。感想は掲示板かメールでくださいね。
＜Excel 起動オプションのお話(引数的なラブレターを渡したい)＞
どうも、三流プログラマーのＫｅｎ３です。
今回は、
Excelの起動オプションのお話です
/*
* １．今回のキッカケ
*/
http://www.ken3.org/cgi-bin/bbs/vba/wforum.cgi
掲示板に、下記の質問が来ました。
---------------
>投稿時間：2003/06/06(Fri) 10:08
>投稿者名：JackZero
>
>タイトル：起動時に 引数を！
>
>お世話になります．
>
>ExcelやProject等で、起動時にコマンドラインにて
>引数を渡したいのですが、どのようにすればマクロ内で
>取得できるのでしょうか？
---------------
そんな質問に対して、
> ExcelやProject等で、起動時にコマンドラインにて
> 引数を渡したいのですが、どのようにすればマクロ内で
> 取得できるのでしょうか？
excel.exe c:\new.xls -in:d:\data\20030607.csv
or
c:\new.xls -in:d:\data\20030607.csv
と起動時に、例えばファイル名＋パラメータで起動したい、
で、起動した.xls内のAuto_Open起動時に走るマクロで、
その値を使用したい、
そんな感じですか？
起動は、ユーザーさんが、ショートカットなどから起動？
それともVBやAccessからExcelを開く？
どちらでしょうか？
※Excelの起動オプション、私も見ましたが、
それらしきオプションがなぜか無かったですね・・・
excel.exe c:\new.xls -in:d:\data\20030607.csv
みたいな起動はできないのかなぁ。
連続処理や固定のファイルを処理する場合、
c:\new.xls -u:営業
c:\new.xls -u:経理
なんて、感じで、起動時に処理分岐可能にできると
いいのにね。
なんて答えてました。
/*
* ２．機能を調査してみる
*/
コマンドラインの引数、あってもよさそうなので、ヘルプで調査。
Excel の起動スイッチを設定する
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
起動時のオプション 入力する文字列
特定のブックを開く --------------- ブックのパス/ファイル名
特定のブックを読み取り専用で開く - /r ブックのパス/ファイル名
Excel の起動画面および新規ブックが表示されないようにする /e
作業フォルダを指定する ----------- /p フォルダのパス/フォルダ名
Office Safe モードを指定する ----- /safe
あらら、これしか無いの？
^^^^^^^^^^^^^^^^^^^^^^^^
起動するブックに引数を渡したいのに、、、
余談：Accessの起動オプション
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
今回の質問とあまり関係ないけど、Accessだと、コマンドラインオプションで、
/cmd
~~~~
ってオプションがあり、
コマンド ラインの後に、Command 関数から返される値が続くように指定します。
このオプションは、必ずコマンド ラインの最後に指定してください。/cmd の代わりに
セミコロン (;) を使用することもできます。
このオプションを使用して、Visual Basic コードで使用可能なコマンド ライン引数を
指定します。
なんて、使えそうな便利なオプションがあります。
Command 関数
^^^^^^^^^^^^
Access を起動したときに使われたコマンド ライン (コマンド ライン :
アプリケーションを起動するための文字列のことです。)の引数を返します。
解説
コマンド ラインから Access を起動すると、/cmd オプションに続く部分がコマンド
ラインの引数としてプログラムに渡されます。
Command 関数を使うと、プログラムに渡された引数を返すことができます。
必ずコマンド ラインの最後に指定がポイントです。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub aaa()
MsgBox "起動時に/cmdで指定したのは[" & Command & "]"
End Sub |
まぁ、こんな感じで、Accessだとできるのに、Ａｃｃｅｓｓだとできるのに・・・
共通にしろVBA、なんで共通じゃないんだＶＢＡ、、
VBだと簡単なのにＶＢだと、、
なんて、どこかで聞いたこと言ってても先に進まないので、
/*
* ３．私が使いたくないAPIにそれらしきものが
*/
コマンドライン引数で、Ｗｅｂを検索すると、出てくる出てくる、
サンプルが。
その中で、
GetCommandLine ってものを見つけました。
^^^^^^^^^^^^^^
'実はAPIまるでわからない三流作者、宣言文を丸ごとコピー
Private Declare Function GetCommandLine _
Lib "kernel32" Alias "GetCommandLineA" () As Long
Private Declare Function lstrcpy _
Lib "kernel32" Alias "lstrcpyA" _
(ByVal lpString1 As String, _
ByVal lpString2 As Any) As Long
Private Sub Auto_open()
Dim sBuf As String
sBuf = Space$(255) 'バッファをスペース埋めで取る
'呪文じゃないが、GetCommandLine()関数を呼びlstrcpyでバッファにコピー
Call lstrcpy(sBuf, GetCommandLine())
'確認のため表示
MsgBox "起動方法は、[" & sBuf & "]です"
Debug.Print "[" & sBuf & "]"
End Sub |
と、Auto_openのモジュールに入れてテスト起動したところ、
["C:\Program Files\Microsoft Office\Office10\excel.exe" /e
無事にそれらしく取れたかなぁ。
↑表示された例
ショートカットを新たに作成してテストしてみますか。
"C:\Program Files\Microsoft Office\Office10\EXCEL.EXE" e:\work\vba086.xls 総務
↑ショートカット作成
テスト実行、出来るだろ？
あれれ、、、ダメだ。
予定では総務が渡ると思ったのに、、、
/*
* ４.少し横にそれて、VBScriptで引数を受け取る方法
*/
コマンドライン引数で、Ｗｅｂを検索すると、
VBScriptで引数を受け取る方法なんて今回のExcelに無関係そうなものも拾える。
拡張子を.vbsとしてファイルを作成します。
~~~~~~~~~~~~~
test.vbs
^^^^^^^^
msgbox "test"
なんて一行書いて、保存。
ダブルクリックで実行すると、
あらら不思議、メッセージが表示されたよ。
↑実行結果
これがウイルスの温床と噂のＶＢスクリプトってヤツですね。
よんでね.VBSとかファイル名を付けて、添付する・・なんてヤツですね。
まぁ、そんな話は、置いといて、
次の VBScript コードは、名前付きのコマンド ライン引数を表示する例です。
Set WshArguments = WScript.Arguments
Set WshNamed = WshArguments.Named
If WshNamed.Exists("a") Then
msgbox "A=" & WshNamed("a")
Else
Msgbox "引数Aが見つかりませんでした"
End If
で、Aの値を取れるんだぁ。
テストでまず、test.vbsのショートカットを作成します。
※右クリックでショートカット作成
作成したショートカットのプロパティを開きます。
↑ショートカットを右クリック、プロパティを選択
プロパティのリンク先に
~~~~~~~~~~~~~~~~~~~~
E:\Work\test.vbs /A:Ken3 /B:1234
と値を入れます。
↑リンク先に起動パラメータを代入
ダブルクリックして実行すると、パラメータが渡ったことが確認できます。
/*
* ５．VBScriptからExcelファイルを開く
*/
さてと、複合技じゃないけど、
直接意中の彼女にラブレター（パラメータ）渡せないんだったら、
共通の友人（VBScript）にお願いして、間接的に渡してみる、
そんな手段を考えました。
※でも、ラブレター、告白は直接でしょホントは、、、？
直接出来なきゃねぇ・・・と話をそらしてみました（笑）
まぁ、間接的にでも渡せないよりは、渡せたほうがいいのかな。
まずは、Excelの起動とファイルを開くルーチンの復習で、
過去のメルマガを検索すると、初めのほうで、
No.2 AccessからExcel出力
http://www.ken3.org/backno/backno_vba01.html#2
で、
Dim oApp As Object
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True
'*1↓頭にCreateObjectで作成した変数を追加しただけ
oApp.Workbooks.Open FileName:="D:\vba002\TYPE.xls"
'^^^^
なんてAccessからExcelを起動して、ファイルを開いてたよね。
これをそのままVBScriptに持っていきテストします。
おいおい、コンパイルエラーかよ（笑）
↑エラー画面
あっ、VBScriptって変数の型指定ができないんだっけ、
Dim oApp As Object
みたいに、As XXXXはダメなんだっけ、忘れてた。
これを取って、
えっ、まだダメなの？
oApp.Workbooks.Open FileName:="E:\work\vba086.xls"
の場所？
oApp.Workbooks.Open "E:\work\vba086.xls"
と引数の名前指定を外して見た（う〜ん、後で調査ですね）
無事、固定のファイルが開けたので、
今度はパラメータをFILE:E:\work\vba086.xlsで渡せるかチェックする。
ショートカットのリンクを
E:\Work\test.vbs /FILE:E:\work\vba086.xls
とファイル名をパラメータで渡すように変更してみた。
VBScript側で、ファイル名（引数）を受け取り、
そのファイルを開いてみた。
Dim oApp
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True
'引数のチェック、ファイルを開く
Set WshArguments = WScript.Arguments
Set WshNamed = WshArguments.Named
If WshNamed.Exists("file") Then
oApp.Workbooks.Open WshNamed("file") 'ファイルを開く
msgbox "File=" & WshNamed("file")
Else
Msgbox "引数Fileが見つかりませんでした"
End If
なんとか、ファイルを開くことが出来ました。
やりたいのは、彼女（Excel）にラブレター（パラメータ）を渡すんだっけ。
No.54 AccessからExcel開いて、処理終了後に閉じたい
http://www.ken3.org/backno/backno_vba11.html#54
で、.RUNってメソッドで、マクロを起動してたっけ。
受け取り側のExcelファイルを下記のように変更して、
^^^^^^^^^^^^^^^^^
Private Sub START(strP As String)
If IsEmpty(strP) Then 'パラメータ無しか？
'パラメータ無しの処理
MsgBox "パラメータ無しで起動されました"
Else
'パラメータありの処理
MsgBox "受け取ったのは" & strP & "です"
End If
End Sub |
ショートカットのリンクを
E:\Work\test.vbs /FILE:E:\work\vba086.xls /P:総務
とファイル名とパラメータを渡すように変更、
↑ショートカットにパラメータを追加
VBScript側では、.Runメソッドを使用して、
STARTマクロを起動、パラメータを1つ渡します。
Dim oApp
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
oApp.UserControl = True
'引数のチェック、ファイルを開く
Set WshArguments = WScript.Arguments
Set WshNamed = WshArguments.Named
If WshNamed.Exists("file") Then
oApp.Workbooks.Open WshNamed("file") 'ファイルを開く
If WshNamed.Exists("p") Then
oApp.Run "START", WshNamed("p")
Else
Msgbox "引数Pを指定してください"
End If
Else
Msgbox "引数Fileを指定してください"
End If
ポイントは
~~~~~~~~~~
oApp.Run "START", WshNamed("p")
と、マクロ名と引数としてパラメータを渡してます。
実行すると、総務とExcelで受け取ることが出来ました。
↑実行結果
/*
* ５．終わりの挨拶
*/
なんか、まわりくどいラブレター(引数)の渡し方でしたが、
VBScript経由でExcelを開き、マクロの起動のサンプルです。
ショートカットのリンクを
E:\Work\Excel起動.vbs /FILE:E:\work\vba086.xls /P:総務
E:\Work\Excel起動.vbs /FILE:E:\work\vba086.xls /P:営業
など、複数作成して使うことが出来ます。
なんかなぁ、、、ヤッパラブレターは直接でしょ。
Excelお嬢へ直接、引数（ラブレター）渡す方法、ご存知の人は教えてください。
私じゃＥｘｃｅｌお嬢様に渡せなかったので・・・・
拾い読みして、
１つでも何かの参考となれば幸いです。
Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。
※評価は↓で投票してね。感想は掲示板かメールでくださいね。
＜Access DLookup関数を使ってみた＞
どうも、三流プログラマーのＫｅｎ３です。
今回は、
AccessでDLookup関数を使ってみたいと思います。
/*
* １．今回のキッカケ
*/
メールで下記の質問をもらいました。
---------------
In message "アクセスVBAでwindowの閉じ方、グループでのデータ取得法...",
>あとひとつは、やはりアクセスで、
>グループ処理のとき、次のデータから
>
> fld1 fld2 fld3
> あ 2 a
> あ 1 b
> あ 4 c
>
>「あ」でグループ化し、fld2から 最小値 1をえらび
> fld3 については、bを表示させたいのですが、
>（fld2に属するfldsのデータを選ぶ)
>
>どうすればよろしいのですか。
---------------
そんな質問に対して、
よく使うだろうと思う、DLookupを使ってみたいと思います。
/*
* ２．クエリーとDLookupでかわしてみようと思った
*/
クエリーとDLookupでできそうです。
fld1 fld2 fld3
あ 2 a
あ 1 b
あ 4 c
「あ」を条件にして、fld2で小さい順にされているクエリーから
fld3の値を取り出す。
と方針を少し変えてみました。
まぁ、軽くテストでもするかな。
※下記失敗作です、動かないので注意。
Sub aaa() '失敗作・・・SQL直接は記述できないのかなぁ？
Dim data As String
Dim strSQL As String
'fld2の小さい順に並べたデータの集合がほしいので
strSQL = "select * from TEST_TABLE order by fld2"
'必要なデータfld3をstrSQLの集合から条件はfld='あ'で検索
data = DLookup("fld3", strSQL, "fld1 = 'あ'")
MsgBox data
End Sub |
TEST_TABLE
ID fld1 fld2 fld3
1 あ 2 a
2 あ 1 b
3 あ 4 c
4 い 5 a
5 い 6 b
6 い 4 c
7 い 10 d
fldの小さい順に並べたデータがほしかったので、
select * from TEST_TABLE order by fld2
とオーダーして
ID fld1 fld2 fld3
2 あ 1 b
1 あ 2 a
3 あ 4 c
6 い 4 c
4 い 5 a
5 い 6 b
7 い 10 d
とキレイになったデータからDLookupしたかったので、
'fld2の小さい順に並べたデータの集合がほしいので
strSQL = "select * from TEST_TABLE order by fld2"
'必要なデータfld3をstrSQLの集合から条件はfld='あ'で検索
data = DLookup("fld3", strSQL, "fld1 = 'あ'")
と書いてみたが、この書き方は、ダメなんだぁ、、
↑実行時エラーの画面
しかたがない、ワンクッション置いたクエリーを作成するかな。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
↑fld2でソートしたクエリーの作成
で、そのクエリーを元にデータをチェックします。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
下記、Accessのフォームに貼ったコマンドボタンです。
txtA [ ___ ]に条件を入れてテスト。
Private Sub コマンド2_Click()
Dim data As Variant
'直接の値でやってたけど、フォームの値を利用して、
data = DLookup("fld3", "Q_CHK", "fld1 = '" & Me![txtA] & "'")
MsgBox "fld3=" & data
End Sub |
普通に、
DLookup("取り出したい項目", "データ場所", "条件")
を使用して、
DLookup("fld3", "Q_CHK", "fld1 = '" & Me![txtA] & "'")
と関数を作りました。
フォームに条件"い"を入れて検索してみます。
↑フォームから検索した結果
それにしてもなぜ？
Dim data As Variant
~~~~~~~~~~~~~~~~~~~
えっとですね、
データが無いときに、"う"など見つからなかった時に、
Stringだと、Dlookupの戻り値がNULLなので、エラーが発生してしまうので。。。
↑フォームから検索した結果
いつものかわしかたで、
Dim data As String
'直接の値でやってたけど、フォームの値を利用して、
data = "" & DLookup("fld3", "Q_CHK", "fld1 = '" & Me![txtA] & "'")
と
””＆〜と強引に文字列にする、なんて逃げ方もあります。
/*
* ３．DLookupでデータの存在チェック、そんな使用例
*/
----
>質問なのですが…
>
>添付のファイルなのですが、移設依頼入力フォーム（メイン）内に、
>サブフォームとして照明番号・種別等を入れています。
>ここの照明番号を入力検索して、その照明が有るメインフォームを検索
>させるにはどうしたらよいのでしょうか？
>
>例えば、９１７−５００という番号で検索したら、
>ＩＤ：５ のページが出てくるようなイメージなのですが。
>
>ご教授のほど、よろしくお願い致します。m(__)m
----
検索条件を入れるテキストボックスと検索実行のボタンを追加して、
入力された番号を条件に照明番号台帳サブからデータを読む(DLookup)
ken3_番号 [ ________ ]
とテキストボックスを作成
ken3_検索とコマンドボタンを追加。
データが無事に読めたかIsNullでチェック
読めたら、
DoCmd.GoToControl "ID" 'IDフィールドに入力コントロールを移動
DoCmd.FindRecord 探したID 'で該当レコードに移動って感じです。
Private Sub ken3_検索_Click()
Dim 探したID
Dim str検索条件 As String
'検索条件を作成
str検索条件 = "照明番号 = '" & Trim(Me![ken3_番号]) & "'"
'該当するＩＤを照明番号台帳サブから検索する
探したID = DLookup("ID", "照明番号台帳サブ", str検索条件)
'検索がＯＫか判断する
If IsNull(探したID) Then
MsgBox "証明書番号" & Me![ken3_番号] & "は見つかりませんでした"
Else
'IDが見つかったら、レコードを移動させたい
DoCmd.GoToControl "ID" 'IDフィールドに入力を移動
'指定した条件のＩＤを探す
DoCmd.FindRecord 探したID
End If
End Sub |
漢字の変数名使ったり、DoCmd.連発でプロぽくないけど、こんな感じです。
＊英語が不得意ってバレてるからいっかな（笑）
検索？サーチ？スペルがわからないるFindもよくわからないけど、
記号として覚えてるようなもんかなぁ。
あっ、gotoとControl,Recordはなんとなくわかります私でも（笑）
なんて言ってないで、考え方は、
人が普通に処理する時はどうするんだろう？と考えて、
テーブル照明番号台帳サブを見て、証明番号を探す、
で
見つかったらＩＤを覚える。
次は、ＩＤが一致するデータを探す。
みたいな流れをプログラムで書くと、いろいろかける（だから難しいんだけど）
普通に、
DoCmd.GoToControl "ID" 'IDフィールドに入力コントロールを移動
DoCmd.FindRecord Me![ken3_番号]
でもいいんだけど、該当なしデータの時、エラーが発生するので、
DLookupで、事前にテーブルやクエリーからデータを検索してみました。
/*
* ５．終わりの挨拶
*/
簡単な、Dlookupの使い方でした。
拾い読みして、
１つでも何かの参考となれば幸いです。
Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。
※評価は↓で投票してね。感想は掲示板かメールでくださいね。
＜Access クエリー DCount関数で順位付け＞
どうも、三流プログラマーのＫｅｎ３です。
今回は、
Accessのクエリー演算フィールドで、
DCount関数を使って順位を付けて遊んでみたいと思います。
サンプルファイルは、
http://www.ken3.org/vba/lzh/vba088.lzh
にdb088.mdb(Access2000版)が保存されています。
＊Access97のマシーン熱暴走気味で不安定なので、
Access2000になってます。
※私のページ、いつの間にか重くなってる（笑）
/*
* １．今回のキッカケ
*/
メールでもらった下記の質問、
---------------
In message "アクセスVBAでwindowの閉じ方、グループでのデータ取得法...",
>あとひとつは、やはりアクセスで、
>グループ処理のとき、次のデータから
>
> fld1 fld2 fld3
> あ 2 a
> あ 1 b
> あ 4 c
>
>「あ」でグループ化し、fld2から 最小値 1をえらび
> fld3 については、bを表示させたいのですが、
>（fld2に属するfldsのデータを選ぶ)
>
>どうすればよろしいのですか。
---------------
そんな質問に対して、
No.87 Access DLookup関数を使ってみた
http://www.ken3.org/backno/backno_vba18.html#87
で、クエリーとDLookup関数を使って、処理してみました。
今回は、DCount関数を使って、少しイタズラしてみたいと思います。
/*
* ２．クエリーでレコードに順位を付ける
*/
No.85 Access クエリーで演算フィールドを使ってみる
http://www.ken3.org/backno/backno_vba18.html#85
で、
クエリーでIIfとDateAdd関数などの関数を使用することができ、
これを演算フィールドと解説してました。
いろいろとＷｅｂを演算フィールドをキーワードに散歩していると
クエリーの演算フィールドに
順位: DCount("fld2","TEST_TABLE","fld2 >" & [fld2])+1
と
順位を付けるサンプルが転がっていた。
元データ
^^^^^^^^
ID fld1 fld2 fld3
1 あ 2 a
2 あ 1 b
3 あ 4 c
4 い 5 a
5 い 6 b
6 い 4 c
7 い 10 d
に対して、さっそく使ってみると、
順位: DCount("fld2","TEST_TABLE","fld2 >" & [fld2])+1
をクエリーの項目として指定。
fld1 fld2 fld3 順位
あ 2 a 6
あ 1 b 7
あ 4 c 4
い 5 a 3
い 6 b 2
い 4 c 4
い 10 d 1
ははは、これだとたんにfld2の大きさで順位付けてるなぁ。
それに大きい順ジャン
まぁ、速度は別にして面白い方法だよね。
Dcountで自分より大きい該当レコードがいくつあるか？
を
"fld2 >" & [fld2]
を条件にして検索。
で＋１がポイントで、一番大きい値の時、
DCount("fld2","TEST_TABLE","fld2 >" & [fld2])
は０を返すから＋１して１位〜はじめてるのね。
DCount("項目","テーブルorクエリ","条件")
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
これをうまく使っているよね。
[]で項目を囲って、クエリー内で使っているのがポイントなぁ。
/*
* ３．グループ別に順位を付ける
*/
順位付け、まぁなんとなくわかったけど、
何やりたいの？アナタは？
あっ、グループ別小さい順に順位をふって、頭の１位のデータだけ取りたくて
そうなんだぁ、大変だねぇ。
まずは、逆順（小さい順）、自分以下の値のデータが在るかカウントするか。
あっ、符号を逆にしてみるか。
順位: DCount("fld2","TEST_TABLE","fld2 <" & [fld2])+1
fld1 fld2 fld3 順位
あ 2 a 2
あ 1 b 1
あ 4 c 3
い 5 a 5
い 6 b 6
い 4 c 3
い 10 d 7
ここまでの小細工で、fldの小さい順に順位がふれたけど、
これでは意味無いんだよねまだ。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
えっ、なんで？
fld1='あ'別、fld1='い'別に順位を振りたいんだよねグループ別にAnd条件で。
アンタなに？言ってんの？And条件とか自分で言ってるジャン。
そのまま素直に書いたら？いつもヒネクレテるから素直なソース書けないの？
順位: DCount("fld2","TEST_TABLE","fld1 = '" & [fld1] & "' And fld2 <" & [fld2])
fld1 fld2 fld3 順位
あ 2 a 1
あ 1 b 0
あ 4 c 2
い 5 a 1
い 6 b 2
い 4 c 0
い 10 d 3
※+1今回からは止めました(０からの順位にしました)
あらら、素直に条件に、
fld1(が)= '" & [fld1] & "'" フィールド１が同じで
And
fld2(が) <" & [fld2]) フィールド２が自分より小さい
そんなレコードを数えて順位としてみました。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
/*
* ４．先頭レコードに絞りたいので、順位=0にしてみた。
*/
で、レコードの折りたたみじゃないけど、
順位が０の先頭レコードだけ取り出したいので、
クエリーの条件に０を入れてみます。
fld1 fld2 fld3 順位
あ 1 b 0
い 4 c 0
と、先頭を取り出すことが出来ました。
↑抽出条件に順位０を指定
SQLビュー、、長い
SELECT TEST_TABLE.fld1, TEST_TABLE.fld2, TEST_TABLE.fld3,
DCount("fld2","TEST_TABLE","fld1 = '" & [fld1] & "' And fld2 <" & [fld2]) AS 順位
FROM TEST_TABLE
WHERE (((DCount("fld2","TEST_TABLE","fld1 = '" & [fld1] & "' And fld2 <" & [fld2]))=0));
これでQ_CHKって、
グループ別のfld2が一番小さなレコードを取り出す
クエリーが無事に出来ました。
※順位付けしなくても、グループ化のクエリー集計でできるかもしれませんが。
あとは、このQ_CHKクエリーをつなげて遊んでみるかな。
会員情報
ID 入会日 更新日 point 住所 氏名 電話番号 記号
1 2003/04/01 120 名古屋 ドラキチ あ
2 2002/03/01 2003/03/01 15 北海道 どさんこ い
3 2003/05/01 0 う
なんて、明細データの記号項目とQ_CHKをつなげて、Q_CHKのfld3を取り出してみます。
会員情報の記号とQ_CHKのfld1をまずつなげます。
その後結合プロパティを会員情報全てに変更します。
あっ、マスターが見つからない時（テストでは"う"の時）を考慮して
↑結合プロパティの設定サンプル、右押してプロパティ変えただけ
あとは、ほしいデータをセレクトします。
↑fld3をQ_CHKから選択してみました。
ID 入会日 住所 氏名 記号 fld3
1 2003/04/01 名古屋 ドラキチ あ b
2 2002/03/01 北海道 どさんこ い c
3 2003/05/01 う
と、無事にデータが取れました。
１つ順位付けのクエリーを使用したけど（逃げたけど）、
１つのＳＱＬで福問い合わせで書くと、なんか長くなりそうですね。
あとは、順位付けて細工したマスターデータ、量が多いと、
Q_CHKクエリーの中でDcountしているので、速度の注意が必要かなぁ。
もし繰り返し使うなら、一時テーブルに書き出す(テーブル作成orデータ削除・追加)、
集計や印刷作業
終了後一時テーブルを消す、
なんて方が、速度的には速いし、安心できるかも。
/*
* ５．終わりの挨拶
*/
サンプルファイルは、
http://www.ken3.org/vba/lzh/vba088.lzh
にdb088.mdb(Access2000版)が保存されています。
＊Access97のマシーン熱暴走気味で不安定なので、
Access2000になってます。
※私のページ、いつの間にか重くなってる（笑）
今回は、クエリーでDcount関数を使用した、
順位付けのお話、ついでにグループ別の順位付けの話。
拾い読みして、
１つでも何かの参考となれば幸いです。
Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。
※評価は↓で投票してね。感想は掲示板かメールでくださいね。
＜Access 検索フォームで選択した番号を使用する その１＞
どうも、三流プログラマーのＫｅｎ３です。
今回は、
フォーム間のデータのやり取りで、
固定の処理で通用する簡単なやり方を書きたいと思います。
~~~~
固定の処理とか言ってると、逆の汎用性のある方法から聞きたいよね。
まぁ、まぁ、あせらないでよ。
サンプルファイルは、
http://www.ken3.org/vba/lzh/vba089.lzh
にdb089.mdb(Access2000版)が保存されています。
/*
* １．今回のキッカケ
*/
掲示板に下記の質問をもらいました。
---------------
In message "[BBS :142] Accessでのパラメータ引渡し方法",
Yoshi さん wrote...
>投稿時間：2003/06/18(Wed) 17:25
>
>おなまえ：Yoshi
>タイトル：Accessでのパラメータ引渡し方法
>ＵＲＬ ：
>コメント：
>
>こんにちは。はじめて書き込みます。
>現在「顧客画面」と「顧客番号検索画面」というものがあります。
>「顧客画面」は、KEYである顧客番号とその他情報を登録、修正、削
>除する画面です。
>「顧客番号検索画面」は、「顧客画面」の顧客番号の検索機能です
>。
>つまり、「顧客画面」で登録されている顧客情報を変更する場合
>現在データベースに登録されている顧客番号を一覧(顧客番号検索画
>面)表示させ選択された顧客番号を「顧客画面」に戻すといった方法
>を教えてください。
>すいません、わかりずらいと思うので簡単な図を書きます。
>
>１顧客画面 ← 顧客情報の一部の修正を行いたいためこの画面を
>開く
>
>２顧客番号横の検索ボタンを押下 ← 顧客番号がすぐにわからな
>いため検索画面を開く
>
>３顧客番号検索画面が開く ← リストボックスで作成した一覧を
>表示
>
>４リストボックスの対象行を選択
>
>５顧客画面 ← 選択された顧客番号を表示させる
---------------
そんな質問に対して、
今回は、単純にデータのセット、レコード移動でかわしてみたいと思います。
/*
* ２．簡単に勝手な仕様を起す
*/
元になるテーブルは、顧客番号と適当に作成する。
テーブル名:顧客
^^^^^^^^^^^^^^^
顧客番号 point 住所 氏名 記号
1 120 名古屋 ドラキチ あ
2 15 北海道 どさんこ い
3 20 東京 ヤクルト う
4 5 神奈川 横浜 え
5 20 福岡 ダイエー お
顧客画面
^^^^^^^^
この画面は、登録、修正、削除の機能を持っている。
画面フォーマットは凝らないで、単票形式のオートフォームで作成。
顧客番号 [ ______ ] の横（ここ）に[検索]のボタンがある。
処理機能、流れはいろいろあるけど、
[検索]ボタンがクリックされたら、
"顧客番号検索画面"を開く。
顧客番号検索画面
^^^^^^^^^^^^^^^^
画面は、リストボックス、選択ボタン、キャンセルボタンを持っている。
画面が開かれたら、（開かれたタイミングで）
リストボックスで、
顧客番号 氏名 記号
1 ドラキチ あ
2 どさんこ い
3 ヤクルト う
4 横浜 え
5 ダイエー お
を表示する。（顧客テーブルから、顧客番号、氏名、記号をリスト表示）
対象行を選択後、
[選択]のボタンが押されたら、
顧客画面！（の）顧客番号にデータをセットする。
顧客画面、該当レコードへデータを移動する。
検索フォームを閉じる。
[キャンセル]のボタンが押されたら、
行が選択されていても、何もしないで検索フォームを閉じる。
/*
* ３．顧客番号検索画面の作成（まだ処理無し）
*/
顧客番号の選択画面を作成します。
lst顧客番号(リストボックス)
顧客番号 氏名 記号
1 ドラキチ あ
2 どさんこ い
3 ヤクルト う
4 横浜 え
5 ダイエー お
を表示するリストボックス。
↑リストボックスウィザードで簡単に作成
[btn選択] [btnキャンセル]とボタンを２つ作成する。
↑ボタンウィザードで閉じるを簡単に作成
まだ何も本格的なコードは書いていないけど、
リストボックスにデータが表示されていると、
それらしく見えるから不思議。
↑ボタンウィザードで閉じるを簡単に作成
まぁ、このへんは、べつに気にしないで。
/*
* ４．顧客画面に検索ボタンの作成（顧客番号検索画面を開く）
*/
ウイザードで、簡単に単票フォームの画面を作ってから、
顧客番号 [ ______ ] の横に[検索]ボタンを作成する。
↑ボタンウィザードでフォームを開くを簡単に作成
btn_Clickのイベントが完成する。
Private Sub btn検索_Click()
On Error GoTo Err_btn検索_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = ChrW(-26521) & ChrW(23458) & ChrW(30058) & ChrW(21495)
& ChrW(26908) & ChrW(32034) & ChrW(30011) & ChrW(-26782)
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_btn検索_Click:
Exit Sub
Err_btn検索_Click:
MsgBox Err.Description
Resume Exit_btn検索_Click
End Sub |
余談：オートで作成されたソースを見て
~~~~~
stDocName = ChrW(-26521) & ChrW(23458) & ChrW(30058) & ChrW(21495)
& ChrW(26908) & ChrW(32034) & ChrW(30011) & ChrW(-26782)
えっ、なにこれ？漢字のフォーム名ってダメなの？
なめやがって、
stDocName = "顧客番号検索画面"
DoCmd.OpenForm stDocName, , , stLinkCriteria
と直してテスト。
あれ・・動くよ。まぁいっか。
びっくりさせやがって、Access2000から漢字のフォーム名ムリかと思ったよ。
（ホントのところは、ダメかもしれないけど、潔癖症じゃないので、
私はそのまま書いてみました）
/*
* ５．選択後、顧客画面のレコードを移動させる
*/
さてと、無事に２つの画面が完成、開いたところで、
顧客番号検索画面
~~~~~~~~~~~~~~~~
のbtn選択にコードを追加します。
フォームを閉じるコードが既に書かれていると思います。
Private Sub btn選択_Click()
On Error GoTo Err_btn顧客_Click
DoCmd.Close
Exit_btn選択_Click:
Exit Sub
Err_btn選択_Click:
MsgBox Err.Description
Resume Exit_btn選択_Click
End Sub |
まぁ、エラー処理今回は、消しちゃいます。（オイオイ平気なの？）
Private Sub btn選択_Click()
DoCmd.Close '閉じる
End Sub |
だけでいいんで。
これに、
顧客画面、該当レコードへデータを移動する。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
機能を追加してみます。
Private Sub btn選択_Click()
Dim LNG顧客番号 As Long
'番号選択のチェック
If IsNull(Me![lst顧客番号]) Then '何も選択されていないか？チェック
MsgBox "顧客番号を選択してください"
Exit Sub '関数を抜ける
End If
'番号をローカル変数に保存する
LNG顧客番号 = Me![lst顧客番号]
Debug.Print LNG顧客番号 'テバックで表示
'フォームを閉じる
DoCmd.Close
'レコードを移動する
DoCmd.GoToControl "顧客番号" '顧客番号フィールドにコントロールを移動
DoCmd.FindRecord LNG顧客番号 '選択された番号のレコードに移動
'
End Sub |
あれ、質問は、フォーム間の代入処理なのに、代入してないよ？
あっ、これですか・・・
Forms![顧客画面]!顧客番号 = LNG顧客番号
で、隣のフォームにデータをセットしたり・参照できるんだけど、
呼び出し元が連結フォームで処理していると、
Forms![顧客画面]!顧客番号 = LNG顧客番号
でセットすると、レコードは移動前の状態なので、
顧客番号を上から重ねて書いてしまいます。（データが壊れてしまいます）
非連結の番号エリアなら、閉じる前に、
Forms![顧客画面]![顧客番号] = Me![lst顧客番号]
Forms!顧客画面!顧客番号 = Me!lst顧客番号
で、フォーム名、コントロール名を指定して簡単にセットできます。
Forms![フォーム名]![コントロール名]
がデータセットのポイントかな。
連結フォームだと、データセット＝代入になってしまうので、
前のデータを消してしまうので、
DoCmd.GoToControl "顧客番号" '顧客番号フィールドにコントロールを移動
で、コントロールを顧客番号にして、
DoCmd.FindRecord LNG顧客番号 '選択された番号のレコードに移動
で、レコードを探す（移動する）
代入はしていないのですが、レコードが移動しているので、
代入しているように見えます。
サンプルファイルは、
http://www.ken3.org/vba/lzh/vba089.lzh
にdb089.mdb(Access2000版)が保存されています。
動きを見てもらうのが一番ハヤイかもしれません。
※いろいろと追加して、テストしてみてください。
/*
* ６．終わりの挨拶
*/
今回は、
別フォームで番号を選択、選択された番号を使用して、
該当レコードに移動する、
そんな処理方法の例でした。
※汎用性のある方法は次回に・・・
拾い読みして、
１つでも何かの参考となれば幸いです。
Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。
※評価は↓で投票してね。感想は掲示板かメールでくださいね。
検索して目的の情報を探す。
目的の情報を探すには、最近はググれとよく聞きます。なので、検索ボックスを付けました。
いろいろなキーワードを入れて、検索してみてください。
ページフッター
ここまで、読んでいただきどうもです。ここから下は、三流君宛のメッセージ送信や 三流君のホームページの紹介・案内です
目的の情報が見つかったか？少々心配しつつ、、、※質問や感想は、気軽に送ってくださいね。
質問や要望など メッセージを送る(三流君に連絡する)
質問や要望など連絡方法でお互い確認が取りやすく、便利なのが掲示板なのですが、私の対応のまずさから不定期で荒れてしまい、掲示板は現在封鎖中です。(反省しなきゃ)
|
|
感想や質問・要望・苦情など 三流君へメッセージを送る。
時間的余裕のある要望・質問・苦情の場合は、下記のフォームからメッセージを送ることができます。
|
急ぎで連絡がほしい、そんな時は:[三流君連絡先]に連絡してください。
リンクや広告など
項目別に↓に人気の記事をまとめてみました。お探しのジャンルを選択してください。
人気記事(来場者が多いTOP3):
[VBAでIE,WebBrowserを操作]・・・VBAでIE,WebBrowserを操作する サンプルです
[Access から Excel 連携 CreateObject("Excel.Application")]・・・AccessからExcelを操作したりデータの書き出しなどです
[VBAでOutlookの操作 CreateObject("Outlook.Application" )]・・・VBAからOutlookを使い、メール関係を処理するサンプルです
↑上記３つみたいなCreateObjectで他のアプリケーションを操作するサンプルが人気です。
開発時の操作:
[F1を押してHELPを見る]/
[Debug.Print と イミディエイトウインドウ]/
[実行時エラーでデバッグ]/
[ウォッチ式とSTOP]/
[参照設定を行う]
仕様書(設計書？) XXXX書類:
[基本設計書や要求仕様書]/
[テスト仕様書 テストデータ]/
[バグ票]/
[関数仕様書]/
[流れは 入力・処理・出力]
Excel関係:
[Excel UserFormを操作する]・・・エクセルでユーザーフォームを作成して入力などを行ってます
[ExcelからAccessを操作する]・・・ExcelからAccessのマクロを起動してみました、
[Excel関係 関数、その他]・・・その他Excel関係です
Access関係:
[Access UserForm/サブフォーム 操作]・・・アクセスでフォームを使ったサンプルです
[Access レポート操作]・・・レポートを操作してみました
[Access クエリーやその他関数]・・・あまりまとまってませんが、スポット的な単体関数の解説です
その他：VBAの共通関数やテキストファイルの操作など
[VBAでテキストファイル(TextFile)の操作]・・・普通のテキストファイルを使ったサンプルです
[VBA 標準関数関係とその他解説]・・・その他、グダグタ解説してます
Blog:[三流君の作業日記]/
[サンプルコードのゴミ箱]/
広告-[通販人気商品の足跡]
[三流君(TOP ken3.org へ戻る)]
/ [VBA系TOPへ]
/ [VBA系バックナンバー目次へ移動]
