SELECT min( ymd ) As d_from
, max( ymd ) As d_to
FROM
(
SELECT x.ymd
, count(1) As n
FROM q_ymdlist x
, q_ymdlist y
WHERE x.ymd >= y.ymd
GROUP BY x.ymd
) q
GROUP BY ymd - n ;
q_fromto
d_from
d_to
2021/08/11
2021/08/12
2021/08/23
2021/08/23
SELECT x.d_from
, x.d_to
, max( iif( y.終了日 Is Null, y.開始日 ) ) As high
, max( y.開始日 ) As low
FROM q_fromto x
LEFT JOIN 単価マスタ y
ON (
(
x.d_from > y.開始日
AND
y.開始日 >= dateserial( year( date() ), month( date() ), 1 )
)
)
GROUP BY x.d_from
, x.d_to ;
q_highlow
d_from
d_to
high
low
2021/08/11
2021/08/12
2021/08/01
2021/08/05
2021/08/23
2021/08/23
2021/08/13
2021/08/20
SELECT x.d_from
, x.d_to
, y.開始日 As 基準開始日
, 1 As 補填行
, y.単価
FROM q_highlow x
, 単価マスタ y
WHERE cdate( nz( x.high, x.low ) ) = y.開始日 ;
q_union
d_from
d_to
基準開始日
補填行
単価
2021/08/11
2021/08/12
2021/08/01
1
150
2021/08/23
2021/08/23
2021/08/13
1
100
SELECT 開始日 As 指定開始日
, 終了日 As 指定終了日
, 開始日 As 基準開始日
, 0 As 補填行
, 単価
FROM q_base
UNION ALL
SELECT d_from
, d_to
, 基準開始日
, 補填行
, 単価
FROM q_union
ORDER BY 1 ;
Private Sub 日付_AfterUpdate()
Dim res
res = DLookup("単価", "単価マスタ", "#" & Me.日付 & "# Between 開始日 AND 終了日")
If IsNull(res) Then
res = DMax("開始日", "単価マスタ", "#" & Me.日付 & "# >=開始日 AND 終了日 Is Null")
If Not IsNull(res) Then
Me.単価 = DLookup("単価", "単価マスタ", "#" & res & "#=開始日")
End If
Else
Me.単価 = res
End If
End Sub
hirotonさんの提示したテーブルなら、下記の1行のコードで済みます。
Me.単価 = DLookup("単価", "単価マスタ", "#" & Me.日付 & "# Between 開始日 AND 終了日")
Private Sub 日付_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("日付", "テーブル名", "日付=#" & Me.日付 & "#")) Then
MsgBox "既に入力された日付です。異なる日付を入力してください"
Cancel = True
End If
End Sub
Private Sub 日付_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("日付", "テーブル名", "日付=#" & Me.日付 & "#")) Then
If MsgBox("既に入力された日付です。登録してもいいですか?", vbOKCancel) = vbCancel Then
Cancel = True
End If
End If
End Sub
此方32bit版なのであれですが、『Set rs = Me.Recordset』みたいな記述をよく見ます。
32bit版の問題が起こらないコード、64bit版の問題が起きるコード、エラーメッセージの有無、もう少し詳しく出さないのでしょうか?
ご回答ありがとうございます。
そちらは確認してしましたが、@はありませんでした。
キーになる項目と当該長いテキスト型のフィールドだけを別シートに
出力するとフルで出力されました。ですので、現状、それを別シートに吐き出し、
本来出力したいフィールドは空データで出力し、最後に、VlookUp関数で、
別シートを参照するような方法でひとまずは回避いたしました。
特に根拠は無いですけど
個人的には、以下の2つを試してみます。
【 1 】
Me.Data1.ControlSource = rs![data1].Name
Me.Data2.ControlSource = rs![data2].Name
の記述を
Data1.ControlSource = rs![data1].Name
Data2.ControlSource = rs![data2].Name
として、Me. を外してみてもエラーが出るのか
【 2 】
Set Me.Recordset = rs
の後に
Requery 系メソッドを発行してる箇所があったらコメントアウト
テーブルをデザインビューで開いて
該当フィールドの 書式プロパティに「@」の記述があるようでしたら
消してみて下さい。
何をしたいのか良く分かってないんですが、
やり取りじゃなくて一方通行でいいのなら
●ファイルB側に以下を作成
標準モジュール
マクロ1
●ファイルA側で以下を実行
`
ファイルAで指定したレコードのIDをファイルBのフォームで拾ってどうのする、という考えで書いてますが、
単純にフォーム開くだけでいいならマクロ1の中身を「フォームを開く」とかにして
ファイルA側のコマンドの「/cmd」以降は不要です。
なお当方2003環境ないので試してないです。
実装しました。結果、9500行を16秒でINSERTできたのでだいぶ改善されました。
ありがとうございました。
未だWIN7にてACCESS2003を使っています。ACCESSファイルAのフォーム上ボタンからACCESSファイルBのフォームを開きたいのです。ネットで調べて”Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase***等のコードを記述したところ記述直後は動作するのですが、一旦ASSESSを終了して再度動作させてみるとフリーズしてしまうのです。何度同じ事をしても一緒です。意味がわからなくて。SheLLを使うとACCESSファイルBは何度でも開くのですがそこからリンクさせた所定のフォームを開く方法がわかりませんファイルAフォームのIDと関連させたファイルBフォームを開きたいのです。
コメント追加です。
テーブルにはフルで文字列が保存されているものの、クエリのデータシートビューで開くと、そのフィールドは255文字までしか表示されいません。
有難うございます。参考情報をみてトライしてみます。
おそらくですが、VB.NET で利用なさっているのが
ADO ではなく、ADO.NET の DataSet だからではないでしょうか。
レガシーADO は OLE DB プロバイダー
ADO.NET は .NET Framework データプロバイダー
とアーキテクチャも異なっており、
名前が似てはいますけど、仕様は全くの別物になっています。
一応、SQLServer 側のテーブルから Acccess のテーブルへ
Insert文を発行する例を載せておきます。
レスありがとうございます。
VB.NETの方でしたら、レコードセットの受け渡しをしてテーブルに反映ができましたので、
Accessもできると思っていました。
残念ながら、無理でしょう。
ご自身で書かれているように、INSERT文を用いて TBL2 にデータをコピーして下さい。
理由:
RstD のインスタンスを Rst0 に単純コピーするだけでは
Rst0 は、単に RstD のクローンになるだけで無意味ですし、
コピーした後に Rst0 の ActiveConnection を Nothing に設定すると
Rst0 は、どの DB とも接続されていない 独立した ADODB.Recordset にはなります。
で、Rst0 を Access 側である TBL2 の ADODB.Recordset として扱うためには
ActiveConnection プロパティと Source プロパティを書き換える必要がありますけど
Source プロパティは Recordset.Open 後、読取り専用のプロパティになっています。
結局、Source プロパティを書き換えるためには Recordset を一度閉じる必要があるのですけど
言うまでもなく、Closeメソッドを呼び出すと
Rst0 に内包されていたデータは消滅してしまいます。
この度はいろんな方に御指南していただきまして、
ありがとうございました。
無事、hirotonさんの記載くださった方法で、
問題が解決しました。
どうもありがとうございました。
ありがとうございます
クエリで日付から単価や開始日を呼び出したりする形で使いたかったので
hatenaさんのコードをもとに標準モジュール(日付を入数化)にする形で対応できました
解決しました!
ありがとうございました。
'TBL1からTBL2へデータをコピー
'接続1
'SQL server
Dim StConnectDB As String
Dim dCnn As New ADODB.Connection
dCnn.ConnectionString = "Provider=SQLOLEDB; " & _
"Data Source=SALESSV2\ABCINSTANCE; " &
"Integrated Security=SSPI;Initial Catalog= 'AAA'"
dCnn.Open
Dim RstD As New ADODB.Recordset
Dim Sql As String
Dim fDate As Date
Dim tDate As Date
fDate = "2021/08/01"
tDate = "2021/08/31"
Sql = "select * from TBL1 " &
"where planDate between '" & fDate & "' and '" & tDate & "' " &
"order by ID"
RstD.Open Sql, dCnn, adOpenKeyset, adLockOptimistic
'接続2
'Access local
Dim Cnn As New ADODB.Connection
Dim Rst0 As New ADODB.Recordset
Set Cnn = CurrentProject.Connection
Rst0.Open "TBL2", Cnn, adOpenKeyset, adLockOptimistic
Set Rst0 = RstD.Clone
'TBL2に反映させたい
'while で addnewすれば書き込めるが、 SQLのinsertのように一気に書き込みたい
'TBL1とTBL2は構造は同じ
Cnn.Close
ありがとうございます。
クエリの抽出条件の欄に、
=[Forms]![フォーム名]![コンボボックス名]
と入力すれば良いのでしょうか?
入力してクエリを実行したのですが、パラメータの選択も求められず、何もデータのない結果のみが表示されてしまったのですが…
よろしくお願いいたします。
フォーム上にコンボボックスを配置して、クエリではそれを参照するようにすればいいでしょう。
下記のような式で参照できます。
途中でもいいので現状のコードを提示してください。
上記で紹介している関数を使えば、
例えば
のようなデータの時、
指定日=2021/08/15の場合すると
と、2レコードHITします
これは、つまり、このようなレコードは存在しないということでしょうか?だとすると、終了日に値の存在が許されるレコードは1つだけという歪なテーブル構造なんでしょうか?
質問の解自体はhatenaさんの回答の通りでしょう。ただ、手順や処理時間で見た簡略化は見込めないと思います。この観点から言えば
ないです
しかしながら、データ構造の観点から言えば、「一時的に変わる時」のデータは単純に追加するだけで済むというメリットができ、データ管理面での単純化なら見込めます
さらに言えば、既に指摘している通り、なぜ最終的なデータ構造でテーブルを作らないのか?という話になります。データ構造が単純で、データの取得もシンプルです
単純=「簡単」とは限らないですが、テーブル構造からはわからない独自ルールを運用するよりはよほど「簡単」でしょう。
本当の主題はこれですよね?
ならば、職人芸のようなテーブル構造・データ取得を考えるよりも単純に「期間限定明け後のデータ入力(ここでは8/11~8/19)を」自動化したいと考えるべきでしょう
( 続き )
q_fromto
q_highlow
q_union
q_result
( 続き )
# 最初は SQLの一筆書きを投稿しようとしたところ
# 160行の記述が長すぎて投稿不可であったことと
# サブクエリのネストが深くて、記述が複雑怪奇になったため、
# 構造別にクエリを複数( 計7つ )作るという回答に切り替えました。
まぁ...個々の記述が短いと 最終結果が出るまでの工程も分かりやすいでしょう。
( クエリを作る順番も、掲載した順と同じになります )
q_base
q_ymd
q_ymdlist
私個人は、hirotonさんやhatenaさんと同じく
ソリューションとしては 圧倒的に
{ テーブルデータの保有形式を変更 } >>> { トリッキーな SQL または VBA }
という見解です。
ただ Access の一般機能で「 実現可能 」な ご要望であることも事実ですから
実用性とパフォーマンスは度外視で、SQL( クエリ )のサンプル を載せておきます。
( ※ 今回の投稿はテーブル定義とデータ例のみ )
なお、( 何らかの )日付範囲の抽出条件を満たした後のデータが
「 例 」の3行であるとするなら
「 指定日付 」に該当するような パラメータなりリテラル値は
開始日フィールドに指定された可能性が高いと推測し、
私の回答( SQL )では、現状のテーブルデータに影響を与えない抽出条件として
を指定しています。
■テーブル: 単価マスタ
■テーブル: T_num
( T_num テーブル の num には 0 ~ 9 までの数値を入力 )
下記が参考になると思います。
レポートをグループ毎に分割してPDFファイルとして保存したい - hatena chips
有り難うございます!やりたいことが実現できました。大変助かりました。
追加の質問があります。
1つのボタンで、一覧に表示されている請求書を1件つづ、自動的にPDF保存を繰り返し行わせるには、どのように記述したら
よいでしょうか。
可能であれば、ご教示頂きたく、よろしくお願いいたします。
hirotonさんの提示したテーブルのような形にしない限りは、簡単にはならないと思います。
どのようなUIなのか不明なので、
フォーム上に「日付」と「単価」というテキストボックスがあると仮定すると、
下記のようなコードで期待する結果にはなります。
hirotonさんの提示したテーブルなら、下記の1行のコードで済みます。
2レコード以上は範囲入力側の制御の時点で入れられない形にしています
>(8/5~8/10 & 8/6~8/11のような複数で日付が被る形はありません)
あくまで終了日付が入るのは期間限定の臨時の時だけで基本的には開始日で単価を制御するようになっており、
単価情報を入力する側が期間限定明け後のデータ入力(ここでは8/11~8/19)を意識しなくてもいい形にしたいというものです
これ、結果は一意じゃないですよね。2レコード以上HITしたらどうしてるんですか?
このデータ形式でテーブルを作らないのは何故なんでしょう?
重複を許さないだけならすでに挙げた方法でいいですが、既定のメッセージじゃ使いにくいのはありますね。2つ目の方法でメッセージを出してキャンセルする(=条件分岐を入れない)ようにすればいいです
返信がわかりづらかったので、
再投稿です。
(レスの引用の仕方が
いまいちよく把握できてません)
りんごさん、hirotonさん
お二人様
御指南ありがとうございます。
どちらもかなり難しそうなので、
しばらくお時間をください。
ちょっと研究してみようと思います。
>hirotonさん
>これはOKなら重複を許すということですか?
これは、自分で書きながら、
改めて考えると重複を一切許さない方がいいかな?
と思いました。重複のお知らせと
入力キャンセルのみのメッセージボックスが
開いた方がいいかもしれないと思いました。
こうした場合は、どうしたらよいでしょう?
りんごさん、hirotonさん
お二人様
御指南ありがとうございます。
どちらもかなり難しそうなので、
しばらくお時間をください。
ちょっと研究してみようと思います。
>hirotonさん
これはOKなら重複を許すということですか?
これは、自分で書きながら、
改めて考えると重複を一切許さない方がいいかな?
と思いました。重複のお知らせと
入力キャンセルのみのメッセージボックスが
開いた方がいいかもしれないと思いました。
こうした場合は、どうしたらよいでしょう?
ごめんなさい、こちらにミスがありました。『リンク親フィールド:日付ID、リンク子フィールド:日付ID』のつもりが、『リンク親フィールド:日付、リンク子フィールド:日付』となっていました。サブフォームで示した件は間違いです。
下記のような感じでどうでしょうか。
hiroton様
確かにそうですね。。。
アドバイスの通りに処理を入れ替えてみました。
結果は30秒ほどで処理が完了し、Excel側も希望通りの値に変更になっておりました。
本当にありがとうございました。
閉じた後に設定の変更って効く(意味がある)んですかね?
あと、設定を有効にする前に保存しているから設定が保存されないのでは?
処理の順番を入れ替えたらどうでしょう?
ついでに、主体はExcel VBAの話なので、解決が難しいようならExcel VBAに強い質問サイトを探すといいと思います
インデックスを使用してテーブル フィールドの値の重複を防ぐ
これはOKなら重複を許すということですか?
上記方法はデータベース的に重複を許さない形なので、これをやりたい場合は自前で制御を組む必要があります。更新前処理イベントで入力しようとした値がすでに登録されているかどうかチェックすればいいですね
まずは、状況確認ですが、もしかしてこんな感じですか?
あと、どのテキストボックスがイメージに近いでしょうか?
親テーブル:日付ID,日付
子テーブル: 主キー,日付ID,その他
メインフォーム連結クエリ:日付ID,日付
サブフォーム連結クエリ:主キー,日付ID,日付,その他
↪フォームヘッダー
日付テキストボックス(非連結):⬜⬜⬜
↪詳細
日付ID:(新規)
日付 :⬜⬜⬜
↪フォームフッダー
📋サブフォーム
↪サブフォームヘッダー
日付テキストボックス(非連結):⬜⬜⬜
↪サブフォーム詳細
↓
↓
↓
↓
2021/08/18↓ 親テーブル
いろいろな見解があると思いますが、
私は非連結フォームはほぼ使いません。
よってDAOからレコード操作もほとんど使いません。
どこにそのような情報があったか知りませんが、
クエリを使わないなら、Accessで開発する意味はないと思います。