SQL- Select Statement
The SQL Select Statement is the SQL code that used to select data from a database. It is the most SQL code that used in any language. It can be used in VBA MS Access as well as Macro. It can be used in VBA as a Data Source of a form or can be used in Recordset method. I will show the examples of using the SQL –Select Statement below.
SQL SELECT Syntax
Select a specific field name from a table:
SELECT field_name, field_name,… FROM table_name;
Select all fields from table:
Or SELECT * FROM table_name;
#1 Select Statement on Form Loaded
Load a Search Customer form with a blank form by using a criteria Customer_id = null on the Select Statement : “SELECT * FROM tbl_customer WHERE (customer_ID)is Null”
Private Sub Form_Load() Dim strTask As String strTask = "SELECT * FROM tbl_customer WHERE (customer_ID)is null" Me.RecordSource = strTask End Sub
#2 Select All Records
Under the on Click Event of button Show All, use “SELECT * FROM tbl_Customer” to show all records from tbl_Customer table on this search form.
Private Sub CmdShowAll_Click() Dim Task As String Task = "SELECT * FROM tbl_Customer" Me.RecordSource = Task End Sub
#3 Select Statement with Criteria
Filter a customer list on a subform after selecting a Customer Type from the drop-down box. Select customer Type “Business” then the form will show only the customers that are business type.
Private Sub cboCustomerType_AfterUpdate() Dim myCustomer As String myCustomer = "Select * from tbl_customer where ([customer_type_id] = " & Me.cboCustomerType & ")" Me.tbl_Customer_subform1.Form.RecordSource = myCustomer Me.tbl_Customer_subform1.Form.Requery End Sub
#4 Select Specific Fields
The Select Statement method can be used to add items or records into a list box and display only certain fields on the list. For example below, I want to load a list box with all customers from Customer table, but display only the Customer_ID and Customer Name fields.
Per the property of list box above, it is set to four columns with the Column Widths of 1,1.5,1, 1 inch. However, the list box below is displaying only two columns; the Customer_ID and CustomerName with two blank columns. This is an example of Select Statement for Selecting only two fields; Customer_ID and CustomerName from the above code.
#5 Select Top Record
Display the top 5 records from Customer table order by Customer_ID. Use Select Statement in Query with SQL view with VB code below.
SELECT TOP 5 tbl_Customer.* FROM tbl_Customer Order by Customer_ID;
#6 Select Last record
There are 16 records from Customer table as shown below. It displays the last 5 records from Customer table by using SELECT TOP 5 and Order by Customer_ID DESC. Use Select Statement in Query with SQL view with VB code below.
SELECT TOP 5 tbl_Customer.* FROM tbl_Customer Order by Customer_ID DESC;
#7 Select Statement with Recordset
Use the Select Statement as a data source of RecordSet. Function below will count a number of customer from a Customer table.