Running Query in VBA

[bjschon]

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

 

[vanderghast]

Problem of delimiter and of ).




SQL = SQL & " where [Active?] =" & True & " And [DEALER] = """
& Me.DEALER & """"



If your statement is to be used by DoCmd, or as record source/ row source,
you could have use:

SQL = SQL & " WHERE [Active?] = FORMS!formNameHere!ActiveControlNameHere
AND [dealer] = FORMS!formNameHere!DealerControlNameHere "

without problem of delimiter (and parentheses).




Vanderghast, Access MVP

 

[JimBurke via AccessMonster.com]

Change it to:

If Me.Active Then
SQL = SQL & " where [Active] And [DEALER] = '" & Me.DEALER & "'"
End If

I removed all the parentheses - they're not needed here. When dealing with
boolean fields you don't need to say = TRUE or = FALSE - you can just put the
fieldName for when you want it = TRUE and you can use NOT fieldName when you
want it = FALSE. If you did want to specify it the way you were trying to, it
would be

" where [Active] = True And [DEALER] = '"...

This code is set up as if Dealer is a text field, with the surrounding quotes.
Make sure that is the case. Not sure why you had 'Active?' there - I hope the
? isn't part of a field name? If it is , then I guess you'll need it, though
offhand I don't think you can use those in field names.

Aslo, the way you have it set up,if Active is false it will select every
record in the table - you'll have no where clause at all. Is that what you
really want? Or do you need it to select by dealer in either case?

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

 

[bjschon]

Thank you so much. Still learning VBA, and sometime I get too complicated
when the solution is relatively simple. Thanks again...

Change it to:

If Me.Active Then
SQL = SQL & " where [Active] And [DEALER] = '" & Me.DEALER & "'"
End If

I removed all the parentheses - they're not needed here. When dealing with
boolean fields you don't need to say = TRUE or = FALSE - you can just put the
fieldName for when you want it = TRUE and you can use NOT fieldName when you
want it = FALSE. If you did want to specify it the way you were trying to, it
would be

" where [Active] = True And [DEALER] = '"...

This code is set up as if Dealer is a text field, with the surrounding quotes.
Make sure that is the case. Not sure why you had 'Active?' there - I hope the
? isn't part of a field name? If it is , then I guess you'll need it, though
offhand I don't think you can use those in field names.

Aslo, the way you have it set up,if Active is false it will select every
record in the table - you'll have no where clause at all. Is that what you
really want? Or do you need it to select by dealer in either case?

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