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
How To Escape Apostrophe In SQL Update Query
If you are looping a table with thousands of records, you’ll probably run into at least one that has an apostrophe in the field name. Like “Mike’s” or “M’cormick”, or something else. Anyway, here is one way to escape the string when you are doing your update query. Option Compare Database Sub YDriveLoop() ‘4/23/24 erik@loeblcomservices.com […]
How To Parse A Flat File In Excel VBA
In another post I demonstrated how to access a file on your computer using the MS Office Library. Here it is if you don’t know what I’m talking about. In this post, I am going to show you how to access the file and load it into your spreadsheet. I will do the same thing […]
How can I interact with other Office applications (Excel) using VBA in Access?
Need to write your Access data or query to an Excel file? Here is the how to do it: Most people are familiar with Excel and know how to use it well (enough), and when you start talking about Access, they get scared off, and don’t know what to do anymore. Well, here you are […]
How To Create A Parameter Query In Access
A parameter query changes your ordinary static access query to be more dynamic and interactive. It will ask you a question about what you want to search for, allowing you to do a search query multiple times instead of just once. You can do your parameter query straight from the QBE (Query By Example) Editor, […]