我有這個基本的 VBA SQL 陳述句。我搜索外部資料庫并回傳外部資料庫中欄位[LabNumberPrimary]= 的所有記錄[labnummer]。
我的 VBA 代碼通過一些小的調整重復了自己。如何組合這 2 條陳述句，使我的 VBA 代碼變得更小、更用戶友好？
第一條宣告：
cmdString = "SELECT DISTINCT t.rapportnaam, t.norm, t.analysenaam FROM taken t, monsters m, opdrachten o WHERE o.labnummer = " & [LabNumberPrimary] & " AND m.opdrachtteller = o.opdrachtteller AND t.monsterteller = m.monsterteller"
宣告二：
cmdString = "SELECT DISTINCT t.rapportnaam, t.norm, t.analysenaam FROM taken t, monsters m, opdrachten o WHERE o.labnummer = " & [LabNumber_4_CT] & " AND m.opdrachtteller = o.opdrachtteller AND t.monsterteller = m.monsterteller"
我如何結合這兩個陳述？我試過：
cmdString = "SELECT DISTINCT t.rapportnaam, t.norm, t.analysenaam FROM taken t, monsters m, opdrachten o WHERE o.labnummer = " & [LabNumberPrimary] & " AND o.labnummer = " & [LabNumber_4_CT] & " AND m.opdrachtteller = o.opdrachtteller AND t.monsterteller = m.monsterteller"
但似乎不起作用。
LabNumberPrimary而LabNumber_4_CT在我的查詢列名qry_Administration
編輯：經過一些研究，我想我知道為什么沒有記錄回傳。
這段代碼：
AND m.opdrachtteller = o.opdrachtteller AND t.monsterteller = m.monsterteller"
兩個數字的“出納員”不同。所以我認為解決方案應該是這樣的：我想你們知道我要去哪里：
cmdString = "SELECT DISTINCT t.rapportnaam, t.norm, t.analysenaam" & _
" FROM taken t, monsters m, opdrachten o" & _
" WHERE (o.labnummer = " & [LabNumberPrimary] & _
" AND m.opdrachtteller = o.opdrachtteller" & _
" AND t.monsterteller = m.monsterteller")
" AND WHERE (o.labnummer = " & [LabNumber_4_CT] & _
" AND m.opdrachtteller = o.opdrachtteller" & _
" AND t.monsterteller = m.monsterteller")
甚至有可能結合這樣的陳述嗎？
uj5u.com熱心網友回復：
試試這個：
cmdString = "SELECT DISTINCT t.rapportnaam, t.norm, t.analysenaam" & _
" FROM taken t, monsters m, opdrachten o" & _
" WHERE o.labnummer = " & [LabNumberPrimary] & _
" OR o.labnummer = " & [LabNumber_4_CT] & _
" AND m.opdrachtteller = o.opdrachtteller" & _
" AND t.monsterteller = m.monsterteller"
uj5u.com熱心網友回復：
解決方案比預期的要困難得多。但我設法通過逆向工程讓它作業。我在查詢編輯器中復制了 sql 陳述句，添加了我的 3 個欄位，然后添加為數字。然后我回到 SQL 視圖并將該代碼復制到我的 VBA 代碼中。我用欄位名稱替換了數字LabNumberPrimary LabNumber_4_CT。我還必須為空值添加 Nz( 函式
LabNumberPrimary = Nz([LabNumberPrimary])
LabNumber_3_ASB = Nz([LabNumber_3_ASB])
LabNumber_4_CT = Nz([LabNumber_4_CT])
cmdString = "SELECT DISTINCT t.rapportnaam, t.norm, t.analysenaam FROM taken AS t, monsters AS m, opdrachten AS o WHERE (((o.labnummer)='" & LabNumberPrimary & "' Or (o.labnummer)='" & LabNumber_3_ASB & "' Or (o.labnummer)='" & LabNumber_4_CT & "') AND ((m.opdrachtteller)=[o].[opdrachtteller]) AND ((t.monsterteller)=[m].[monsterteller]))"
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/382294.html