VBA script – Dynamic WHERE clause with CSV Export

Visual_Basic_for_Applications_logo_and_wordmark.svgThe script below dynamically creates SQL statements from MS-Access form fields and their values (if any). As an extra bonus, this will also generate a .csv file that contains query results if needed; otherwise you can leave that part out.

*Disclaimer: This contains scripts I created as well as ones created by others that I found in various forums. They were then mixed together, put into a skillet and simmered until workable for my specific needs.

An over-simplification of how this will work is:

  • A user will enter values into an MS-Access form and click a ‘search’ button run the query/generate a .csv file
  • The script creates a RecordSource which gets assigned to a subform which will display the results
  • That same RecordSource will be used to generate the .csv file

Ok, lets get to it…

The VBA script below places the control values into a LIKE clause that searches a specified table/query column. The AddToWhere function below will just sit on top of your form’s VBA file. Copy it “as is”.
'Pass Fieldname and Fieldvalues from the form into subroutine
Private Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

If FieldValue <> "" Then    'Check each field to see if there is a value entered
  If ArgCount > 0 Then    'Checks to see if more than one field has a value
    MyCriteria = MyCriteria & " and "  'If more than one field has value add an 'AND' clause
  End If

'Creates a 'LIKE' condition WHERE FieldName is LIKE FieldValue 
'(Chr(42) = asterisk(wildcard), this works in VBA but you could also use '%' as Chr(37)
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & Chr(42) & FieldValue & Chr(42) & Chr(39))

ArgCount = ArgCount + 1   ' Argument count increases with each populated control.

End If

End Sub
This subroutine accepts 4 parameters from the ‘search’ button’s click event (FieldValue, FieldName, MyCriteria, ArgCount)
Private Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)
FieldValue is the value of the form’s controls that was set by the user. It could be text from a textbox or memo field, a selected value from a combo or list box, or a Boolean value from a radio button or check box.
If one or more controls have a value then the FieldName and value gets added to the SQL statement within a “LIKE” clause. If more than one control has a value then the “AND” operator gets added to the query before the next “LIKE” clause gets added.
If FieldValue <> "" Then    'Check each field to see if there is a value entered
   If ArgCount > 0 Then    'Checks to see if more than one field has a value
     MyCriteria = MyCriteria & " and "  'If more than one field has value add an 'AND' clause
End If

'Creates a 'LIKE' condition WHERE FieldName is LIKE FieldValue 
'(Chr(42) = asterisk(wildcard), this works in VBA but you could also use '%' as Chr(37)
 MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & Chr(42) & FieldValue & Chr(42) & Chr(39))
The ArgCount variable increments after each cycle
ArgCount = ArgCount + 1   ' Argument count increases with each populated control

End If
Now let’s take a look at the “search” button’s click event. In this case we are naming the button
“btnSeach”. The function below sends the form’s control values to the AddToWhere function
 '**The search criteria will be entered on a form while the results will be displayed witthin a subform    
Private Sub btnSearch_Click()

    Dim mySQL As String
    Dim SQL As String
    Dim SQL2 As String
    Dim MyCriteria As String
    Dim MyRecordSource As String
    Dim ArgCount As Integer

    'Clear the subform's RecordSource. Replace 'YourSubFormName' with your subform's actual name
    Me![YourSubFormName].Form.RecordSource = ""

    ArgCount = 0    'Initialize argument count

    mySQL = "SELECT * " 'Declare fields
    SQL = "FROM YourTableOrQueryName WHERE " 'Declare query source replace 'YourTableOrQueryName' with actual name
    MyCriteria = "" 'Clear critteria values

    'Use values entered in form's controls to create criteria for WHERE clause.
    '[FieldName#] refers to the control's name on main form, [ColumnName#] refers to the column name in database
    AddToWhere [FieldName1], "[ColumnName1]", MyCriteria, ArgCount 
    AddToWhere [FieldName2], "[ColumnName2]", MyCriteria, ArgCount

    'If no criterion specifed, return all records.
    If MyCriteria = "" Then    
        MyCriteria = "True"
    End If

    'Concatenates the query variables to create SQL statement
    MyRecordSource = mySQL & SQL & MyCriteria   

    'Set the full query string to be YourSubFormName's RecordSource 
    Me![YourSubFormName].Form.RecordSource = MyRecordSource    

    'Put query results into a table to be used as a report's source
    SQL2 = mySQL & "into tblRecord " & SQL & MyCriteria

    'stop warnings
    DoCmd.SetWarnings False 

    'excecute the updating tblRecord by SQL statement    
    DoCmd.RunSQL SQL2           

    'if no selections exist display a message box
    If Me![YourSubFormName].Form.RecordsetClone.RecordCount = 0 Then   
        MsgBox "No records match the criteria you entered.", 48, "No Records Found"
    Else
        Me![YourFormName].SetFocus  'Move insertion point to frmReportSub.
    End If

End Sub
Here’s our variable declarations
Private Sub btnSearch_Click()

    Dim mySQL As String
    Dim SQL As String
    Dim SQL2 As String
    Dim MyCriteria As String
    Dim MyRecordSource As String
    Dim ArgCount As Integer
The first thing that happens is we clear the subform’s record source so no data from previous searches get carried over and the ArgCount variable gets reset to 0.
'Clear the subform's RecordSource. Replace 'YourSubFormName' with your subform's actual name
    Me![YourSubFormName].Form.RecordSource = ""

    ArgCount = 0    'Initialize argument count
Now we start to create the SQL statement in chunks. The “mySQL” variable will contain the start of the query and declare which fields get searched (in this case, all of them)
mySQL = "SELECT * " 'Declare fields
The “SQL” variable will declare which tables or queries are searched from as well as adding the WHERE clause at the end.
SQL = "FROM YourTableOrQueryName WHERE " 'Declare query source. Replace 'YourTableOrQueryName' with actual name

MyCriteria = "" 'Clear criteria values
The “MyCriteria” variable gets cleared out but will eventually contain the criteria string generated by the “AddToWhere” subroutine

Now start adding the field values (if any) to the search criteria. The AddToWhere subroutine gets called and parameters get sent. In this case the [FieldName#] parameter represent the name of the form’s control that contains a value.

 'Use values entered in form's controls to create criteria for WHERE clause.
    '[FieldName#] refers to the control's name on main form, [ColumnName#] refers to the column name in database
    AddToWhere [FieldName1], "[ColumnName1]", MyCriteria, ArgCount 
    AddToWhere [FieldName2], "[ColumnName2]", MyCriteria, ArgCount
[ColumnName#] is the name of the column in the table (or query) where the data will be searched. The “MyCriteria” parameter will contain the search criteria (aka the “LIKE” clauses). The first time around “MyCriteria” will be empty but afterwards will contain the previously generated criteria. ArgCount will contain the number of controls that have value up to this point.
If the is no criteria then the search will return all values
'If no criterion specifed, return all records.
    If MyCriteria = "" Then    
        MyCriteria = "True"
    End If
MyRecordSource is a variable that will concatenate the ‘mySQL” and “SQL” variables as well as the continually updated “MyCriteria” variable. We then assign “MyRecordSource” as the recordsource of the subform that displays the results.
  'Concatenates the query variables to create SQL statement
    MyRecordSource = mySQL & SQL & MyCriteria   

    'Set the full query string to be YourSubFormName's RecordSource 
    Me![YourSubFormName].Form.RecordSource = MyRecordSource
There’s another variable called SQL2 that creates a query to insert the results into a “temp” table called “tblrecord”. The content of this table changes with each search. In this case it gets used as a report’s record source
 'Put query results into a table to be used as a report's source
    SQL2 = mySQL & "into tblRecord " & SQL & MyCriteria

    'stop warnings
    DoCmd.SetWarnings False 

    'excecute the updating tblRecord by SQL statement    
    DoCmd.RunSQL SQL2
A conditional checks the number of results and throws a message if nothing is returned.
 'if no selections exist display a message box
    If Me![YourSubFormName].Form.RecordsetClone.RecordCount = 0 Then   
        MsgBox "No records match the criteria you entered.", 48, "No Records Found"
    Else
        Me![YourFormName].SetFocus  'Move insertion point to frmReportSub.
    End If

End Sub

Export to CSV

'************************Exports to CSV********************
Sub ExportCSV_Click

'Runs when your export button is clicked
Dim strFilename As String
Dim expRecSource As DAO.Recordset

'Use 'MyRecordSource' variable for CSV export
Set expRecSource = CurrentDb.OpenRecordset(MyRecordSource)

'Create the CSV file
Set fso = CreateObject("Scripting.FileSystemObject")

'This creates the CSV file. Replace 'YourCSVFileName' with whatever you want the exported file to be named
Set a = fso.CreateTextFile("C:\Users\Public\Desktop\YourCSVFileName.csv") 

a.Close

'Make sure to start at the beginning of the RecordSource
If Not ((expRecSource.EOF) And (expRecSource.BOF)) Then
expRecSource.MoveFirst

'query_exp' is the name of an empty query in MS-Access that will use 'MyRecordSource' as its string
CurrentDb.QueryDefs("query_exp").SQL = MyRecordSource

'Place path to CSV file into a variable
strFilename = "C:\Users\Public\Desktop\YourCSVFileName.csv"

'Put query results into CSV file
DoCmd.TransferText acExportDelim, , "query_exp", strFilename, True

'Ding! Export is done...
MsgBox "Export Complete"

'Open the CSV file
Application.FollowHyperlink "C:\Users\Public\Desktop\YourCSVFileName.csv"

End If

End Sub
'********************End CSV Export************************

The final step is to set up the CSV export.

This requires two variables, one is set “As String” the other “As DAO.Recordset” (“strFilename” and “expRecSource” respectively)
Sub ExportCSV_Click

'Runs when your export button is clicked
Dim strFilename As String
Dim expRecSource As DAO.Recordset
Set “expRecSource” to “MyRecordSource”, create a File System Object and create the CSV file.
'Use 'MyRecordSource' variable for CSV export
Set expRecSource = CurrentDb.OpenRecordset(MyRecordSource)

'Create the CSV file
Set fso = CreateObject("Scripting.FileSystemObject")

'This creates the CSV file. Replace 'YourCSVFileName' with whatever you want the exported file to be named
Set a = fso.CreateTextFile("C:\Users\Public\Desktop\YourCSVFileName.csv") 

a.Close
Then start moving through the record source starting form the top.
'Make sure to start at the beginning of the RecordSource
If Not ((expRecSource.EOF) And (expRecSource.BOF)) Then
expRecSource.MoveFirst
There’s an empty query called “query_exp” which uses the “MyRecordSource” variable as its record source.
'query_exp' is the name of an empty query in MS-Access that will use 'MyRecordSource' as its string
CurrentDb.QueryDefs("query_exp").SQL = MyRecordSource
The path to the CSV file gets assigned to a variable
'Place path to CSV file into a variable
strFilename = "C:\Users\Public\Desktop\YourCSVFileName.csv"
The results of the query get transferred to the file using DoCmd.TransferText. After which a message box note the export is complete.
'Put query results into CSV file
DoCmd.TransferText acExportDelim, , "query_exp", strFilename, True

'Ding! Export is done...
MsgBox "Export Complete"
And then open the file.
'Open the CSV file
Application.FollowHyperlink "C:\Users\Public\Desktop\YourCSVFileName.csv"

End If

End Sub
'********************End CSV Export************************

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>