ACCESSの型変換（日付型）について

質問

諸賢者の皆様、よろしくお願いいたします。
【環境】
OS：WindowsXP
ソフトウエア：ACCESS2003
【やりたいことの概要】
　お客様より作業依頼を受領し、作業種別に応じて予め設定された作業期限を関数により求め、所定フィールドに設定したい。
【前提】
（１）テーブル「納品期限管理テーブル」
　フィールド（１）：プライマリーID（長整数型、固有値）
　フィールド（２）：作業番号（長整数型、固有値）
　フィールド（３）：受領日（日付型　書式は「yy/mm/dd hh:nn AM/PM」に設定）
　フィールド（４）：納品期限（テキスト型）
（２）関数
　モジュールに次の関数を作成。
Function 納品期限(作業番号 As Integer, 受領日 As Date, 受領時間 As Date) As Variant
Dim date_received As Date '受領日
Dim time_received As Date '受領時
date_received = DateSerial(Year(受領日), Month(受領日), Day(受領日))
time_received = TimeSerial(Hour(受領時間), Minute(受領時間), Second(受領時間))
Select Case 作業番号
Case 101:　'当日17時30分までの納品
納品期限 = date_received + TimeSerial(17, 30, 0)
Exit Function
Case 201:　'翌日17時30分までの納品
納品期限 = (DateAdd("d", 1, date_received)) + TimeSerial(17, 30, 0)
Exit Function
Case 301:　'お客様との調整
納品期限 = "お客様との調整"
Exit Function
Case Else
納品期限 = ""
End Select
End Function
【これまでの試み】
（１）イミディエイトウインドウ
　「?納品期限(101,#2008/5/20#,"9:00")」と入力すると「2008/05/20 17:30:00」と表示される。問題なし。
（２）フィールド更新
　データを更新するため次のSQL文を実行する。
　「UPDATE Job_List SET Job_List.担当受付日時 = 納品期限([作業番号],"#" & [担当受付日時] & "#","#" & Format([担当受付日時],"HH:NN") & "#");」
　■ここで問題発生。「型変換エラー」メッセージが出て、データを更新できません。

　どなたか、解法を御教示願います。

回答No.1

nda23 · ベストアンサー

「担当受付日時」の属性は何ですか？
日付型なら "#" & [担当受付日時] & "#" ではなく、[担当受付日時]と記述します。
文字型ならFunction 納品期限()の引数を文字型とし、関数内で、IsDate、CDate関数を
使ってチェックと変換を行うべきです。

あと、気になった点を少し直してみました。
Function 納品期限(ByVal 作業番号 As Integer, ByVal 受領日 As Date, ByVal 受領時間 As Date) As Variant
Select Case 作業番号
　　Case 101:　'当日17時30分までの納品
　　　　納品期限 = Format(受領日, "YYYY/MM/DD 17:30:00")
　　Case 201:　'翌日17時30分までの納品
　　　　納品期限 = Format(DateAdd("d", 1, 受領日), "YYYY/MM/DD 17:30:00")
　　Case 301:　'お客様との調整
　　　　納品期限 = "お客様との調整"
　　Case Else
　　　　納品期限 = ""
End Select
End Function

(1)パラメータにByValキーワードを付けました。
(2)受領日の0時0分0秒を作る意味はないのでは？
(3)受領時間は最終的に使用されないようです。

それと、SQLの中でユーザ関数を呼び出すと、遅いので、今回のように
４通りくらいならIif関数をネストしてみてはいかがでしょう。
=Iif(作業番号=101,Format(受領日,"YYYY/MM/DD 17:30:00")
,Iif(作業番号=201,Format(DateAdd("D",1,受領日),"YYYY/MM/DD 17:30:00")
,Iif(作業番号=301,"お客様との調整","")))

お礼

☆　御礼　☆
　御指導に基づく作業を行い、次のSQL文でフィールドデータを更新できました。　
UPDATE Job_List SET tbl.納品期限 = 納品期限([作業番号],[受領時間]);
　これより、各種処理・画面の製作に移ります。
　本当にありがとうございました。
＜修正作業概要＞
１　関数の修正
　大変ありがとうございました。
　特に、「Format(DateAdd("d", 1, 受領日), "YYYY/MM/DD 17:30:00")」の表現については全く未知のことでした。
２　テーブルの再作成
　テーブルの設計による障害を避けるため、テーブルを再作成しました。
　　　　　　　　　　　　　　　　　　拝

補足

早速のご指導、まことにありがとうございました。
　ご指摘の点について、記述誤り、表現不足がありましたことをお詫び申し上げます。
　取り急ぎ御指導に基づき修正作業を行い、あらためて御礼を申し上げたく存じます。
　よろしくお願いします。

＜ご質問＞
「担当受付日時」の属性は何ですか？
＜ご質問に対する回答＞
下記に示すように、「受領日」の誤りであり、「受領日」は日付型としております。

＜記述誤り＞
----------誤り１----------
【前提】
（１）テーブル「納品期限管理テーブル」　
----------修正１----------
（１）テーブル「Job_List」
----------誤り２----------
【これまでの試み】
（２）フィールド更新
　データを更新するため次のSQL文を実行する。
「UPDATE Job_List SET Job_List.担当受付日時 = 納品期限([作業番号],"#" & [担当受付日時] & "#","#" & Format([担当受付日時],"HH:NN") & "#");」
----------修正２----------
「UPDATE Job_List SET Job_List.納品期限 = 納品期限([作業番号],"#" & [受領日] & "#","#" & Format([受領日],"HH:NN") & "#");」

＜表現不足等＞
・時間について
　記述サンプルは簡略化してあり、午前中に作業依頼を受領したものは当日中に、午後のものは翌日に納品します。
　よって、受領時間は必要な情報です。ただし、御指導により自分なりに考えた結果、「受領日」が日付と時間の情報をもっていたら、引数として組み込むことは不要と判断しますので、一から作り直します。
・Iif関数について
　有益な指導、ありがとうございます。ただ、作業種別は全部で３９種あり、納品までの期限設定ルールは１３種あります。また、営業日を勘案する処理を行う必要から、case文で処理したいと存じます。
　以上