VBA Building Dynamic SQL
This video example is going to show you how to generate
a SQL string from a form in VBA.
[sourcecode language=”vb”]
‘========================================================
‘DATABASE DESIGNED & CODED BY LOEBLCOM SERVICES 2013
‘ERIK LOEBL(713)409-7041
‘EMAIL: erik@loeblcomservices.com
‘WEB: http://loeblcomservices.com
‘========================================================
Option Compare Database
================
form:
================
Private Sub btnFindStocks_Click()
Dim strWhere As String
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strQuery As String
Dim dblSP_min As Double
Dim dblSP_max As Double
Dim dblMV_min As Double
Dim dblMV_max As Double
Dim dblDivY_min As Double
Dim dblDivY_max As Double
On Error GoTo errHandler
strQuery = "qryStockListings"
strSQL = "SELECT * FROM tblStockListings WHERE "
‘=================================================================
‘MIN Revenue($) (revenue) PRICE…
If Len(Me.cboSharedPrice_min) Then
If Me.cboSharedPrice_min = "Any" Then ‘ALL SHARED PRICEs
strCriteria = Me.cboSharedPrice_min.Column(0) & " Min Share Price"
strCriteria = "All Minimum Share Prices, "
Else
Select Case Right(Me.cboSharedPrice_min, 3)
Case "mil"
dblSP_min = Val(Me.cboSharedPrice_min)
dblSP_min = dblSP_min * 1000000
Case "bil"
dblSP_min = Val(Me.cboSharedPrice_min)
dblSP_min = dblSP_min * 1000000000
End Select
strWhere = strWhere & " ([Revenue($)] >= " & dblSP_min & ") AND "
strCriteria = Me.cboSharedPrice_min.Column(0) & " Min Share Price"
End If
End If
‘=================================================================
‘MAX Revenue($) (revenue) PRICE…
If Len(Me.cboSharedPrice_max) Then
If Me.cboSharedPrice_max = "Any" Then ‘ALL SHARED PRICEs
strCriteria = Me.cboSharedPrice_max.Column(0) & " Max Share Price"
strCriteria = "All Maximum Share Prices, "
Else
Select Case Right(Me.cboSharedPrice_max, 3)
Case "mil"
dblSP_max = Val(Me.cboSharedPrice_max)
dblSP_max = dblSP_max * 1000000
Case "bil"
dblSP_max = Val(Me.cboSharedPrice_max)
dblSP_max = dblSP_max * 1000000000
End Select
strWhere = strWhere & " ([Revenue($)] <= " & dblSP_max & ") AND "
strCriteria = strCriteria & ", " & Me.cboSharedPrice_max.Column(0) & " Max Share Price"
End If
End If
‘=================================================================
‘MIN Market_Cap($) (market cap) PRICE…
If Len(Me.cboMarketCap_min) Then
If Me.cboMarketCap_min = "Any" Then ‘ALL MIN MARKET PRICE
strCriteria = Me.cboMarketCap_min.Column(0) & " MIN MARKET PRICE"
strCriteria = "ALL MIN MARKET PRICE, "
Else
Select Case Right(Me.cboMarketCap_min, 3)
Case "mil"
dblMV_min = Val(Me.cboMarketCap_min)
dblMV_min = dblMV_min * 1000000
Case "bil"
dblMV_min = Val(Me.cboMarketCap_min)
dblMV_min = dblMV_min * 1000000000
End Select
strWhere = strWhere & " ([Market_Cap($)] >= " & dblMV_min & ") AND "
strCriteria = strCriteria & ", " & Me.cboMarketCap_min.Column(0) & " MIN MARKET PRICE"
End If
End If
‘=================================================================
‘=================================================================
‘MAX Market_Cap($) (market cap) PRICE…
If Len(Me.cboMarketCap_max) Then
If Me.cboMarketCap_max = "Any" Then ‘ALL MIN MARKET PRICE
strCriteria = Me.cboMarketCap_max.Column(0) & " MAX MARKET PRICE"
strCriteria = "ALL MAX MARKET PRICE, "
Else
Select Case Right(Me.cboMarketCap_max, 3)
Case "mil"
dblMV_max = Val(Me.cboMarketCap_max)
dblMV_max = dblMV_max * 1000000
Case "bil"
dblMV_max = Val(Me.cboMarketCap_max)
dblMV_max = dblMV_max * 1000000000
End Select
strWhere = strWhere & " ([Market_Cap($)] <= " & dblMV_max & ") AND "
strCriteria = strCriteria & ", " & Me.cboMarketCap_max.Column(0) & " MAX MARKET PRICE"
End If
End If
‘=================================================================
‘=================================================================
‘MIN Margin_Rate(%) (profit margin) YIELD PRICE…
If Len(Me.cboDividendYield_min) Then
If Me.cboDividendYield_min = "Any" Then ‘ALL MIN DIVIDEND YIELD PRICE
strCriteria = Me.cboDividendYield_min.Column(0) & " MIN DIVIDEND YIELD PRICE"
strCriteria = "ALL MIN DIVIDEND YIELD PRICE, "
Else
dblDivY_min = Val(Me.cboDividendYield_min)
dblDivY_min = dblDivY_min / 100
strWhere = strWhere & " ([Margin_Rate(%)] >= " & dblDivY_min & ") AND "
strCriteria = strCriteria & ", " & Me.cboDividendYield_min.Column(0) & " MIN DIVIDEND YIELD PRICE"
End If
End If
‘=================================================================
‘=================================================================
‘MAX Margin_Rate(%) (profit margin) PRICE…
If Len(Me.cboDividendYield_max) Then
If Me.cboDividendYield_max = "Any" Then ‘ALL MAX DIVIDEND YIELD PRICE
strCriteria = Me.cboDividendYield_max.Column(0) & " MAX DIVIDEND YIELD PRICE"
strCriteria = "ALL MAX DIVIDEND YIELD PRICE, "
Else
dblDivY_max = Val(Me.cboDividendYield_max)
dblDivY_max = dblDivY_max / 100
strWhere = strWhere & " ([Margin_Rate(%)] <= " & dblDivY_max & ") AND "
strCriteria = strCriteria & ", " & Me.cboDividendYield_max.Column(0) & " MAX DIVIDEND YIELD PRICE"
End If
End If
‘=================================================================
If Right(strWhere, 4) = "AND " Then
strWhere = Mid(strWhere, 1, Len(strWhere) – 4)
End If
strSQL = strSQL & strWhere
If Len(strWhere) = 0 Then
MsgBox "You didn’t enter any criteria for this report, now exitting", vbExclamation
Exit Sub
Else
Set db = CurrentDb
Set qdf = db.QueryDefs(strQuery)
qdf.SQL = strSQL
End If
DoCmd.OpenReport "rptStockListings", acViewPreview
Debug.Print strSQL
Exit Sub
errHandler:
Select Case Err.Number
Case 2501
‘no data
Resume Next
Case Else
MsgBox Err.Number & " – " & Err.Description
End Select
End Sub
Private Sub btnReset_Click()
With Me
.cboSharedPrice_min = "Any"
.cboSharedPrice_max = "Any"
.cboMarketCap_min = "Any"
.cboMarketCap_max = "Any"
.cboDividendYield_min = "Any"
.cboDividendYield_max = "Any"
End With
End Sub
Private Sub Form_Activate()
DoCmd.Restore
End Sub
Private Sub Form_Load()
End Sub
================
report:
================
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No Data"
Cancel = True
End Sub
Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
End Sub
[/sourcecode]
Click here for the database and the code:
Building-Dynamic-SQL.mdb