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

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!