accessのユーザー定義関数記述について

質問

VBAでユーザー定義関数を記述しようと思って勉強中です。

以前、アドバイスして頂きすばらしい関数式で合計8フィールドに入力後、
同一クエリ上で再度結合して1つのフィールドにまとめることが出来ました。
そして最後にもう1つクエリでファイルを作成しCSVファイル出力です。
目的は、文字列とtag(htmlタグ)と組合わせて1つのHTMLテキストにしようとして下ります。

そこでまた問題が発生いたしました。
1つのフィールドが1024文字のため分割して結合しておりましたが、クエリとクエリとクエリの3段階のため

「クエリが複雑すぎます」　というエラーが出ててしまいました。

VBAのユーザー定義関数に変換しなければならないのですが、かなり複雑になってきました。一体どのような記述になるのでしょうか。
大変お手数をお掛けいたしますが、よろしければアドバイスお願いいたします。

最終目的　block1 + block2 + block3 + block4 + block5 + block6+ block7+ block8を連結して
html文を作成するためVBAでユーザー関数定義で保存したいのです。

クエリ関数整理後の今回の連結
フィールド名 block3 & block4 & block5 &　以後省略

IIF(TRIM("" & [A] & [B] & [C])="","",[T_shop]![tg6])&
MID(IIF(TRIM("" & [A] & [B] & [C])="","<BR><BR>","")&
IIF(TRIM("" & [A])="","","<BR><BR>" & [A])&
IIF(TRIM("" & [B])="","","<BR><BR>" & [B])&
IIF(TRIM("" & [C])="","","<BR><BR>" & [C])&
IIF(TRIM("" & [A] & [B] & [C])="","","</FONT>"),9)
&
IIF(TRIM("" & [D] & [E] & [F])="","",[T_shop]![tg7])&
MID(IIF(TRIM("" & [D] & [E] & [F])="","<BR><BR>","")&
IIF(TRIM("" & [D])="","","<BR><BR>" & [D])&
IIF(TRIM("" & [E])="","","<BR><BR>" & [E])&
IIF(TRIM("" & [F])="","","<BR><BR>" & [F])&
IIF(TRIM("" & [D] & [E] & [F])="","","</FONT>"),9)
&
IIF(TRIM("" & [G] & [H] & [I])="","",[T_shop]![tg8])&
MID(IIF(TRIM("" & [G] & [H] & [I])="","<BR><BR>","")&
IIF(TRIM("" & [G])="","","<BR><BR>" & [G])&
IIF(TRIM("" & [H])="","","<BR><BR>" & [H])&
IIF(TRIM("" & [I])="","","<BR><BR>" & [I])&
IIF(TRIM("" & [G] & [H] & [I])="","","</FONT>"),9)

回答No.6

jamshid6 · ベストアンサー

なるほど。原因わかりました。
標準モジュールのシート名も「getblock」にしていますね。何でもいいのでここを「getblock」以外にしてみてください。

＃プライベートリンクは禁止されているはずなので、上記リンクは後で削除がかかるかもしれません

お礼

またまたご回答ありがとうございました。リンクの件に関してルールを弁えておらず申し訳ございません。こちらより運営スタッフに連絡をさせて頂き状況の説明をさせて頂きます。ご迷惑をお掛けいたしました。

ご指摘いただきましたとおり訂正をさせて頂きましたところ、画面がパッパと変わり出来ました。この感動を探しておりました。本当に本当にありがとうございました。やっと次のステップに進むことが出来ます。
今回の質問に対してのご親切なご回答今後も本当に忘れません。このたびは、本当にありがとうございました。

回答No.5

jamshid6

画像が貼れるようになったみたいなんで、試してみましょうかね。。
全く同じではないでしょうが、イメージはつかめるはずなので。

お礼

本当に恐縮しております。画像までアップして頂き私の疑問点に関してご対応して頂き本当にありがとうございました。この2週間でとても勉強になりました。本屋さんで立ち読みの毎日、VBAに関して3冊買って只今読おります。何かの設定が違っているのだと思うのですが、多分単純な素人レベルの問題で解決するような気がしてまいりました。1から復習をさせて頂きます。このたびは、必要以上のご質問回数に快くご回答して頂き本当に本当にありがとうございました。

補足

このたびは、ここまでして頂き大変ありがとうございます。
本当に心苦しくお世話になっております。今日も朝より勉強中でございます。

アップロードして頂きました写真を閲覧させていただきましたが、多分同じような感じでございます。

今回ご質問をさせて頂き何度なくご回答して頂き本当にありがとうございました。このサイトの使用も余り理解していないままここまで出来たことが本当にびっくりしております。ご回答のお礼もこの後させて頂きますのでどうか宜しくお願いいたします。ありがとうございました。

回答No.4

jamshid6

うーん。まだ不安ですね。

>教えていただいたとおりモジュールにはgetblockという名称で登録出来たのですが、入力後、デザインビューに戻るときに未定義関数ですとエラーが出てしまいます

「入力後」って何の入力ですか？VisualBasicエディタの画面でエラーが出ているのですか？それともクエリのデザインビュー画面？
getblock関数のコードは、標準モジュールに貼りましたよね？

そもそも
>block5:getblock([a],[b],[c],[tg])
はおかしくないですか？クエリ側では実際に存在するフィールドをパラメータにしないといけませんよね。
（関数を定義する方のと混乱していませんか）
>block5 : getblock(C_html_text![4_1],C_html_text![4_2],C_html_text![4_3],T_sh_add_3![tg6])

お礼

このレベルまで来ると私自身VBAの基本をある程度勉強してからでないと各ツールの正式名称などで説明をしなければまともなご質問が出来ないのに、ポイントを抑えたご回答をして頂き誠にありがとうございました。本当にありがとうございました。

補足

本当にありがとうございます。今日もVBAの本を買って呼んで勉強中です。
説明が悪くまた知識が追いつかず不愉快なご気分にさせてしまいとても恐縮しております。大変申し訳ございません。

>>>「入力後」って何の入力ですか？

block5 : getblock(C_html_text![4_1],C_html_text![4_2],C_html_text![4_3],T_sh_add_3![tg6])

クエリのデザインビュー画面でフィールド名に今度は、block5という名称で試してみました。
保存後にデータシートビューに戻すところで「式に未定義関数 "getblock"があります。」と出てきます。

>>>getblock関数のコードは、標準モジュールに貼りましたよね？
はい、標準モジュールでこのプロシージャ以外は、クラスモジュールのみです。

新規作成→挿入→標準モジュール→挿入→プロシージャ→名称 getblock→Fanctionプロシージャにチェック→コピペで貼り付け→保存→画面閉

>>>そもそもblock5:getblock([a],[b],[c],[tg])はおかしくないですか？
クエリ側では実際に存在するフィールドをパラメータにしないといけませんよね。
ここの部分がまだ理解していないようです。

>>>（関数を定義する方のと混乱していませんか）
>block5 : getblock(C_html_text![4_1],C_html_text![4_2],C_html_text![4_3],T_sh_add_3![tg6])

多分混乱しております。前回教えていただきましたSQLで新規にクエリを作成するということなのでしょうか???

SELECT getblock(C_html_text![4_1],C_html_text![4_2],C_html_text![4_3],T_sh_add_3![tg6])
FROM

何度となくアドバイスして頂き本当にありがとうございます。

回答No.3

jamshid6

うーむ。。
置きかえる必要はないです。関数はそのまま使ってみてください。
Functionにおけるパラメータ名は関数内で受け取った値を示すにすぎないので、a,b,c,tgのままで特に問題ないはずです。

使い方を質問者さんのフィールドに合わせてもう一度書くと、こんな感じです。
SELECT getblock(C_html_text![4_1],C_html_text![4_2],C_html_text![4_3],T_sh_add_3![tg6])
FROM ....

お礼

関数の置き換えで余りにもきれいな無駄のない処理で理解度が足りないのに先に進みすぎてご迷惑をお掛けいたしました。もう少し勉強をしなおします。このたびは、ありがとうございました。

補足

何度も本当に本当にありがとうございました。
ユーサー定義関数の意味がやっとわかりました。私は、個別の関数処理ばっかり考えて下りました。まるでマクロ処理ですね。初心者の勉強不足で大変申し訳ございません。組み込み関数と同じということですね、判ってりました。ありがとうございます。

しかし、教えていただいたとおりモジュールにはgetblockという名称で登録出来たのですが、入力後、デザインビューに戻るときに未定義関数ですとエラーが出てしまいます。設定がおかしいのでしょうか。
本当に何度も申し訳ございませんが、ご教授いただけますでしょうか。
宜しくお願いいたします。

設定クエリファイル名 C_add_text
クエリフィールド　block5:getblock([a],[b],[c],[tg])
使用ファイル リレーショナルシップで下記3種のファイルを連結
T_au_deli　C_html_text　T_sh_add3

回答No.2

jamshid6

うーん、なんというか難しく考えすぎですね。
MidもIIfもそのまま使えるので、正直殆ど書き直す必要などないです。

Public Function getblock(ByVal a, ByVal b, ByVal c, ByVal tg) As String
getblock = IIf(Trim("" & a & b & c) = "", "", tg) _
& Mid(IIf(Trim("" & a & b & c) = "", "<BR><BR>", "") _
& IIf(Trim("" & a) = "", "", "<BR><BR>" & a) _
& IIf(Trim("" & b) = "", "", "<BR><BR>" & b) _
& IIf(Trim("" & c) = "", "", "<BR><BR>" & c) _
& IIf(Trim("" & a & b & c) = "", "", "</FONT>"), 9)
End Function

それで、
SELECT getblock([A],[B],[C],[T_Shop]![tg6])
&getblock([D],[E],[F],[T_Shop]![tg7])
&getblock([G],[H],[I],[T_Shop]![tg8])
FROM ....
って感じでやればいいのでは？

お礼

ユーザー関数に関して一夜つけの知識しかなくせっかくのアドバイスに答えられなく申し訳ございません。自分の考えていた関数がとてもきれいな配列に変換できるということ和とても親切に教えていただき誠にありがとうございました。

補足

ご回答ありがとうございました。いつもお世話になります。早速置き換えてみたのですが、
上段3段目　Public　～　 As String　までの表記がおかしいみたいで赤くなったままなのです。
コンパイルエラーで修正候補 区切り記号または )と出ます。!を何かに置き換えるのでしょうか。
それとも名前付き引数というものでしょうか。???

Option Compare Database
Option Explicit

Public Function block_4(C_html_text![4_1] As Variant
　　　　　　　　, C_html_text![4_2] As Variant
　　　　　　　　 , C_html_text![4_3] As Variant
　　　　　　　　, T_sh_add_3![tg6] As VString) As String

block_4 = IIf(Trim("" & C_html_text![4_1] & C_html_text![4_2] & C_html_text![4_3]) = "", "", T_sh_add_3![tg6]) _
& Mid(IIf(Trim("" & C_html_text![4_1] & C_html_text![4_2] & C_html_text![4_3]) = "", "<BR><BR>", "") _
& IIf(Trim("" & C_html_text![4_1]) = "", "", "<BR><BR>" & C_html_text![4_1]) _
& IIf(Trim("" & C_html_text![4_2]) = "", "", "<BR><BR>" & C_html_text![4_2]) _
& IIf(Trim("" & C_html_text![4_3]) = "", "", "<BR><BR>" & C_html_text![4_3]) _
& IIf(Trim("" & C_html_text![4_1] & C_html_text![4_2] & C_html_text![4_3]) = "", "", "</FONT>"), 9)
End Function

回答No.1

jamshid6

ユーザ定義関数を作ることにしたんですよね（書き方はわかってますよね）？

であれば繰り返し同じような処理が登場する上記のようなケースでは、パラメータA,B,Cとタグの合計４つを受けて、ブロック１つ分の文字列を返す関数にした方がメンテナンスが楽なんじゃないですか？

お礼

組み込み関数からVBAでの処理に関して全然わからずポイントの外れたご質問ばかりで自分なりに本を見ながら勉強していく方向性を教えて頂きにご回答して頂き本当にありがとうございました。

補足

ご回答ありがとうございます。フィールドをまとめ1つにまとめ最終クエリにつなげたところ、
クエリが複雑すぎますというメーっセージが出てファイルが開かなくなってしまいました。そこでユーザー関数に変更という経緯でございます。
空白とMidの処理が書き方がいまいち判りません。
大変お手数をお掛けいたしますが教えていただけませんでしょうか。
宜しくお願いいたします。

Option Compare Database
Option Explicit

Function tg_all(block3 As String
　　 , block4 As String
, block5 As String
) As String

If tg_all = Trim("" & [3_1] & [3_2] & [3_3]) = "" Then
block3 = ""
Elself block3 >= "" Then
block3 = [T_shop]![tg6]

Elself tg_all = MID(Trim("" & [3_1] & [3_2] & [3_3])="" Then
block3 = "<BR><BR>"
Elself block3 = ""
block3 = ""

Elself tg_all = Trim("" & [3_1])="" Then
block3 = ""
Elself block3 >= "" Then
block3 = Trim("<BR><BR>" & [3_1])

Elself tg_all = Trim("" & [3_2])="" Then
block3 = ""
Elself block3 >= "" Then
block3 = Trim("<BR><BR>" & [3_2])

Elself tg_all = Trim("" & [3_3])="" Then
block3 = ""
Elself block3 >= "" Then
block3 = Trim("<BR><BR>" & [3_3])

Elself tg_all = Trim("" & [3_1] & [3_2] & [3_3])="" Then
block3 = ""
Elself block3 >= "" Then
block3 = "</FONT><BR><BR>"),9)

Elself tg_all = Trim("" & [4_1] & [4_2] & [4_3]) = "" Then
block4 = ""
Elself block4 = False Then
block4 = [T_shop]![tg7]

Elself tg_all = MID(TRIM("" & [4_1] & [4_2] & [4_3])="" Then
block4 = "<BR><BR>"
Elself block4 = ""
block4 = ""

Elself tg_all = TRIM("" & [4_1])="" Then
block4 = ""
Elself block4 = ""
block4 = Trim("<BR><BR>" & [4_1])

Elself tg_all = TRIM("" & [4_2])="" Then
block4 = ""
Elself block4 = False Then
block4 = Trim("<BR><BR>" & [4_2])

Elself tg_all = TRIM("" & [4_3])="" Then
block4 = ""
Elself block4 = False Then
block4 = Trim("<BR><BR>" & [4_3])

Elself tg_all = TRIM("" & [4_1] & [4_2] & [4_3])="" Then
block4 = ""
Elself block4 = False Then
block4 = "</FONT><BR><BR>"),9)

Elself tg_all = Trim("" & [5_1] & [5_2] & [5_3]) = "" Then
block5 = ""
Elself block5 = False Then
block5 = [T_shop]![tg8]

Elself tg_all = MID(TRIM("" & [5_1] & [5_2] & [5_3])="" Then
block5 = "<BR><BR>"
Elself block5 = ""
block5 = ""

Elself tg_all = TRIM("" & [5_1])="" Then
block5 = ""
Elself block5 = False Then
block5 = Trim("<BR><BR>" & [5_1])

Elself tg_all = TRIM("" & [5_2])="" Then
block5 = ""
Elself block5 = False Then
block5 = Trim("<BR><BR>" & [5_2])

Elself tg_all = TRIM("" & [5_3])="" Then
block5 = ""
Elself block5 = False Then
block5 = Trim("<BR><BR>" & [5_3])

Elself tg_all = TRIM("" & [5_1] & [5_2] & [5_3])="" Then
block5 = ""
Elsel block5 = False Then
block5 = "</FONT><BR><BR>"),9)

End If
End Select