Databases Reference
In-Depth Information
FIGURE 7.17
A Compound Query built using the Database Query Tool.
The concept of the compound query is demonstrated in Figure 7.17. Here the following query
is built:
Select * From Suppliers Where STATE = 'MA' Or STATE = 'NY' which will return all suppliers
whose addresses are listed in New York or Massachusetts.
Figure 7.18 shows the results for the query on the included sample database.
The actual query that is sent to Access is built from the GUI by means of the BuildSQL function.
Keep in mind that, because the Excel 2000 implementation of OLE DB does not support background
refresh, the form must be closed prior to the execution of the query, and therefore the SQL string
must be built and stored in a string variable prior to closing the form.
Function BuildSQL() As String
'Build a SQL Query from Form Object Selections
BuildSQL = "Select " & frm7Access.ComboBox_Fields & " From "
& frm7Access.ComboBox_Tables & " "
If frm7Access.Check_Cond1.value = False Then Exit Function
BuildSQL = BuildSQL & "Where " &
frm7Access.ComboBox_CFields1e.Text & " " & _
frm7Access.ComboBox_CondEq1e.Text & " " &
frm7Access.TextBox_Cond1e.Text & " "
If frm7Access.Check_Cond2.value = False Then Exit Function
BuildSQL = BuildSQL & frm7Access.ComboBox_LogOps2e.Text & " "
& frm7Access.ComboBox_CFields2e.Text & _
FIGURE 7.18
Complex Query Results on Sample MCS.mdb Database.
