Running Query in VBA

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


I am building a custom query form allowing the user to choose the fields and
criteria for each field in the query. This has required me to build the
query via VBA. I almost have it, but am struggling with one line of code:

If Me.Active = True Then
SQL = SQL & " where ((([Active?]) =" & True & ")" And (([DEALER]) = ""
& Me.DEALER & """)")
End If

This line basically allows the user to select criteria for the field
“active” (which is a check box yes/no field) and also allows the user to
choose a specific type of auto dealer from the dealer field. I’m getting the
error “Data type mismatch” for some reason.

Any thoughts would be greatly appreciated!


The entire code is below:

Private Sub cmdRunQuery_Click()
If Me.lstFieldList.ItemsSelected.Count = 0 Then
MsgBox "Select some field names first."
Exit Sub
End If
If Me.Active = False And Me.Inactive = False And Me.All = False Then
MsgBox "Select Active, Inactive, or All Dealers"
Exit Sub
End If

Dim qDef As Object
Dim SQL As String
Dim vItem As Variant

' loop through selected field names
For Each vItem In Me.lstFieldList.ItemsSelected
SQL = SQL & ",[" & Me.lstFieldList.ItemData(vItem) & "]"
Next vItem

' build new SQL statement
SQL = "Select " & Mid(SQL, 2) & " from [IndirectTableQuery]"

' add criteria for Component if a component has been selected

If Me.Active = True Then
SQL = SQL & " where ((([Active?]) =" & True & ")" And (([DEALER]) = ""
& Me.DEALER & """)")
End If

' save query with new SQL statement
Set qDef = CurrentDb.QueryDefs("CustomReportQuery")
qDef.SQL = SQL

Set qDef = Nothing

' run query
DoCmd.OpenQuery "CustomReportQuery"

End Sub

.

Relevant Pages

  • Re: expiration
    ... First what type of field are you applying the criteria against? ... You can try the following in a query. ... If you are trying to get just records with the License has expired within ... it is the actual SQL statement that is executed. ...
    (microsoft.public.access.queries)
  • Re: need help creating criteria for time
    ... You are correct in your assumption; they are date/time fields: ... If you switch your query to SQL View, ... post the whole SQL statement. ... You can now put your criteria in the Criteria row under this field. ...
    (microsoft.public.access.queries)
  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... YES for all of the combo boxes.) ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)
  • Re: Building Query Criteria
    ... You cannot change just the criteria. ... Create the entire SQL statement as a string, ... Declare parameters in the query, and assign just the parameters at ... Tips for Access users - http://allenbrowne.com/tips.html ...
    (microsoft.public.access.queries)
  • Re: Building Query Criteria
    ... > You cannot change just the criteria. ... Create the entire SQL statement as a string, ... Declare parameters in the query, and assign just the parameters at ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.queries)