How to Use SQL Select Statement




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

load with blank

 

#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

show all record

#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

search sql

 

#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.

code for list

list format width
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.

list view



#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;

Query View:

select top abc

#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;

Query View:

select top 5 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.

recordset with select st