さて、今回はAccessの関数です。
難しいですよね～、関数。関数の種類はたくさんあり、また、書式に従って記述しないと正しく動作しません。しかし、使い方さえ分かっていれば、テクニックの幅が一段と広がります。必ず役に立つときがくるので、この機会に、ぜひ勉強しておきましょう！
この講座ではステップbyステップで簡単に進めることができます。
是非、ご参考に！
関連講座
オススメの参考書
Accessクエリ&フォーム&レポート辞典―2010/2007対応 (Office2010 Dictionary Series)
  |
著者 | 日野間 佐登子 |
|---|---|---|
| 出版 | 秀和システム | |
| 発行日 | 2010年10月 | |
| 総ページ数 | 498ページ | |
| ダウンロードファイル | あり |
この本の良い所
簡単なクエリから、複雑な条件、関数を利用したクエリまで幅広く網羅されているオススメの参考書。約500ページ近いボリュームの3分の2以上が、クエリについて記載されています。ページの随所に、「操作のヒント」や「ワンポイント」といった記載がされており、ユーザーに配慮した作りといえます。さらに、ページの下部には関連項目があり、ページの索引がしやすくなっているのも特徴です。
何より、476ページ以降には「関数の構文一覧」が載っており、困った時に大いに役立つでしょう。サンプルデータがある点も○です。ある程度知識のある中級者以上ならば、概ね満足する内容となっているハズです。Accessの関数の参考書は、あまり多くないので、貴重な一冊といえます。
※Access 2010 / 2007対応となっていますが、Access2013でも問題なく使用できます。
【ちょっと残念な所】
本書は普通の参考書よりも、やや小さいため、文字が多少ちいさくなっています。人によっては読みにくいと感じることがあり、少しごちゃごちゃした印象を受けます。参考書というより、専門書に近いため、初級者には向いてないので注意です！
「これから関数を勉強してみようかな・・・」というレベルなら問題ないでしょう。
算術演算子
これから関数やクエリを使用していくにあたり、計算が必要になります。最も基本的な知識がこの算術演算子です。
ぜひ、覚えましょう。
| 演算子 | 目的 | 使い方 |
|---|---|---|
| ＊（アスタリスク） | ２つの数値を乗算する | 10＊5 |
| /（スラッシュ） | 最初の数値を2番目の数値で除算する | 10 / 5 |
| Mod | 最初の数値を2番目の数値で除算し、余りを返す | 10 Mod 3 |
| ＋（プラス） | 2つの数値を加算する | 10 ＋ 5 |
| ー（マイナス） | 2つの数値を減算する | 10 ー 5 |
比較演算子
数値を比較するための演算子です。大切なのは、「含む」「含まない」の違いです。この違いをしっかり覚えておかないと、クエリの問い合わせ結果は全く違うものになります。
気を付けて使用しましょう！
| 演算子 | 目的 | 使い方 |
|---|---|---|
| ＜ | より小さい（含まない） | [小計] ＜ 1000 |
| ＜＝ | 以下（含む） | [小計] ＜＝ 1000 |
| ＞ | より大きい（含まない） | [小計] ＞ 1000 |
| ＞＝ | 以上（含む） | [小計] ＞＝ 1000 |
| ＝ | 等しい | [小計] ＝ 1000 |
| ＜＞ | 等しくない | [小計] ＜＞ 1000 |
論理演算子
中学生の時に習ったベン図と同じです。
- 「A And B」はＡとＢの両方を含むレコード。
- 「A or B」は、ＡかＢのどちらか一方を含むレコード。
- 「Not A」は、Ａ以外のレコード。
といった感じになります。
ほかにも「Xor」や「Eqv」などがあるのですが、ほぼ使わないので割愛します。
| 演算子 | 使い方 | 説明 |
|---|---|---|
| And | >=#2015/08/09# And <=#2015/12/30# | 2015/08/09～2015/12/30 |
| Or | 300 Or 500 | 数値が300または500のレ―コード |
| Not | Not 500 | 数値が500以外のレ―コード |
連結演算子
文字列と文字列をつなげる演算子です。通常は「＆（アンパーサント）」を使用しますが、「＋」でも利用可能です。[顧客名] + [単価]のように、「文字列 + 数値」をつなげることはできません。
（※基本的に実行エラーとなるが、数値を文字列に変換すれば可能）
| 演算子 | 使い方 | 説明 |
|---|---|---|
| ＆ | [住所1] ＆ [住所2] | 複数の文字列を連結させます(Null値は反映されない) |
| ＋ | [住所1] + [住所2] | 複数の文字列を連結させます(Null値を反映する) |
Access 関数
ここでは、用途に応じた関数の一覧を表示しています。また、見やすいように、括弧は全角で表示しています。関数名、括弧、コンマ、ダブルコーテーションなどは全て半角で入力してください。
★「金額」や「単価」、「数量」などの＜数値＞に使えるもの
| 関数例 or 抽出条件 の記述例 | 実行結果と説明 |
|---|---|
| In（500,1000） | In（数値） 複数の場合はカンマ区切り |
| Avg（[合計] ） | [合計] の平均値を算出 / 集計関数 |
| DAvg（”単価”,”Ｍ_商品”） | 「M_商品」の「単価」の平均値を算出 / 定義域集計関数 |
| Count（[商品名]） | [商品名]フィールドのデータ数をカウント / 集計関数 |
| DCount（”[顧客名]” , “M_顧客”） | [顧客名]フィールドのデータ数をカウント / 定義域集計関数 |
| Sum（[単価] ＊ [数量] ） | 単価 × 数量の合計 / 集計関数 |
| Partition（[金額] , 1 , 500 , 100） | １～100 101～200・・・ / データベース関数 |
| IIf（[金額] >= 10000 , “ポイント2倍” ） | 金額が10,000円以上ならば、「ポイント2倍」と表示 |
集計関数と定義域集計関数の違い
集計関数はSQLステートメント（SQL文）に記述できますが、VBから直接呼び出すことはできません。定義域集計関数は、SQLステートメント（SQL文）に記述でき、VBから直接呼び出すこともできます。これらの関数は、列のデータの項目を＜グループ化＞した後、その列に対して使用する関数です。
よって、集計関数も定義域集計関数も、＜グループ集計＞や＜クロス集計＞などで主に使用します。
DAvg関数
DAvg（”単価”,”Ｍ_商品”）
「フィールド名」と「レコードセット名」は必ず指定する必要があります。レコード設定名は「テーブル名」でも、「クエリ名」でもＯＫです。
DCount関数
「フィールド名」と「レコードセット名」は必ず指定する必要があります。レコード設定名は「テーブル名」でも、「クエリ名」でもＯＫです。
Partition関数
Partition（数値 , 最小値 , 最大値 , 間隔）
時間帯: Replace(Partition(Format([注文時刻],”h”),0,24,3),”:”,”～”) & “時”
上記の例では、Format関数で[注文時刻]を「時」のみに変換し、Partition関数で「時間帯」を設定しています。さらに、Replace関数を使用し、「:」を「～」に置き換えています。
IIf関数
比較的良く使うIIf関数。条件に一致したレコードに対して、何かを操作することができます。Yesの場合は「必須」ですが、Noの場合は「任意」となっています。
【記述例 1】
IIf（[金額] >= 10000 , “ポイント2倍” , “ポイント無し” ）
上記の例では、金額が「10,000円以上」であれば、「ポイント2倍」と表示します。そうでなければ（10,000円未満）、「ポイント無し」と表示します。
◆IIf関数で使える記述例
| 記述例 | 実行例 |
|---|---|
| IIf（[金額] >= 10000 , “ポイント2倍” ） | 10,000円以上なら、ポイント２倍と表示 |
| 判定: IIf(Format([注文日],”yyyymd”)=2015618,”ラッキー”) | 2015/6/18日なら、「ラッキー」と表示 |
| 判定: IIf([小計]<=10000,Format([小計]*1.05,”#,###”)) | 金額が10,000円以下なら、5%の配送料上乗せ |
| IIf( Format([注文日],”yyyymd”)=2015618,”セール”) | 注文日が2015/06/18なら、「セール」と判定させる |
★「注文日」「時刻」などに使えるもの
| 関数例 or 抽出条件 の記述例 | 実行結果と説明 |
|---|---|
| #2015/08/09# | 2015/08/09のレコードを抽出 日付の前後を#で囲む |
| ＜#2015/08/09# | 2015/08/09 以前のレコードを抽出 |
| >=#2015/08/09# And <=#2015/12/30# | 2015/08/09～2015/12/30のレコードを抽出 |
| Between #2015/08/09# And #2015/12/30# | 上記と同じレコードを抽出 |
| Not Between #2015/08/09# And #2015/12/30# | 2015/08/09～2015/12/30以外のレコードを抽出 |
| Year（ [注文日] ） = 2015 | 注文日が2015年のレコードを抽出 |
| Month（ [注文日] ）= 7 | 注文日が７月のレコードを抽出 |
| Date（ [注文日] ）= 21 | 注文日が21日のレコードを抽出 |
| Format（ [注文日] , “yyyy￥年” ） | 注文日（2015 / 08 /12）を2015年に変換して表示 |
| DatePart（”q” , [注文日]） = 1 | 注文日が第一四半期(1月～3月) のレコードを抽出 |
| １(1月～3月) ２(4月～6月) ３(7月～9月) 4(10月～12月) | |
| CDate（”平成27年08月09日”） | 日付けと認識できる文字列を「2015/08/09」に変換する |
| DateDiff（”m” , #2010/01/01# , #2010/07/01#） | 指定された日付から日付を引き、結果を返す（ｍは月を表す） |
| Datepart（”y” , #2010/7/15#） | 196（1月1日から数えて196日） |
| DateSerial（2015,08,09） | 2015/08/09 指定した数値から日付値を返す。 |
| IIf( Format([注文日],”yyyymd”)=2015618,”セール”) | 注文日が2015/06/18なら、「セール」と判定させる |
Year関数
日付型のデータを操作する関数です。
「Year関数」の他に、「Month関数」、「Date関数」も同様です。
【記述例 1】
Year（ “2015 / 08 / 12” ） or Year（ [注文日] ）
上記の例では、日付けに設定されたものから年のみを抜出します。つまり、「2015」年のみを取得します。
【記述例 2】
Year （”2015 / 08 / 12”） & ”年”
上記の例では、抜出した「2015」に、文字列「年」を連結しています。
つまり、「2015年」となります。
Format関数
Format関数は、書式により、様々な形式に変換することができます。詳細は、書式文字一覧を参照してください。関数の中で最も頻繁に使用する関数です。
書式文字は全て覚えるのは困難なので、その都度、参照して使用しましょう。
【記述例 1】
Format（ [注文日] , “yyyy￥年” ）
上記の例では、注文日（例えば 2015 / 08 /12）を「2015年」に変換して表示します。
◆書式文字一覧
| 書式文字 | 説明 |
|---|---|
| : (コロン) | 時刻の区切り文字を表示します。区切り記号は、Windows の地域の設定で指定します。 |
| / | 日付の区切り記号を表示します。 |
| c | 定義済み書式の [General Date/日付 (標準)] と同じです。 |
| d | 日を 1 桁または 2 桁の数字で表示します (1 ～ 31)。 |
| dd | 日を 2 桁の数字で表示します (01 ～ 31)。 |
| ddd | 曜日を英語の省略形 3 文字で表示します (Sun ～ Sat)。 |
| dddd | 曜日を英語のフル スペルで表示します (Sunday ～ Saturday)。 |
| ddddd | 定義済み書式の [Short Date/日付 (S)] と同じです。 |
| dddddd | 定義済み書式の [Long Date/日付 (L)] と同じです。 |
| w | 曜日を表す数字を表示します (1 ～ 7)。 |
| ww | その日が 1 年のうちの何週目であるかを表示します (1 ～ 53)。 |
| m | 月を 1 桁または 2 桁の数字で表示します (1 ～ 12)。 |
| mm | 月を 2 桁の数字で表示します (01 ～ 12)。 |
| mmm | 月を英語の省略形 3 文字で表示します (Jan ～ Dec)。 |
| mmmm | 月を英語のフル スペルで表示します (January ～ December)。 |
| oooo | 月の名前を日本語で表示 |
| q | その日が属する四半期を表示します (1 ～ 4)。 |
| g | 年号の頭文字を表示（Ｍ、Ｔ、Ｓ、Ｈ） |
| gg | 年号の先頭の頭文字を漢字で表示（明、大、昭、平） |
| ggg | 年号を表示（明治、大正、昭和、平成） |
| e | 年号に基づく和暦の年を表示。１桁の場合、先頭に０はつかない |
| ee | 年号に基づく和暦の年を２桁の数値で表示。1桁の場合、先頭に0がつく |
| y | 同じ年の 1 月 1 日からの日数を表示します (1 ～ 366)。 |
| yy | 西暦の下 2 桁を表示します (01 ～ 99)。 |
| yyyy | 西暦を 4 桁で表示します (0100 ～ 9999)。 |
| h | 時を 1 桁または 2 桁の数字で表示します (0 ～ 23)。 |
| hh | 時を 2 桁の数字で表示します (00 ～ 23)。 |
| n | 分を 1 桁または 2 桁の数字で表示します (0 ～ 59)。 |
| nn | 分を 2 桁の数字で表示します (00 ～ 59)。 |
| s | 秒を 1 桁または 2 桁の数字で表示します (0 ～ 59)。 |
| ss | 秒を 2 桁の数字で表示します (00 ～ 59)。 |
| ttttt | 定義済み書式の [Long Time/時刻 (L)] と同じです。 |
| AM/PM | 12 時間制の時刻に、大文字の “AM” または “PM” を付加して表示します。 |
| am/pm | 12 時間制の時刻に、小文字の “am” または “pm” を付加して表示します。 |
| A/P | 12 時間制の時刻に、大文字の “A” または “P” を付加して表示します。 |
| a/p | 12 時間制の時刻に、小文字の “a” または “p” を付加して表示します。 |
| AMPM | 12 時間制の時刻に、”午前”と”午後”の識別子を付加して表示します。 |
◆時間の変換でつかえる記述例
[注文時刻]には「08 : 05 : 01」（時：分：秒）の形式でデータが挿入されているとします。記述例は「クエリのフィールド」に記述します。
実行例は、「実際に表示される結果」となります。
| 記述例 | 実行例 |
|---|---|
| 時刻: Format([注文時刻] ,”hh : nn”) | 20 : 05 |
| 時刻: Format([注文時刻],”h\時”) | 8時（一桁表示） |
| 時刻: Format([注文時刻],”hh\時”) | 08時（二桁表示） |
| 時刻: Format([注文時刻],”AM/PMhh\時”) | PM08時（二桁表示） |
| 時刻: Format([注文時刻],”hh””時（””am/pm）”) | 08時（pm） |
| 時刻: Format([注文時刻],”ampmhh\時”) | 午後08時 |
| 時刻: Format([注文時刻],”hh\時nn\分”) | 20 時 00 分 |
◆日付の変換で使える記述例
[注文日]には「2015 / 08 / 12」（年：月：日）の形式でデータが挿入されているとします。記述例は「クエリのフィールド」に記述します。
実行例は、「実際に表示される結果」となります。
| 記述例 | 実行例 |
|---|---|
| 注文日付: Format([注文日],”yyyy\年”) | 2015年 |
| 注文日付: Format([注文日],”yyyy\年mm\月”) | 2015年08月 |
| 注文日付: Format([注文日],”yyyy\年mm\月dd\日”) | 2015年08月12日 |
| 注文日付: Format([注文日],”yyyy\年mm\月dd(aaa)”) | 2015年08月02(水) |
| 注文日付: Format([注文日],”yyyy\年mm\月dd(aaaa)”) | 2015年08月02(水曜日) |
| 注文日付: Format([注文日],”gee””年 “”mm\月dd\日”) | H27年 08月12日 |
| 注文日付: Format([注文日],”gggee””年 “”mm\月dd\日”) | 平成27年 08月02日 |
★「商品名」や「顧客名」などの＜文字列＞に使えるもの
| 関数例 or 抽出条件 の記述例 | 説明 |
|---|---|
| In（”消しゴム”,”鉛筆”） | 「消しゴム」または「鉛筆」のレコードを抽出 |
| Like “ペン＊” | 先頭に「ペン」が含まれるレコードを抽出 |
| Like “＊ペン＊” | 文字列の一部に「ペン」が含まれるレコードを抽出 |
| Like “＊ペン” | 最後に「ペン」が含まれているレコードを抽出 |
| IIf（[小計] >= 10000,”優良顧客”） | 小計が10,000円以上なら、「優良顧客」と表示させる |
| Left（[住所] , 3） | 「住所」の左端から3文字を取り出す |
| Right（[住所] , 3） | 「住所」の右端から3文字を取り出す |
| Mid（[住所] , 3 , 3） | 「住所」の左端から3番目の文字から3文字を取り出す |
| Replace（[商品名] ,”ペン” , “ペンシル” ） | 「ペン」を「ペンシル」に置き換える |
| Trim（[顧客名]） | 「顧客名」の前後にあるスペースを削除する |
| Nz（[顧客名] , “名無し”） | 「顧客名」がNullの場合、「名無し」に変換する |
| StrConv（[商品名] , 16） | ひらがなをカタカナに変換する |
Mid関数
特定箇所の文字列を操作する関数です。
「Mid関数」の他に、「Left関数」「Right関数」なども同様です。
【記述例 1】
Mid（”岐阜県羽島市” , 4 , 3）
上記の例では、「岐阜県羽島市」の左端から４文字目を開始位置とし、３文字分を抜出します。文字列操作で割とよく使うことになるかもしれません。
Nz関数
Null値かどうかを判定するための関数です。
【記述例 1】
Nz（ 「顧客名」 , “名無し” ）
上記の例では、「顧客名」がNull値だった場合、「名無し」という文字列に変換して表示させます。
【記述例 ２】
=Nz(DMax(“[顧客ID]”,”M_顧客”)+1,1)
上記の例では、主キーが＜数値型＞などの場合に、フォームの顧客IDなどのコントロールソースで使用します。通常、＜数値型＞の主キーは自動的にカウントアップできません。しかし、上記コードをコントロールソースに付与することで、自動的にカウントアップさせます。Dmax関数は、「M_顧客テーブル」の「顧客ID」の最大値を取得しています。
つまり、顧客IDの最大値を取得して、「＋１」した値をフォームに表示させます。
また、Nz関数で、最大値を取得できなければ（データが一件もない場合）、「１」を表示させます。
Replace関数
文字列を特定の文字列に置き換える関数です。
【記述例 1】
Replace（[商品名] ,”ペン” , “ペンシル” ）
上記の例では、「商品名」に「ペン」が使われている文字列を、「ペンシル」に置き換えます。Replace関数は主に、クロス集計などで使うケースもあります。
【記述例 2】
時間帯: Replace(Partition(Format([注文時刻],”h”),0,24,3),”:”,”～”) & “時”
上記の例は「入れ子」になっているため、少しわかりにくいかもしれません。本来なら「0:2時」となるフィールド名を、Replace関数で「0 ～ 2時」の形式に変換しています。
「:（セミコロン）」と「～」と置き換えているわけです。
StrConv関数
「大文字」 ⇔ 「小文字」、「ひらがな」 ⇔ 「カタカナ」などに変換する関数です。
変換方法のコードをしてすることで、実行結果が変わります。
【記述例 1】
StrConv（[顧客名] , 16 ）
上記の例では、＜ひらがな＞の「顧客名」を、＜カタカナ＞の顧客名に変換します。
StrConv関数で使える記述例
| 記述例 | 実行例 |
|---|---|
| 氏名: StrConv（[顧客名] , 1 ） | 英字を大文字に変換 |
| 氏名: StrConv（[顧客名] , 2 ） | 英字を小文字に変換 |
| 氏名: StrConv（[顧客名] , 3 ） | 英字の単語の先頭を大文字に変換 |
| 氏名: StrConv（[顧客名] , 4 ） | 半角文字を全角文字に変換 |
| 氏名: StrConv（[顧客名] , 8 ） | 全角文字を半角文字に変換 |
| 氏名: StrConv（[顧客名] , 16 ） | ひらがなをカタカナに変換 |
| 氏名: StrConv（[顧客名] , 32 ） | カタカナをひらがなに変換 |
| 氏名: StrConv（[顧客名] , 128 ） | Unicocodeに変換 |