Function MyBase(数値 As Long, 基数 As Long, 桁数 As Long) As String
Const num = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim q As Long '商
Dim r As Long '余り
If 基数 <= 1 Or 基数 > 36 Or 数値 < 0 Then
Exit Function
End If
q = 数値
Do
r = q Mod 基数
q = q \ 基数
MyBase = Mid(num, r + 1, 1) & MyBase
Loop While q > 0
MyBase = Right(String(桁数, "0") & MyBase, 桁数)
End Function
入力フォームの挿入前処理に下記のコードを設定する。
Private Sub Form_BeforeInsert(Cancel As Integer)
Randomize
Dim DNum As String
Do
DNum = Format(Int(Rnd * 99999999), "0000-0000-") & MyBase(Rnd * 1679615, 36, 4)
Loop Until IsNull(DLookup("伝票番号", "テーブル1", "伝票番号='" & DNum & "'"))
Me.伝票番号 = DNum
End Sub
実行できないのでなんともいえませんが、下記リンク先の内容から考えるに、SELECT下の「SUM(請求単価) AS 請求金額」必要ないのかな、と思えます。
https://qiita.com/fuk101/items/731379d12cd7f5559fb2
＞配置したグラフの表示部分を囲む点線の枠
「最背面」にすると実行時、完全にグラフが表示されたあとに消えます。テーブルを経由しているので、表示までに少しタイムラグがあるようです。
クエリを直に入れられれば改善できそうではあります。
とりあえずエクセルをインポートするVBAを組んではいかがでしょうか。
流れ的には以下の感じかと思います。概略です。
1.取り込みボタンなどでVBAを呼び出す。
2.エクセルをインポートするVBA（ループ形式）で、取り込み対象のExcelの1行を読む。ループ中では各項目を別の変数に一旦保存
3.保存した変数から、キーとなる項目を判別、結果から受注データを登録（または既存の受注データと紐つけるかも判別）
4.登録した受注データのキーIDと一緒に変数の内容を明細テーブルに書き込む
5.ループで全行をまわす
ACCESSで直接入力できているなら、キーの割り振りのルールは決まっていると思いますので、それをインポートのループを使ってVBA内でやるイメージかと思います。
回答有り難うございます
具体的に書きますと、
ACCESSでは受注(伝票)テーブルとその明細が別テーブルになっていて1対多の構造で入力されています
ACCESSで直接入力する分には問題ないのですが
Excelのデータをインポートする必要があります
Excelでは、伝票の概念がなく、ACCESSの明細にあたる部分のみが入力されています
インポートするときに、日付と取引先でまとめて1伝票とし、伝票テーブルに伝票を作成し、1対多の関係にしたいです
丁・番地・号を区切る-（ハイフン）に、別の似たような横棒の記号が使われている
この横棒の記号をハイフンに変換したい
というだけなら、上記の私の回答の方法で抽出して手作業でなんとかなるでしょう。(データ量にもよりますが)
ただ、無規則に入力されたデータは、実際はそれだけでは済まない場合がおおいでしょう。
厳密に住所データの正規化をしようとするとなかなか困難です。
Excel上で利用できる下記のようなWebサービスがありますので、それを使うのも検討されるといいかもしれません。
住所の統一 ★ | ExcelAPI
住所の分割 ★ | ExcelAPI
住所に使われる文字は郵便局のデータベースで確認することができます。ここにアラビア数字を含めれば住所に使われている文字を作ることができます。これだけで一仕事ですが
ここに含まれていない文字があれば、「住所に使われるはずのない文字」となりますが、表記ゆれを考えれば、「リストにはない文字を使っているが正しい住所」のデータがあるかもしれません
文字一覧とのデータチェックもかなり大変です
雑に、楽にやるなら、「日本語」「アラビア数字」「-（ハイフン）」以外の文字ととらえて一括変換してしまえばそれっぽくなると思います
VBAで文字列から日本語を抽出する(Excel作業をVBAで効率化さん)
リンク先の例では「日本語だけを残す（日本語以外の文字を消す（空白に置き換える））」としているので、これにアラビア数字も含めて、置き換え文字を「-（ハイフン）」にしてやればそれっぽくなるんじゃないでしょうか
リファレンスを読んだ限りでは、DAOには接続先DBのトランザクション分離レベルを確認するためのプロパティまたはメソッドは存在しないように見えます。
Accessデータベースエンジン以外への外部DBへの接続にはADOが推奨されていますが、
どうしてもDAOでAccess以外のDBに接続する必要があり、かつ事前にトランザクション分離レベルを知ることができない場合は
外部DB側にトランザクション分離レベルを返却するストアドプロシージャを用意してDAOで呼び出すなどの工夫が必要になるのではないでしょうか。
・Workspace.IsolateODBCTransについて
通常、同じDBへ接続する複数のWorkspaceのODBC接続は共有されますが、Workspace.IsolateODBCTransをTrueにすることで、Workspaceごとに個別のODBC接続を確立することができます。接続先DBがひとつの接続に対し複数のトランザクションの同時実行を許可しない場合は、複数のWorkspaceを用意しこのプロパティをTrueにすることでトランザクションの同時実行が可能になります。
<参考>
Workspace.IsolateODBCTrans プロパティ (DAO)
https://learn.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/workspace-isolateodbctrans-property-dao
ーーーー以下は上記リンク先より引用ーーーー
注釈
状況によっては、同じ ODBC 接続上で同時に発生する複数のトランザクションを保留状態にする必要があります。 これを行うには、それぞれのトランザクションに対して別々の Workspace オブジェクトを開く必要があります。 各 Workspace オブジェクトがデータベースに対して独自の ODBC 接続を確立できますが、これによりシステムのパフォーマンスが低下します。 通常、トランザクションの分離は不要であるため、同じユーザーが開いた複数の Workspace オブジェクトからの ODBC 接続は、既定で共有されます。
Microsoft SQL Server などの一部の ODBC サーバーでは、単一の接続における複数のトランザクションの同時発生は許可されていません。 このようなデータベースに対して同時に複数のトランザクションを保留状態にする必要がある場合は、各 Workspace オブジェクトを開いた直後に、その IsolateODBCTrans プロパティを True に設定します。 これにより、各 Workspace オブジェクトの ODBC 接続が別々に確立されます。
ずずやんさんの言われるように最終的には目視による確認後、手作業での修正になるとは思いますが、
丁・番地・号を区切る記号ということなので、その記号の前後は数字であるということだと思います。
とりあえず下記のような抽出条件で要修正のデーターをある程度絞り込めると思います。
[]内にはハイフンの代わりに使われるような横棒の文字をいれてください。(とりあえず思いついた３文字をいれてます)
これで、前後に数字のある横棒を含むレコードが抽出されます。
わたしが同じ作業をすることになった場合、まず目視で確認するツールを作ると思います。
もし、もとのデータが1万件あったとしても、当てはまるのは数百件ではないでしょうか。
クエリで「-」「一」「－」など「ハイフン」の代わりに使われそうな文字を条件として全て当てはめたクエリを書き、そういった住所が含まれるデータを全て洗い出します。
結果が一覧として表示されるので、目視で確認にながら修正対象のデータを抽出します。
最後に対象となったデータを手動で修正します。
データシートで修正すれば素早く修正できるのではないでしょうか。
住所フィールドは1・2に分かれており、住所1は丁・番地・号まで、住所2は建物名を入力します。
回答で提示して頂いた、住所に使われるはずのない文字はすべてハイフンにするという方法で変換を行いたいのですが、どのようにすればよいでしょうか。AccessでもExcelでも構いませんのでご教示下さい。
無理ですよ。諦めてください
文字とデータの歴史はまぁ、いちいち語るほどでもないんでしませんが、そういう問題と常に戦ってきた歴史です。対処していないシステムから移行する際には全データ1件1件人の目でチェックしなおすしかありません
「住所録」なので例えば次のようなデータですかね
「ハイフン」の代わりに「漢数字の一」を使っているパターンは絶望的です。正しい住所として「漢数字の一」を使っている場合と区別がつきません
それ以外の「ハイフンの代わりの文字」であれば、文字を一文字ずつチェックして住所に使われるはずのない文字はすべてハイフンにするとできるかもしれません
稀に記号を屋号に入れていたりする例を見るので住所に使われるはずのない文字をうまく設定できるかも何とも言えませんけどね
「カタカナの伸ばし棒」と条件を付けているので上記の例を出していますが、建物名まで含まないような住所であれば、使われる文字も限定されるのでいくらかマシになるかもしれません
分割されていないのなら、どうやって区切るか？という新しい問題が発生しますが
すぐにでも直面する例を挙げてみましたが、他にもあれこれ出てくると思います。できることはせいぜい「住所に使わるであろう文字のみのデータはチェック済みとする」くらいでしょう。この「文字のチェック」をするならば、VBAで文字列チェックすればやれます
データベースでは正規化されたデータでないと扱いにくいので、正規化されていないデータの場合は、正規化してから利用するというのが原則です。
Excel側で正規化するのが難しいのなら、いったんAccessにそのままインポートして、それをSQLやVBAを駆使して正規化することになります。
具体的に方法は、現状のAccessのテーブル構成と、エクセルのデータ例などを提示してもらえれば、アドバイスがあると思います。
ちょっとなにを解決したいのかが明確でないのでただの予想ですが、「正規化されていないエクセルの内容をAccessにインポートして受注テーブルと結びつけて保存したい」ということでしょうか。
とりあえず全てのエクセルのデータを読み出して、テーブルに収めてはどうでしょうか。
その後、テーブルデータを一つづつ読み込みながら条件判断し、条件によって「ID」とやらを付加し、受注テーブルと結びつける、という感じ、かもしれません。
なにを持って条件判断するかは制作者歯しだいですね。
自己レスです。とりあえず Workspace.IsolateODBCTrans プロパティ (DAO) でいいのだと思っておきます。
取り急ぎ、一旦AccessのテーブルでSQLServerの結果を受け取り、それをグラフで表示させる方法は実現できました。
今のところ希望通り動作しています。
今回の質問とは関係ないのですが、配置したグラフの表示部分を囲む点線の枠がどうしても消えません。厄介です・・・。
hiroton様
いつもお世話になっております。
わかりました。オペレーターが削除できるのが一番わかりやすそうですね。
＞どのようなエラーが起きるのか？および、それぞれに対する対応については今一度検討の上、必要であれば再度質問してください
ありがとうございます。その時はよろしくお願いします。
どのようなエラーが起きるのか？および、それぞれに対する対応については今一度検討の上、必要であれば再度質問してください
根本的なところは
が問題の原因です。自分（ACCESS）がExcelオブジェクトを作りますよーってしてそのままほったらかしになっている状態ですね
正常に動作したときは
しているので問題が起きません。エラーが発生したとき
.Quitしていいかどうかは分からないので、とりあえずとしておけば、質問のような問題は解決します
hiroton様
透明のコマンドボタンを設置して対応しました。
ありがとうございました！
連携というのは、リンクテーブルのことですか？
それともAdoのSQLServerのテーブルの参照のことですか？
お二方、ご丁寧な説明有難うございました。
無事解決しました。
オートナンバー型を0000-0000-ランダムな英数4桁に加工するのは面倒そうなので、
エクセルと同様の方法で生成して重複チェックをして発番する方法を考えてみました。
まずは、BASE関数に相当するものがAccessにはないので、それを自作します。
標準モジュールに下記の関数を作成します。
入力フォームの挿入前処理に下記のコードを設定する。
これで新規レコード入力時に、重複しない伝票番号が発行されます。
それだと伝票番号が重複する可能性がないとは言えませんよね。
セルに設定した式なら、更新するたびに変化するし。
VBAで値に変換して重複チェックしているのでしょうか。
Accessでするなら、
オートナンバー型のフィールドで、新規レコードの値を「ランダム」に設定しておけば、重複しないランダムな値を発番してくれます。
それをつかって、加工すればどうでしょうか。
印刷内容を設定するそれぞれの領域のことをセクションと呼びます。「レコードを表示させるところ」なら詳細セクションですね
厳密には違う動作ですが、続くセクションの一番上に線（高さ0の長方形）を配置すればそれっぽい見た目になります
ただし、ページフッターだけは特別な表示をされるセクションなので、違うセクションを用意する必要があります。基本的にはレポートフッターセクションで設定すれば良いでしょう
連番ですと、相手に売上規模を知られるので
完全にランダムにしたいです
Excelでは
=BASE(RANDBETWEEN (0,9999),10,4)&"- "&BASE (RANDBETWEEN (0,9999),10,4)&"- "&BASE (RANDBETWEEN (0,1679615),36,4)
としていました
伝票番号のために、わざわざそんなものを作らないといけないの？
すでにhirotonさんから的確な回答が付いていますので、蛇足ですが、
レコードソースに設定したクエリは、フォーム読み込み時にレコードセットとして開かれて読み込まれます。
その後、クエリのSQLを変更したとします。
そこで、フォームを再クエリしても、クエリからレコードセットを再読込せずに、現状のレコードセット（つまり変更前のSQLのまま）として再読込します。
よって、変更したSQLは反映されません。
hirotonさんの回答にあるように、
Where句の変更ならForm.Filterプロパティ、Order By句の変更なら、Form.OrderByプロパティで設定すればいいでしょう。
もし、それ以外の部分でSQLを変更したい場合(例えばFROM句とか...)は、いったんRecordSourceプロパティを解除してから再設定すると反映されます。
フォームに設定したレコードセットに対してWHERE条件を追加したい場合、フォームのフィルター(Filter)プロパティを操作すると良いです
Form.Filter プロパティ (Access)
「レコードソースに設定したクエリ自体にWHERE条件が含まれていて、その条件を変更したい」というのはのはちょっと困難なので、そういう場合はその部分のWHERE条件を外した状態のクエリをレコードソースに設定して、やはりフィルタープロパティで操作すると良いです
デフォルトのWHERE条件を設定したい場合はフォームの読み込み時イベントで設定すればいいでしょう
ありがとうございます。
考え方自体が違ったようですね。
サブフォームに使われているクエリをのWHERE条件を変化させたかったので、ソースのクエリごと毎回生成させようかと思ったのですが。
hiroton様
そのやり方もやってみます。
ありがとうございます。
ACCESSでそのような、「クエリそのものを入れ替える処理をする」のがおかしい。ですかね。そうしなければならない理由がちょっと思いつかないです
サブクエリなら問題ない速度ということであれば、操作できるコントロール（透明ボタンや非連結のテキストボックス）を用意して更新後処理などでデータの更新処理をする（更新クエリを実行する）という手もあります
hiroton様
お返事遅くなりすみません。
定義域集合関数を使ったら確かに更新できるようになりました。
が、帳票フォームなので動きがノッソリノッソリしてしまいます。
いろいろ考えましたが、集計した金額をテーブルのフィールドに格納することにしました。
ありがとうございました。
hatenaさん すみませんでした。結果的に日付・ｻﾌﾞID 並べ替え設定して 日付&ｻﾌﾞIDを結合したｶｽﾀﾑ順番（例：2023040100005）のﾌｨｰﾙﾄﾞを使い DSum("金額","ｸｴﾘ名","ｶｽﾀﾑ順番<=" & [ｶｽﾀﾑ順番]) にしたらﾚｺｰﾄﾞ毎にきちんと累計がだせました。
色々している内に混乱し、お手数かけてしまいました。
ここまでは出来ましたのでいずれﾃﾞｰﾀが大量になる事を想定して、自動入力する関数にトライしたいと思ってます。
ありがとうございました。
どのような累計が欲しいのでしょうか。
最初の質問では、日付とサブIDで並べ替える。
次に、その並び順で累計を出したい。
サブIDはオートナンバー型ということなので重複はないですよね。ならば、同じ値はでないと思いますが。
もう少し正確な情報を出してください。
実際のテーブル名とフィールド構成(フィールド名、データ型、主キー設定)
どのような累計が欲しいのか。
hatenaさん 何度もすみません、ふと思ったのですがDSum式にメインＩＤも記述必要でしょうか？ 日付等が格納されていますメインテーブルには同じ日付のものも多数あるので（メインID：１ 日付：4/1、メインＩＤ：2 日付：4/1の場合もあり）。だから今のDSum式では同じ値の累計がでてるのでしょうか？
hatenaさん すみません、取りあえず DSum("項目の金額等","クエリ名","日付 <= #" & [日付] & "# OR (日付=#" & [日付] & "# AND サブID<=" & [サブID] & ")")をクエリに記述して試したところエラーにはなりませんが累計の値がところどころ計算されずにすぐ上のレコードと同じ値がでます。並べ替えも設定し出来ていているのですが・・・何かが間違っているのでしょうか？
hatenaさん ありがとうございました。すごいアイデアです。因みにクエリ内での累計をせずにフォームやレポートにだけ累計の場合はどうなりますでしょうか？（日付、ｻﾌﾞIDの並べ替え設定したクエリを基に作成）
2つのテーブルを結合したクエリを元にクエリを作成し、下記のような感じの式になります。
２つのフィールドを＆で結合してそれを対象にするとインデックスが無効になるので重い処理になります。DSumはただでさえ重いので、演算フィールドを対象にするのはお勧めできません。
上記の式にようにすればインデックスが有効になるので、データ数が多くなっても重くなりづらいです。
ただ、それでも重い処理になりがちなので、なんとかしたいと思われたなら、下記のページを参考にしてください。
累計値をテーブルに自動入力する関数 - hatena chips
hatenaさん ありがとうございます。その場合、累計の式はどうすればいいでしょうか？
累計: DSum("項目の金額等","テーブル名","日付 <= #" & [日付] & "#")や日付のところをサブIDにしても、日付+IDの順番での累計がでないので、、、それで結合をしたのです。何で設定すれば分からなくて。