MS Download Create QBF-how to use listbox

[jeninOk]

I have created a form to filter records using the download at MS, QBF.exe.
The code for the generated form is below and it works great until I add list
boxes. It's the only example I've found where I can type "between 100 and
200" in a text control and/or use wildcards (see IsOperator Function).

However I need to add 3 multiselect listboxes to the form. How do I change
the code below to allow for the results of selections of the three
multiselect list boxes?

1. Function: BuildSQLString: How to add list box selections to the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to the
Where clause of the where clause?
Or, does the code for the list boxes need to be in Function QBFDoHide?

***I am not asking for advice on changing the code behind the form that
creates the QBF, just in the resulting form.

Thanks so much in advance,
Jenny

*********CODE FOLLOWS*********
Option Compare Database 'Use database order for string comparisons
Option Explicit

' REQUIRES A REFERENCE TO Microsoft DAO 3.6.

Const QUOTE = """"

' This string is the text that gets appended
' to the chosen form name, once it's become a
' QBF form. It's completely arbitrary, and can be
' anything you like.
Public Const conQBFSuffix = "_QBF"

Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)

' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.

Dim strTemp As String

On Error GoTo HandleErrors

If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If

' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS with the
text we got.
' This is probably a LOT slower. If you want direct matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE & varFieldValue & QUOTE
strTemp = strTemp & " LIKE " & QUOTE & varFieldValue & "*" &
QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue & "#"
Case Else
' This function really can't handle any of the other data
types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If

BuildSQLString = strTemp

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")",
vbExclamation, "BuildSQLString"
strTemp = vbNullString
Resume ExitHere
End Function

Private Function BuildWHEREClause(frm As Form) As String

' Build the full WHERE clause based on fields
' on the passed-in form. This function attempts
' to look at all controls that have the correct
' settings in the Tag properties.

Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer
Dim varControlSource As Variant
Dim ctl As Control
'var for list control reference


Const conAND As String = " AND "



For Each ctl In frm.Controls
' Get the original control source.
varControlSource = adhCtlTagGetItem(ctl, "qbfField")
If Not IsNull(varControlSource) Then
' If the value of the control isn't null...
If Not IsNull(ctl) Then
' then get the value.
varDataType = adhCtlTagGetItem(ctl, "qbfType")
If Not IsNull(varDataType) Then
strTemp = "(" & BuildSQLString(varControlSource, ctl,
varDataType) & ")"
strLocalSQL = strLocalSQL & conAND & strTemp
End If
End If
End If
Next ctl


' Trim off the leading " AND "
If Len(strLocalSQL) > 0 Then
BuildWHEREClause = "(" & Mid$(strLocalSQL, Len(conAND) + 1) & ")"
End If
End Function

Public Function DoQBF(ByVal strFormName As String, _
Optional blnCloseIt As Boolean = True) As String

' Load the specified form as a QBF form. If
' the form is still loaded when control returns
' to this function, then it will attempt to
' build an SQL WHERE clause describing the
' values in the fields. DoQBF() will return
' either that SQL string or an empty string,
' depending on what the user chose to do and
' whether or not any fields were filled in.

' In:
' strFormName: Name of the form to load
' blnCloseIt: Close the form, if the user didn't?
' Out:
' Return Value: The calculated SQL string.

Dim strSQL As String

DoCmd.OpenForm strFormName, WindowMode:=acDialog

' You won't get here until user hides or closes the form.
' If the user closed the form, there's nothing
' to be done. Otherwise, build up the SQL WHERE
' clause. Once you're done, if the caller requested
' the QBF form to be closed, close it now.
If IsFormLoaded(strFormName) Then
strSQL = BuildWHEREClause(Forms(strFormName))
If blnCloseIt Then
DoCmd.Close acForm, strFormName
End If
End If
DoQBF = strSQL
End Function

Public Function QBFDoClose()
' This is a function so it can be called easily
' from the Properties window directly.

' Close the current form.
On Error Resume Next
DoCmd.Close
End Function

Public Function QBFDoHide(frm As Form)
' This is a function so it can be called easily
' from the Properties window directly.

Dim strSQL As String
Dim strParent As String

'Get the name of the Parent form
strParent = adhGetItem(frm.Tag, "Parent") & vbNullString

'Create the appropriate WHERE clause based on the fields with data in
them.
strSQL = DoQBF(frm.Name, False)

If Len(strParent) > 0 Then
'Open the Parent form filtered with the Where clause generated above
DoCmd.OpenForm FormName:=strParent, View:=acNormal,
WhereCondition:=strSQL
End If

'Make this QBF form invisible.
frm.Visible = False
End Function

Private Function IsFormLoaded(strName As String) As Boolean

' Return a logical value indicating whether a
' given formname is loaded or not.
' You could use the IsLoaded property of a member
' of the AllForms collection to get this information, but
' that method raises an error if you ask about a
' for that doesn't exist. The obscure SysCmd function
' does not.
On Error Resume Next
IsFormLoaded = (SysCmd(acSysCmdGetObjectState, acForm, strName) <> 0)
End Function

Private Function IsOperator(varValue As Variant) As Boolean

' Return a logical value indicating whether a
' value passed in is an operator or not.
' This is NOT infallible, and may need correcting.

Dim strTemp As String

strTemp = Trim$(UCase(varValue))
IsOperator = False

' Check first character for <,>, or =
If InStr(1, "<>=", Left$(strTemp, 1)) > 0 Then
IsOperator = True
' Check for IN (x,y,z)
ElseIf ((Left$(strTemp, 4) = "IN (") And (Right$(strTemp, 1) = ")")) Then
IsOperator = True
' Check for BETWEEN ... AND ...
ElseIf ((Left$(strTemp, 8) = "BETWEEN ") And (InStr(1, strTemp, " AND ")

0)) Then

IsOperator = True
' Check for NOT xxx
ElseIf (Left$(strTemp, 4) = "NOT ") Then
IsOperator = True
' Check for LIKE xxx
ElseIf (Left$(strTemp, 5) = "LIKE ") Then
IsOperator = True
End If
End Function

 

[Douglas J. Steele]

http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" is an
example of how to use a multiselect list box, although I have to admit I
prefer using the IN operator to repeating the field name multiple times:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & ", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


If EmpID were text, you'd need

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & Chr$(34) & ctl.ItemData(varItem) & Chr$(34) & ", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have created a form to filter records using the download at MS, QBF.exe.
The code for the generated form is below and it works great until I add
list
boxes. It's the only example I've found where I can type "between 100 and
200" in a text control and/or use wildcards (see IsOperator Function).

However I need to add 3 multiselect listboxes to the form. How do I
change
the code below to allow for the results of selections of the three
multiselect list boxes?

1. Function: BuildSQLString: How to add list box selections to the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to the
Where clause of the where clause?
Or, does the code for the list boxes need to be in Function QBFDoHide?

***I am not asking for advice on changing the code behind the form that
creates the QBF, just in the resulting form.

Thanks so much in advance,
Jenny

*********CODE FOLLOWS*********
Option Compare Database 'Use database order for string comparisons
Option Explicit

' REQUIRES A REFERENCE TO Microsoft DAO 3.6.

Const QUOTE = """"

' This string is the text that gets appended
' to the chosen form name, once it's become a
' QBF form. It's completely arbitrary, and can be
' anything you like.
Public Const conQBFSuffix = "_QBF"

Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)

' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.

Dim strTemp As String

On Error GoTo HandleErrors

If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If

' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS with the
text we got.
' This is probably a LOT slower. If you want direct
matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE & varFieldValue & QUOTE
strTemp = strTemp & " LIKE " & QUOTE & varFieldValue & "*"
&
QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue & "#"
Case Else
' This function really can't handle any of the other data
types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If

BuildSQLString = strTemp

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")",
vbExclamation, "BuildSQLString"
strTemp = vbNullString
Resume ExitHere
End Function

Private Function BuildWHEREClause(frm As Form) As String

' Build the full WHERE clause based on fields
' on the passed-in form. This function attempts
' to look at all controls that have the correct
' settings in the Tag properties.

Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer
Dim varControlSource As Variant
Dim ctl As Control
'var for list control reference


Const conAND As String = " AND "



For Each ctl In frm.Controls
' Get the original control source.
varControlSource = adhCtlTagGetItem(ctl, "qbfField")
If Not IsNull(varControlSource) Then
' If the value of the control isn't null...
If Not IsNull(ctl) Then
' then get the value.
varDataType = adhCtlTagGetItem(ctl, "qbfType")
If Not IsNull(varDataType) Then
strTemp = "(" & BuildSQLString(varControlSource, ctl,
varDataType) & ")"
strLocalSQL = strLocalSQL & conAND & strTemp
End If
End If
End If
Next ctl


' Trim off the leading " AND "
If Len(strLocalSQL) > 0 Then
BuildWHEREClause = "(" & Mid$(strLocalSQL, Len(conAND) + 1) & ")"
End If
End Function

Public Function DoQBF(ByVal strFormName As String, _
Optional blnCloseIt As Boolean = True) As String

' Load the specified form as a QBF form. If
' the form is still loaded when control returns
' to this function, then it will attempt to
' build an SQL WHERE clause describing the
' values in the fields. DoQBF() will return
' either that SQL string or an empty string,
' depending on what the user chose to do and
' whether or not any fields were filled in.

' In:
' strFormName: Name of the form to load
' blnCloseIt: Close the form, if the user didn't?
' Out:
' Return Value: The calculated SQL string.

Dim strSQL As String

DoCmd.OpenForm strFormName, WindowMode:=acDialog

' You won't get here until user hides or closes the form.
' If the user closed the form, there's nothing
' to be done. Otherwise, build up the SQL WHERE
' clause. Once you're done, if the caller requested
' the QBF form to be closed, close it now.
If IsFormLoaded(strFormName) Then
strSQL = BuildWHEREClause(Forms(strFormName))
If blnCloseIt Then
DoCmd.Close acForm, strFormName
End If
End If
DoQBF = strSQL
End Function

Public Function QBFDoClose()
' This is a function so it can be called easily
' from the Properties window directly.

' Close the current form.
On Error Resume Next
DoCmd.Close
End Function

Public Function QBFDoHide(frm As Form)
' This is a function so it can be called easily
' from the Properties window directly.

Dim strSQL As String
Dim strParent As String

'Get the name of the Parent form
strParent = adhGetItem(frm.Tag, "Parent") & vbNullString

'Create the appropriate WHERE clause based on the fields with data in
them.
strSQL = DoQBF(frm.Name, False)

If Len(strParent) > 0 Then
'Open the Parent form filtered with the Where clause generated
above
DoCmd.OpenForm FormName:=strParent, View:=acNormal,
WhereCondition:=strSQL
End If

'Make this QBF form invisible.
frm.Visible = False
End Function

Private Function IsFormLoaded(strName As String) As Boolean

' Return a logical value indicating whether a
' given formname is loaded or not.
' You could use the IsLoaded property of a member
' of the AllForms collection to get this information, but
' that method raises an error if you ask about a
' for that doesn't exist. The obscure SysCmd function
' does not.
On Error Resume Next
IsFormLoaded = (SysCmd(acSysCmdGetObjectState, acForm, strName) <> 0)
End Function

Private Function IsOperator(varValue As Variant) As Boolean

' Return a logical value indicating whether a
' value passed in is an operator or not.
' This is NOT infallible, and may need correcting.

Dim strTemp As String

strTemp = Trim$(UCase(varValue))
IsOperator = False

' Check first character for <,>, or =
If InStr(1, "<>=", Left$(strTemp, 1)) > 0 Then
IsOperator = True
' Check for IN (x,y,z)
ElseIf ((Left$(strTemp, 4) = "IN (") And (Right$(strTemp, 1) = ")"))
Then
IsOperator = True
' Check for BETWEEN ... AND ...
ElseIf ((Left$(strTemp, 8) = "BETWEEN ") And (InStr(1, strTemp, " AND
")

0)) Then

IsOperator = True
' Check for NOT xxx
ElseIf (Left$(strTemp, 4) = "NOT ") Then
IsOperator = True
' Check for LIKE xxx
ElseIf (Left$(strTemp, 5) = "LIKE ") Then
IsOperator = True
End If
End Function

 

[jeninOk]

Thanks, Doug--especially for such a fast response! One question: Where do I
put this code?

How do I alter these sections of the form to include this code -- sorry
pretty new to VB, but I've read enough that I do get your code.
1. Function: BuildSQLString: How to add list box selections to the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to the
Where clause of the where clause?

http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" is an
example of how to use a multiselect list box, although I have to admit I
prefer using the IN operator to repeating the field name multiple times:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & ", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


If EmpID were text, you'd need

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & Chr$(34) & ctl.ItemData(varItem) & Chr$(34) & ", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have created a form to filter records using the download at MS, QBF.exe.
The code for the generated form is below and it works great until I add
list
boxes. It's the only example I've found where I can type "between 100 and
200" in a text control and/or use wildcards (see IsOperator Function).

However I need to add 3 multiselect listboxes to the form. How do I
change
the code below to allow for the results of selections of the three
multiselect list boxes?

1. Function: BuildSQLString: How to add list box selections to the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to the
Where clause of the where clause?
Or, does the code for the list boxes need to be in Function QBFDoHide?

***I am not asking for advice on changing the code behind the form that
creates the QBF, just in the resulting form.

Thanks so much in advance,
Jenny

*********CODE FOLLOWS*********
Option Compare Database 'Use database order for string comparisons
Option Explicit

' REQUIRES A REFERENCE TO Microsoft DAO 3.6.

Const QUOTE = """"

' This string is the text that gets appended
' to the chosen form name, once it's become a
' QBF form. It's completely arbitrary, and can be
' anything you like.
Public Const conQBFSuffix = "_QBF"

Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)

' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.

Dim strTemp As String

On Error GoTo HandleErrors

If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If

' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS with the
text we got.
' This is probably a LOT slower. If you want direct
matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE & varFieldValue & QUOTE
strTemp = strTemp & " LIKE " & QUOTE & varFieldValue & "*"
&
QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue & "#"
Case Else
' This function really can't handle any of the other data
types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If

BuildSQLString = strTemp

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")",
vbExclamation, "BuildSQLString"
strTemp = vbNullString
Resume ExitHere
End Function

Private Function BuildWHEREClause(frm As Form) As String

' Build the full WHERE clause based on fields
' on the passed-in form. This function attempts
' to look at all controls that have the correct
' settings in the Tag properties.

Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer
Dim varControlSource As Variant
Dim ctl As Control
'var for list control reference


Const conAND As String = " AND "



For Each ctl In frm.Controls
' Get the original control source.
varControlSource = adhCtlTagGetItem(ctl, "qbfField")
If Not IsNull(varControlSource) Then
' If the value of the control isn't null...
If Not IsNull(ctl) Then
' then get the value.
varDataType = adhCtlTagGetItem(ctl, "qbfType")
If Not IsNull(varDataType) Then
strTemp = "(" & BuildSQLString(varControlSource, ctl,
varDataType) & ")"
strLocalSQL = strLocalSQL & conAND & strTemp
End If
End If
End If
Next ctl


' Trim off the leading " AND "
If Len(strLocalSQL) > 0 Then
BuildWHEREClause = "(" & Mid$(strLocalSQL, Len(conAND) + 1) & ")"
End If
End Function

Public Function DoQBF(ByVal strFormName As String, _
Optional blnCloseIt As Boolean = True) As String

' Load the specified form as a QBF form. If
' the form is still loaded when control returns
' to this function, then it will attempt to
' build an SQL WHERE clause describing the
' values in the fields. DoQBF() will return
' either that SQL string or an empty string,
' depending on what the user chose to do and
' whether or not any fields were filled in.

' In:
' strFormName: Name of the form to load
' blnCloseIt: Close the form, if the user didn't?
' Out:
' Return Value: The calculated SQL string.

Dim strSQL As String

DoCmd.OpenForm strFormName, WindowMode:=acDialog

' You won't get here until user hides or closes the form.
' If the user closed the form, there's nothing
' to be done. Otherwise, build up the SQL WHERE
' clause. Once you're done, if the caller requested
' the QBF form to be closed, close it now.
If IsFormLoaded(strFormName) Then
strSQL = BuildWHEREClause(Forms(strFormName))
If blnCloseIt Then
DoCmd.Close acForm, strFormName
End If
End If
DoQBF = strSQL
End Function

Public Function QBFDoClose()
' This is a function so it can be called easily
' from the Properties window directly.

' Close the current form.
On Error Resume Next
DoCmd.Close
End Function

Public Function QBFDoHide(frm As Form)
' This is a function so it can be called easily
' from the Properties window directly.

Dim strSQL As String
Dim strParent As String

'Get the name of the Parent form
strParent = adhGetItem(frm.Tag, "Parent") & vbNullString

'Create the appropriate WHERE clause based on the fields with data in
them.
strSQL = DoQBF(frm.Name, False)

If Len(strParent) > 0 Then
'Open the Parent form filtered with the Where clause generated
above
DoCmd.OpenForm FormName:=strParent, View:=acNormal,
WhereCondition:=strSQL
End If

'Make this QBF form invisible.
frm.Visible = False
End Function

Private Function IsFormLoaded(strName As String) As Boolean

' Return a logical value indicating whether a
' given formname is loaded or not.
' You could use the IsLoaded property of a member
' of the AllForms collection to get this information, but
' that method raises an error if you ask about a
' for that doesn't exist. The obscure SysCmd function
' does not.
On Error Resume Next
IsFormLoaded = (SysCmd(acSysCmdGetObjectState, acForm, strName) <> 0)
End Function

Private Function IsOperator(varValue As Variant) As Boolean

' Return a logical value indicating whether a
' value passed in is an operator or not.
' This is NOT infallible, and may need correcting.

Dim strTemp As String

strTemp = Trim$(UCase(varValue))
IsOperator = False

' Check first character for <,>, or =
If InStr(1, "<>=", Left$(strTemp, 1)) > 0 Then
IsOperator = True
' Check for IN (x,y,z)
ElseIf ((Left$(strTemp, 4) = "IN (") And (Right$(strTemp, 1) = ")"))
Then
IsOperator = True
' Check for BETWEEN ... AND ...
ElseIf ((Left$(strTemp, 8) = "BETWEEN ") And (InStr(1, strTemp, " AND
")

0)) Then

IsOperator = True
' Check for NOT xxx
ElseIf (Left$(strTemp, 4) = "NOT ") Then
IsOperator = True
' Check for LIKE xxx

 

[Douglas J. Steele]

You could probably mess with BuildWhereClause, but it will take a fair bit
of effort.

Where that routine calls BuildSQLString, you'd need to determine whether the
control is a multiselect listbox or not. If it isn't, you'd continue to call
BuildSQLString. If it is, you'd want to use the code I showed instead. And,
since I was lazy, the code I gave you isn't nearly as comprehensive as
BuildSQLString: you'll need to expand it to determine whether you need
quotes or not and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug--especially for such a fast response! One question: Where
do I
put this code?

How do I alter these sections of the form to include this code -- sorry
pretty new to VB, but I've read enough that I do get your code.
1. Function: BuildSQLString: How to add list box selections to the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to the
Where clause of the where clause?

http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" is an
example of how to use a multiselect list box, although I have to admit I
prefer using the IN operator to repeating the field name multiple times:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & ", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


If EmpID were text, you'd need

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & Chr$(34) & ctl.ItemData(varItem) & Chr$(34) &
", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have created a form to filter records using the download at MS,
QBF.exe.
The code for the generated form is below and it works great until I add
list
boxes. It's the only example I've found where I can type "between 100
and
200" in a text control and/or use wildcards (see IsOperator Function).

However I need to add 3 multiselect listboxes to the form. How do I
change
the code below to allow for the results of selections of the three
multiselect list boxes?

1. Function: BuildSQLString: How to add list box selections to the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to the
Where clause of the where clause?
Or, does the code for the list boxes need to be in Function QBFDoHide?

***I am not asking for advice on changing the code behind the form that
creates the QBF, just in the resulting form.

Thanks so much in advance,
Jenny

*********CODE FOLLOWS*********
Option Compare Database 'Use database order for string comparisons
Option Explicit

' REQUIRES A REFERENCE TO Microsoft DAO 3.6.

Const QUOTE = """"

' This string is the text that gets appended
' to the chosen form name, once it's become a
' QBF form. It's completely arbitrary, and can be
' anything you like.
Public Const conQBFSuffix = "_QBF"

Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)

' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.

Dim strTemp As String

On Error GoTo HandleErrors

If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If

' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS with the
text we got.
' This is probably a LOT slower. If you want direct
matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE & varFieldValue &
QUOTE
strTemp = strTemp & " LIKE " & QUOTE & varFieldValue &
"*"
&
QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle,
dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue & "#"
Case Else
' This function really can't handle any of the other
data
types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If

BuildSQLString = strTemp

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")",
vbExclamation, "BuildSQLString"
strTemp = vbNullString
Resume ExitHere
End Function

Private Function BuildWHEREClause(frm As Form) As String

' Build the full WHERE clause based on fields
' on the passed-in form. This function attempts
' to look at all controls that have the correct
' settings in the Tag properties.

Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer
Dim varControlSource As Variant
Dim ctl As Control
'var for list control reference


Const conAND As String = " AND "



For Each ctl In frm.Controls
' Get the original control source.
varControlSource = adhCtlTagGetItem(ctl, "qbfField")
If Not IsNull(varControlSource) Then
' If the value of the control isn't null...
If Not IsNull(ctl) Then
' then get the value.
varDataType = adhCtlTagGetItem(ctl, "qbfType")
If Not IsNull(varDataType) Then
strTemp = "(" & BuildSQLString(varControlSource,
ctl,
varDataType) & ")"
strLocalSQL = strLocalSQL & conAND & strTemp
End If
End If
End If
Next ctl


' Trim off the leading " AND "
If Len(strLocalSQL) > 0 Then
BuildWHEREClause = "(" & Mid$(strLocalSQL, Len(conAND) + 1) &
")"
End If
End Function

Public Function DoQBF(ByVal strFormName As String, _
Optional blnCloseIt As Boolean = True) As String

' Load the specified form as a QBF form. If
' the form is still loaded when control returns
' to this function, then it will attempt to
' build an SQL WHERE clause describing the
' values in the fields. DoQBF() will return
' either that SQL string or an empty string,
' depending on what the user chose to do and
' whether or not any fields were filled in.

' In:
' strFormName: Name of the form to load
' blnCloseIt: Close the form, if the user didn't?
' Out:
' Return Value: The calculated SQL string.

Dim strSQL As String

DoCmd.OpenForm strFormName, WindowMode:=acDialog

' You won't get here until user hides or closes the form.
' If the user closed the form, there's nothing
' to be done. Otherwise, build up the SQL WHERE
' clause. Once you're done, if the caller requested
' the QBF form to be closed, close it now.
If IsFormLoaded(strFormName) Then
strSQL = BuildWHEREClause(Forms(strFormName))
If blnCloseIt Then
DoCmd.Close acForm, strFormName
End If
End If
DoQBF = strSQL
End Function

Public Function QBFDoClose()
' This is a function so it can be called easily
' from the Properties window directly.

' Close the current form.
On Error Resume Next
DoCmd.Close
End Function

Public Function QBFDoHide(frm As Form)
' This is a function so it can be called easily
' from the Properties window directly.

Dim strSQL As String
Dim strParent As String

'Get the name of the Parent form
strParent = adhGetItem(frm.Tag, "Parent") & vbNullString

'Create the appropriate WHERE clause based on the fields with data
in
them.
strSQL = DoQBF(frm.Name, False)

If Len(strParent) > 0 Then
'Open the Parent form filtered with the Where clause generated
above
DoCmd.OpenForm FormName:=strParent, View:=acNormal,
WhereCondition:=strSQL
End If

'Make this QBF form invisible.
frm.Visible = False
End Function

Private Function IsFormLoaded(strName As String) As Boolean

' Return a logical value indicating whether a
' given formname is loaded or not.
' You could use the IsLoaded property of a member
' of the AllForms collection to get this information, but
' that method raises an error if you ask about a
' for that doesn't exist. The obscure SysCmd function
' does not.
On Error Resume Next
IsFormLoaded = (SysCmd(acSysCmdGetObjectState, acForm, strName) <>
0)
End Function

Private Function IsOperator(varValue As Variant) As Boolean

' Return a logical value indicating whether a
' value passed in is an operator or not.
' This is NOT infallible, and may need correcting.

Dim strTemp As String

strTemp = Trim$(UCase(varValue))
IsOperator = False

' Check first character for <,>, or =
If InStr(1, "<>=", Left$(strTemp, 1)) > 0 Then
IsOperator = True
' Check for IN (x,y,z)
ElseIf ((Left$(strTemp, 4) = "IN (") And (Right$(strTemp, 1) = ")"))
Then
IsOperator = True
' Check for BETWEEN ... AND ...
ElseIf ((Left$(strTemp, 8) = "BETWEEN ") And (InStr(1, strTemp, "
AND
")
0)) Then
IsOperator = True
' Check for NOT xxx
ElseIf (Left$(strTemp, 4) = "NOT ") Then
IsOperator = True
' Check for LIKE xxx

 

[jeninOk]

Hi Doug, No I don't want to dynamically determine if the control is a list
box, etc. I just want to query my list boxes on my form from within the code.

Please, where would you insert the code just as it is?

Thanks, Jenny

You could probably mess with BuildWhereClause, but it will take a fair bit
of effort.

Where that routine calls BuildSQLString, you'd need to determine whether the
control is a multiselect listbox or not. If it isn't, you'd continue to call
BuildSQLString. If it is, you'd want to use the code I showed instead. And,
since I was lazy, the code I gave you isn't nearly as comprehensive as
BuildSQLString: you'll need to expand it to determine whether you need
quotes or not and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug--especially for such a fast response! One question: Where
do I
put this code?

How do I alter these sections of the form to include this code -- sorry
pretty new to VB, but I've read enough that I do get your code.
1. Function: BuildSQLString: How to add list box selections to the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to the
Where clause of the where clause?

http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" is an
example of how to use a multiselect list box, although I have to admit I
prefer using the IN operator to repeating the field name multiple times:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & ", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


If EmpID were text, you'd need

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & Chr$(34) & ctl.ItemData(varItem) & Chr$(34) &
", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have created a form to filter records using the download at MS,
QBF.exe.
The code for the generated form is below and it works great until I add
list
boxes. It's the only example I've found where I can type "between 100
and
200" in a text control and/or use wildcards (see IsOperator Function).

However I need to add 3 multiselect listboxes to the form. How do I
change
the code below to allow for the results of selections of the three
multiselect list boxes?

1. Function: BuildSQLString: How to add list box selections to the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to the
Where clause of the where clause?
Or, does the code for the list boxes need to be in Function QBFDoHide?

***I am not asking for advice on changing the code behind the form that
creates the QBF, just in the resulting form.

Thanks so much in advance,
Jenny

*********CODE FOLLOWS*********
Option Compare Database 'Use database order for string comparisons
Option Explicit

' REQUIRES A REFERENCE TO Microsoft DAO 3.6.

Const QUOTE = """"

' This string is the text that gets appended
' to the chosen form name, once it's become a
' QBF form. It's completely arbitrary, and can be
' anything you like.
Public Const conQBFSuffix = "_QBF"

Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)

' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.

Dim strTemp As String

On Error GoTo HandleErrors

If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If

' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS with the
text we got.
' This is probably a LOT slower. If you want direct
matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE & varFieldValue &
QUOTE
strTemp = strTemp & " LIKE " & QUOTE & varFieldValue &
"*"
&
QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle,
dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue & "#"
Case Else
' This function really can't handle any of the other
data
types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If

BuildSQLString = strTemp

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")",
vbExclamation, "BuildSQLString"
strTemp = vbNullString
Resume ExitHere
End Function

Private Function BuildWHEREClause(frm As Form) As String

' Build the full WHERE clause based on fields
' on the passed-in form. This function attempts
' to look at all controls that have the correct
' settings in the Tag properties.

Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer
Dim varControlSource As Variant
Dim ctl As Control
'var for list control reference


Const conAND As String = " AND "



For Each ctl In frm.Controls
' Get the original control source.
varControlSource = adhCtlTagGetItem(ctl, "qbfField")
If Not IsNull(varControlSource) Then
' If the value of the control isn't null...
If Not IsNull(ctl) Then
' then get the value.
varDataType = adhCtlTagGetItem(ctl, "qbfType")
If Not IsNull(varDataType) Then
strTemp = "(" & BuildSQLString(varControlSource,
ctl,
varDataType) & ")"
strLocalSQL = strLocalSQL & conAND & strTemp
End If
End If
End If
Next ctl


' Trim off the leading " AND "
If Len(strLocalSQL) > 0 Then
BuildWHEREClause = "(" & Mid$(strLocalSQL, Len(conAND) + 1) &
")"
End If
End Function

Public Function DoQBF(ByVal strFormName As String, _
Optional blnCloseIt As Boolean = True) As String

' Load the specified form as a QBF form. If
' the form is still loaded when control returns
' to this function, then it will attempt to
' build an SQL WHERE clause describing the
' values in the fields. DoQBF() will return
' either that SQL string or an empty string,
' depending on what the user chose to do and
' whether or not any fields were filled in.

' In:
' strFormName: Name of the form to load
' blnCloseIt: Close the form, if the user didn't?
' Out:
' Return Value: The calculated SQL string.

Dim strSQL As String

DoCmd.OpenForm strFormName, WindowMode:=acDialog

' You won't get here until user hides or closes the form.
' If the user closed the form, there's nothing
' to be done. Otherwise, build up the SQL WHERE
' clause. Once you're done, if the caller requested
' the QBF form to be closed, close it now.
If IsFormLoaded(strFormName) Then
strSQL = BuildWHEREClause(Forms(strFormName))
If blnCloseIt Then
DoCmd.Close acForm, strFormName
End If
End If
DoQBF = strSQL
End Function

Public Function QBFDoClose()
' This is a function so it can be called easily
' from the Properties window directly.

' Close the current form.
On Error Resume Next
DoCmd.Close
End Function

Public Function QBFDoHide(frm As Form)
' This is a function so it can be called easily
' from the Properties window directly.

Dim strSQL As String
Dim strParent As String

'Get the name of the Parent form
strParent = adhGetItem(frm.Tag, "Parent") & vbNullString

'Create the appropriate WHERE clause based on the fields with data
in
them.
strSQL = DoQBF(frm.Name, False)

If Len(strParent) > 0 Then
'Open the Parent form filtered with the Where clause generated
above
DoCmd.OpenForm FormName:=strParent, View:=acNormal,
WhereCondition:=strSQL
End If

'Make this QBF form invisible.
frm.Visible = False
End Function

 

[Douglas J. Steele]

Are you trying to use the code you posted (BuildWhereClause, BuildSQLString
and so on) or not?

As I've tried to indicate, it looks like a non-trivial task to alter that
code to handle multi-select.

On the other hand, if you're going to write your own custom code, you can
use what I suggested as a starting point.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug, No I don't want to dynamically determine if the control is a list
box, etc. I just want to query my list boxes on my form from within the
code.

Please, where would you insert the code just as it is?

Thanks, Jenny

You could probably mess with BuildWhereClause, but it will take a fair
bit
of effort.

Where that routine calls BuildSQLString, you'd need to determine whether
the
control is a multiselect listbox or not. If it isn't, you'd continue to
call
BuildSQLString. If it is, you'd want to use the code I showed instead.
And,
since I was lazy, the code I gave you isn't nearly as comprehensive as
BuildSQLString: you'll need to expand it to determine whether you need
quotes or not and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks, Doug--especially for such a fast response! One question:
Where
do I
put this code?

How do I alter these sections of the form to include this code -- sorry
pretty new to VB, but I've read enough that I do get your code.
1. Function: BuildSQLString: How to add list box selections to the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to the
Where clause of the where clause?

:

http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" is an
example of how to use a multiselect list box, although I have to admit
I
prefer using the IN operator to repeating the field name multiple
times:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & ", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


If EmpID were text, you'd need

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & Chr$(34) & ctl.ItemData(varItem) & Chr$(34)
&
", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have created a form to filter records using the download at MS,
QBF.exe.
The code for the generated form is below and it works great until I
add
list
boxes. It's the only example I've found where I can type "between
100
and
200" in a text control and/or use wildcards (see IsOperator
Function).

However I need to add 3 multiselect listboxes to the form. How do I
change
the code below to allow for the results of selections of the three
multiselect list boxes?

1. Function: BuildSQLString: How to add list box selections to
the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to
the
Where clause of the where clause?
Or, does the code for the list boxes need to be in Function
QBFDoHide?

***I am not asking for advice on changing the code behind the form
that
creates the QBF, just in the resulting form.

Thanks so much in advance,
Jenny

*********CODE FOLLOWS*********
Option Compare Database 'Use database order for string comparisons
Option Explicit

' REQUIRES A REFERENCE TO Microsoft DAO 3.6.

Const QUOTE = """"

' This string is the text that gets appended
' to the chosen form name, once it's become a
' QBF form. It's completely arbitrary, and can be
' anything you like.
Public Const conQBFSuffix = "_QBF"

Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)

' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.

Dim strTemp As String

On Error GoTo HandleErrors

If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If

' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS with
the
text we got.
' This is probably a LOT slower. If you want direct
matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE & varFieldValue &
QUOTE
strTemp = strTemp & " LIKE " & QUOTE & varFieldValue
&
"*"
&
QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle,
dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue & "#"
Case Else
' This function really can't handle any of the other
data
types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If

BuildSQLString = strTemp

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")",
vbExclamation, "BuildSQLString"
strTemp = vbNullString
Resume ExitHere
End Function

Private Function BuildWHEREClause(frm As Form) As String

' Build the full WHERE clause based on fields
' on the passed-in form. This function attempts
' to look at all controls that have the correct
' settings in the Tag properties.

Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer
Dim varControlSource As Variant
Dim ctl As Control
'var for list control reference


Const conAND As String = " AND "



For Each ctl In frm.Controls
' Get the original control source.
varControlSource = adhCtlTagGetItem(ctl, "qbfField")
If Not IsNull(varControlSource) Then
' If the value of the control isn't null...
If Not IsNull(ctl) Then
' then get the value.
varDataType = adhCtlTagGetItem(ctl, "qbfType")
If Not IsNull(varDataType) Then
strTemp = "(" & BuildSQLString(varControlSource,
ctl,
varDataType) & ")"
strLocalSQL = strLocalSQL & conAND & strTemp
End If
End If
End If
Next ctl


' Trim off the leading " AND "
If Len(strLocalSQL) > 0 Then
BuildWHEREClause = "(" & Mid$(strLocalSQL, Len(conAND) + 1) &
")"
End If
End Function

Public Function DoQBF(ByVal strFormName As String, _
Optional blnCloseIt As Boolean = True) As String

' Load the specified form as a QBF form. If
' the form is still loaded when control returns
' to this function, then it will attempt to
' build an SQL WHERE clause describing the
' values in the fields. DoQBF() will return
' either that SQL string or an empty string,
' depending on what the user chose to do and
' whether or not any fields were filled in.

' In:
' strFormName: Name of the form to load
' blnCloseIt: Close the form, if the user didn't?
' Out:
' Return Value: The calculated SQL string.

Dim strSQL As String

DoCmd.OpenForm strFormName, WindowMode:=acDialog

' You won't get here until user hides or closes the form.
' If the user closed the form, there's nothing
' to be done. Otherwise, build up the SQL WHERE
' clause. Once you're done, if the caller requested
' the QBF form to be closed, close it now.
If IsFormLoaded(strFormName) Then
strSQL = BuildWHEREClause(Forms(strFormName))
If blnCloseIt Then
DoCmd.Close acForm, strFormName
End If
End If
DoQBF = strSQL
End Function

Public Function QBFDoClose()
' This is a function so it can be called easily
' from the Properties window directly.

' Close the current form.
On Error Resume Next
DoCmd.Close
End Function

Public Function QBFDoHide(frm As Form)
' This is a function so it can be called easily
' from the Properties window directly.

Dim strSQL As String
Dim strParent As String

'Get the name of the Parent form
strParent = adhGetItem(frm.Tag, "Parent") & vbNullString

'Create the appropriate WHERE clause based on the fields with
data
in
them.
strSQL = DoQBF(frm.Name, False)

If Len(strParent) > 0 Then
'Open the Parent form filtered with the Where clause
generated
above
DoCmd.OpenForm FormName:=strParent, View:=acNormal,
WhereCondition:=strSQL
End If

'Make this QBF form invisible.
frm.Visible = False
End Function

 

[jeninOk]

I'm sorry I wasn't very clear with my questions -- I'm not asking for what
you may think I'm asking for. I do not care to alter the code to BUILD the
query forms, just to add a section to each query form to get the values from
my list boxes and add them to the query string. Also, I don't care to take
the time to make the code dynamic -- I will address each list box control
individually, rather that expecting the form to find all my list boxes like
it does with simple controls.

Yes (unfortunately) I have to alter that code because I've spent
considerable time using it to build search forms. I used the qbf2k.exe
because the readme file that came with it says, "You can enter text into
these controls, make choices from list boxes, or click check boxes just as
though you're entering data". This project is due on Monday, so no time to
completely rebuild the qbf forms.

I think my questions are just :

Where in the code do I insert the code you suggested?

How do I add the value of the where clause built by the list box selections
to the query string the form uses?

Or at least I hope it's that simple, if not, I'm in trouble.

Thanks again for your time, Doug,
Jenny

Are you trying to use the code you posted (BuildWhereClause, BuildSQLString
and so on) or not?

As I've tried to indicate, it looks like a non-trivial task to alter that
code to handle multi-select.

On the other hand, if you're going to write your own custom code, you can
use what I suggested as a starting point.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug, No I don't want to dynamically determine if the control is a list
box, etc. I just want to query my list boxes on my form from within the
code.

Please, where would you insert the code just as it is?

Thanks, Jenny

You could probably mess with BuildWhereClause, but it will take a fair
bit
of effort.

Where that routine calls BuildSQLString, you'd need to determine whether
the
control is a multiselect listbox or not. If it isn't, you'd continue to
call
BuildSQLString. If it is, you'd want to use the code I showed instead.
And,
since I was lazy, the code I gave you isn't nearly as comprehensive as
BuildSQLString: you'll need to expand it to determine whether you need
quotes or not and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks, Doug--especially for such a fast response! One question:
Where
do I
put this code?

How do I alter these sections of the form to include this code -- sorry
pretty new to VB, but I've read enough that I do get your code.
1. Function: BuildSQLString: How to add list box selections to the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to the
Where clause of the where clause?

:

http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" is an
example of how to use a multiselect list box, although I have to admit
I
prefer using the IN operator to repeating the field name multiple
times:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & ", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


If EmpID were text, you'd need

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & Chr$(34) & ctl.ItemData(varItem) & Chr$(34)
&
", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have created a form to filter records using the download at MS,
QBF.exe.
The code for the generated form is below and it works great until I
add
list
boxes. It's the only example I've found where I can type "between
100
and
200" in a text control and/or use wildcards (see IsOperator
Function).

However I need to add 3 multiselect listboxes to the form. How do I
change
the code below to allow for the results of selections of the three
multiselect list boxes?

1. Function: BuildSQLString: How to add list box selections to
the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to
the
Where clause of the where clause?
Or, does the code for the list boxes need to be in Function
QBFDoHide?

***I am not asking for advice on changing the code behind the form
that
creates the QBF, just in the resulting form.

Thanks so much in advance,
Jenny

*********CODE FOLLOWS*********
Option Compare Database 'Use database order for string comparisons
Option Explicit

' REQUIRES A REFERENCE TO Microsoft DAO 3.6.

Const QUOTE = """"

' This string is the text that gets appended
' to the chosen form name, once it's become a
' QBF form. It's completely arbitrary, and can be
' anything you like.
Public Const conQBFSuffix = "_QBF"

Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)

' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.

Dim strTemp As String

On Error GoTo HandleErrors

If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If

' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS with
the
text we got.
' This is probably a LOT slower. If you want direct
matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE & varFieldValue &
QUOTE
strTemp = strTemp & " LIKE " & QUOTE & varFieldValue
&
"*"
&
QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle,
dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue & "#"
Case Else
' This function really can't handle any of the other
data
types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If

BuildSQLString = strTemp

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")",
vbExclamation, "BuildSQLString"
strTemp = vbNullString
Resume ExitHere
End Function

Private Function BuildWHEREClause(frm As Form) As String

' Build the full WHERE clause based on fields
' on the passed-in form. This function attempts
' to look at all controls that have the correct
' settings in the Tag properties.

Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer
Dim varControlSource As Variant
Dim ctl As Control
'var for list control reference


Const conAND As String = " AND "



For Each ctl In frm.Controls
' Get the original control source.
varControlSource = adhCtlTagGetItem(ctl, "qbfField")
If Not IsNull(varControlSource) Then
' If the value of the control isn't null...
If Not IsNull(ctl) Then
' then get the value.
varDataType = adhCtlTagGetItem(ctl, "qbfType")
If Not IsNull(varDataType) Then
strTemp = "(" & BuildSQLString(varControlSource,
ctl,
varDataType) & ")"
strLocalSQL = strLocalSQL & conAND & strTemp
End If
End If
End If
Next ctl


' Trim off the leading " AND "
If Len(strLocalSQL) > 0 Then
BuildWHEREClause = "(" & Mid$(strLocalSQL, Len(conAND) + 1) &
")"
End If
End Function

Public Function DoQBF(ByVal strFormName As String, _
Optional blnCloseIt As Boolean = True) As String

' Load the specified form as a QBF form. If
' the form is still loaded when control returns
' to this function, then it will attempt to
' build an SQL WHERE clause describing the
' values in the fields. DoQBF() will return
' either that SQL string or an empty string,
' depending on what the user chose to do and
' whether or not any fields were filled in.

' In:
' strFormName: Name of the form to load
' blnCloseIt: Close the form, if the user didn't?
' Out:
' Return Value: The calculated SQL string.

Dim strSQL As String

DoCmd.OpenForm strFormName, WindowMode:=acDialog

' You won't get here until user hides or closes the form.
' If the user closed the form, there's nothing
' to be done. Otherwise, build up the SQL WHERE
' clause. Once you're done, if the caller requested

 

[Douglas J. Steele]

I'm sorry, it's very difficult for me to tell you where you need to put the
code as I don't know what your form looks like, what events you're trying to
tie into and so on.

Your best bet is probably to add a button that will allow the user to
generate the SQL.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm sorry I wasn't very clear with my questions -- I'm not asking for what
you may think I'm asking for. I do not care to alter the code to BUILD
the
query forms, just to add a section to each query form to get the values
from
my list boxes and add them to the query string. Also, I don't care to take
the time to make the code dynamic -- I will address each list box control
individually, rather that expecting the form to find all my list boxes
like
it does with simple controls.

Yes (unfortunately) I have to alter that code because I've spent
considerable time using it to build search forms. I used the qbf2k.exe
because the readme file that came with it says, "You can enter text into
these controls, make choices from list boxes, or click check boxes just as
though you're entering data". This project is due on Monday, so no time
to
completely rebuild the qbf forms.

I think my questions are just :

Where in the code do I insert the code you suggested?

How do I add the value of the where clause built by the list box
selections
to the query string the form uses?

Or at least I hope it's that simple, if not, I'm in trouble.

Thanks again for your time, Doug,
Jenny

Are you trying to use the code you posted (BuildWhereClause,
BuildSQLString
and so on) or not?

As I've tried to indicate, it looks like a non-trivial task to alter that
code to handle multi-select.

On the other hand, if you're going to write your own custom code, you can
use what I suggested as a starting point.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug, No I don't want to dynamically determine if the control is a
list
box, etc. I just want to query my list boxes on my form from within
the
code.

Please, where would you insert the code just as it is?

Thanks, Jenny

:

You could probably mess with BuildWhereClause, but it will take a fair
bit
of effort.

Where that routine calls BuildSQLString, you'd need to determine
whether
the
control is a multiselect listbox or not. If it isn't, you'd continue
to
call
BuildSQLString. If it is, you'd want to use the code I showed instead.
And,
since I was lazy, the code I gave you isn't nearly as comprehensive as
BuildSQLString: you'll need to expand it to determine whether you need
quotes or not and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks, Doug--especially for such a fast response! One question:
Where
do I
put this code?

How do I alter these sections of the form to include this code --
sorry
pretty new to VB, but I've read enough that I do get your code.
1. Function: BuildSQLString: How to add list box selections to
the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to
the
Where clause of the where clause?

:

http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" is
an
example of how to use a multiselect list box, although I have to
admit
I
prefer using the IN operator to repeating the field name multiple
times:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & ", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


If EmpID were text, you'd need

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & Chr$(34) & ctl.ItemData(varItem) &
Chr$(34)
&
", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have created a form to filter records using the download at MS,
QBF.exe.
The code for the generated form is below and it works great until
I
add
list
boxes. It's the only example I've found where I can type "between
100
and
200" in a text control and/or use wildcards (see IsOperator
Function).

However I need to add 3 multiselect listboxes to the form. How
do I
change
the code below to allow for the results of selections of the
three
multiselect list boxes?

1. Function: BuildSQLString: How to add list box selections to
the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections
to
the
Where clause of the where clause?
Or, does the code for the list boxes need to be in Function
QBFDoHide?

***I am not asking for advice on changing the code behind the
form
that
creates the QBF, just in the resulting form.

Thanks so much in advance,
Jenny

*********CODE FOLLOWS*********
Option Compare Database 'Use database order for string
comparisons
Option Explicit

' REQUIRES A REFERENCE TO Microsoft DAO 3.6.

Const QUOTE = """"

' This string is the text that gets appended
' to the chosen form name, once it's become a
' QBF form. It's completely arbitrary, and can be
' anything you like.
Public Const conQBFSuffix = "_QBF"

Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)

' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.

Dim strTemp As String

On Error GoTo HandleErrors

If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If

' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS
with
the
text we got.
' This is probably a LOT slower. If you want
direct
matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE &
varFieldValue &
QUOTE
strTemp = strTemp & " LIKE " & QUOTE &
varFieldValue
&
"*"
&
QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle,
dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue &
"#"
Case Else
' This function really can't handle any of the
other
data
types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If

BuildSQLString = strTemp

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")",
vbExclamation, "BuildSQLString"
strTemp = vbNullString
Resume ExitHere
End Function

Private Function BuildWHEREClause(frm As Form) As String

' Build the full WHERE clause based on fields
' on the passed-in form. This function attempts
' to look at all controls that have the correct
' settings in the Tag properties.

Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer
Dim varControlSource As Variant
Dim ctl As Control
'var for list control reference


Const conAND As String = " AND "



For Each ctl In frm.Controls
' Get the original control source.
varControlSource = adhCtlTagGetItem(ctl, "qbfField")
If Not IsNull(varControlSource) Then
' If the value of the control isn't null...
If Not IsNull(ctl) Then
' then get the value.
varDataType = adhCtlTagGetItem(ctl, "qbfType")
If Not IsNull(varDataType) Then
strTemp = "(" &
BuildSQLString(varControlSource,
ctl,
varDataType) & ")"
strLocalSQL = strLocalSQL & conAND & strTemp
End If
End If
End If
Next ctl


' Trim off the leading " AND "
If Len(strLocalSQL) > 0 Then
BuildWHEREClause = "(" & Mid$(strLocalSQL, Len(conAND) +
1) &
")"
End If
End Function

Public Function DoQBF(ByVal strFormName As String, _
Optional blnCloseIt As Boolean = True) As String

' Load the specified form as a QBF form. If
' the form is still loaded when control returns
' to this function, then it will attempt to
' build an SQL WHERE clause describing the
' values in the fields. DoQBF() will return
' either that SQL string or an empty string,
' depending on what the user chose to do and
' whether or not any fields were filled in.

' In:
' strFormName: Name of the form to load
' blnCloseIt: Close the form, if the user didn't?
' Out:
' Return Value: The calculated SQL string.

Dim strSQL As String

DoCmd.OpenForm strFormName, WindowMode:=acDialog

' You won't get here until user hides or closes the form.
' If the user closed the form, there's nothing
' to be done. Otherwise, build up the SQL WHERE
' clause. Once you're done, if the caller requested

 

[jeninOk]

I need to allow the user to enter criteria using operators into any text
control and am using Ken Getz's qbf2k.ext
-- NOTE: The text controls and combo controls work like a charm, but --
there is an error thrown whenever I select from the list control. Why would
it show &lbOptions& rather than my selections? Why is there an extra (?

ERROR:
Run-time error '3075'
Missing ),], or Item in query expression '(([County] In ('&lbOptions &'))


CODE COURTESY KEN GETZ:

Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)

' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.

Dim strTemp As String

On Error GoTo HandleErrors

If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If

' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS with the text we got.
' This is probably a LOT slower. If you want direct matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE & varFieldValue & QUOTE
strTemp = strTemp & " LIKE " & QUOTE & varFieldValue & "*" & QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue & "#"
Case Else
' This function really can't handle any of the other data types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If

BuildSQLString = strTemp

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")", vbExclamation,
"BuildSQLString"
strTemp = vbNullString
Resume ExitHere
End Function

Private Function BuildWHEREClause(frm As Form) As String

' Build the full WHERE clause based on fields
' on the passed-in form. This function attempts
' to look at all controls that have the correct
' settings in the Tag properties.

Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer
Dim varControlSource As Variant
Dim ctl As Control
'var for list control reference
' ********************BEGIN LBO CODE--THIS IS NEW CODE AND NOT WORKING
Dim lbOptions As Variant
Dim varItem As Variant
Dim where As Variant

lbOptions = Null

For Each varItem In frm.lstCounty.ItemsSelected
lbOptions = (lbOptions + ",") & "'" & frm.lstCounty.ItemData(varItem) & "'"
Next varItem

If Not IsNull(lbOptions) Then
where = where & ") AND([County] In (' & lbOptions & ')"
'***changed " ' around & lbOptions &
'***added ( before [County]
'***added ) before the AND
Debug.Print "LBOwhere:"; where
End If
'' END LBO CODE


Const conAND As String = " AND "



For Each ctl In frm.Controls
' Get the original control source.
varControlSource = adhCtlTagGetItem(ctl, "qbfField")
If Not IsNull(varControlSource) Then
' If the value of the control isn't null...
If Not IsNull(ctl) Then
' then get the value.
varDataType = adhCtlTagGetItem(ctl, "qbfType")
If Not IsNull(varDataType) Then
strTemp = "(" & BuildSQLString(varControlSource, ctl, varDataType) & ")"
Debug.Print "strTemp:"; strTemp '****trying to see the sql

strLocalSQL = strLocalSQL & conAND & strTemp
Debug.Print "strLocalSQL:"; strLocalSQL '****added to see the sql
End If
End If
End If
Next ctl
'****try adding var 'where' from LBO code here:
strLocalSQL = strLocalSQL & where '***********THIS IS NEW CODE


' Trim off the leading " AND "
If Len(strLocalSQL) > 0 Then
BuildWHEREClause = "(" & Mid$(strLocalSQL, Len(conAND) + 1) & ")"
Debug.Print "BuildWHEREClause:"; BuildWHEREClause '**** to see the sql
End If

End Function

Public Function DoQBF(ByVal strFormName As String, _
Optional blnCloseIt As Boolean = True) As String

' Load the specified form as a QBF form. If
' the form is still loaded when control returns
' to this function, then it will attempt to
' build an SQL WHERE clause describing the
' values in the fields. DoQBF() will return
' either that SQL string or an empty string,
' depending on what the user chose to do and
' whether or not any fields were filled in.

' In:
' strFormName: Name of the form to load
' blnCloseIt: Close the form, if the user didn't?
' Out:
' Return Value: The calculated SQL string.

Dim strSQL As String

DoCmd.OpenForm strFormName, WindowMode:=acDialog
'*******************************************
Private Function IsOperator(varValue As Variant) As Boolean

' Return a logical value indicating whether a
' value passed in is an operator or not.
' This is NOT infallible, and may need correcting.

Dim strTemp As String

strTemp = Trim$(UCase(varValue))
IsOperator = False

' Check first character for <,>, or =
If InStr(1, "<>=", Left$(strTemp, 1)) > 0 Then
IsOperator = True
' Check for IN (x,y,z)
ElseIf ((Left$(strTemp, 4) = "IN (") And (Right$(strTemp, 1) = ")")) Then
IsOperator = True
' Check for BETWEEN ... AND ...
ElseIf ((Left$(strTemp, 8) = "BETWEEN ") And (InStr(1, strTemp, " AND ") >
0)) Then
IsOperator = True
' Check for NOT xxx
ElseIf (Left$(strTemp, 4) = "NOT ") Then
IsOperator = True
' Check for LIKE xxx
ElseIf (Left$(strTemp, 5) = "LIKE ") Then
IsOperator = True
End If
End Function

I'm sorry, it's very difficult for me to tell you where you need to put the
code as I don't know what your form looks like, what events you're trying to
tie into and so on.

Your best bet is probably to add a button that will allow the user to
generate the SQL.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm sorry I wasn't very clear with my questions -- I'm not asking for what
you may think I'm asking for. I do not care to alter the code to BUILD
the
query forms, just to add a section to each query form to get the values
from
my list boxes and add them to the query string. Also, I don't care to take
the time to make the code dynamic -- I will address each list box control
individually, rather that expecting the form to find all my list boxes
like
it does with simple controls.

Yes (unfortunately) I have to alter that code because I've spent
considerable time using it to build search forms. I used the qbf2k.exe
because the readme file that came with it says, "You can enter text into
these controls, make choices from list boxes, or click check boxes just as
though you're entering data". This project is due on Monday, so no time
to
completely rebuild the qbf forms.

I think my questions are just :

Where in the code do I insert the code you suggested?

How do I add the value of the where clause built by the list box
selections
to the query string the form uses?

Or at least I hope it's that simple, if not, I'm in trouble.

Thanks again for your time, Doug,
Jenny

Are you trying to use the code you posted (BuildWhereClause,
BuildSQLString
and so on) or not?

As I've tried to indicate, it looks like a non-trivial task to alter that
code to handle multi-select.

On the other hand, if you're going to write your own custom code, you can
use what I suggested as a starting point.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug, No I don't want to dynamically determine if the control is a
list
box, etc. I just want to query my list boxes on my form from within
the
code.

Please, where would you insert the code just as it is?

Thanks, Jenny

:

You could probably mess with BuildWhereClause, but it will take a fair
bit
of effort.

Where that routine calls BuildSQLString, you'd need to determine
whether
the
control is a multiselect listbox or not. If it isn't, you'd continue
to
call
BuildSQLString. If it is, you'd want to use the code I showed instead.
And,
since I was lazy, the code I gave you isn't nearly as comprehensive as
BuildSQLString: you'll need to expand it to determine whether you need
quotes or not and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks, Doug--especially for such a fast response! One question:
Where
do I
put this code?

How do I alter these sections of the form to include this code --
sorry
pretty new to VB, but I've read enough that I do get your code.
1. Function: BuildSQLString: How to add list box selections to
the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to
the
Where clause of the where clause?

:

http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" is
an
example of how to use a multiselect list box, although I have to
admit
I
prefer using the IN operator to repeating the field name multiple
times:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & ", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


If EmpID were text, you'd need

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & Chr$(34) & ctl.ItemData(varItem) &
Chr$(34)
&
", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have created a form to filter records using the download at MS,
QBF.exe.
The code for the generated form is below and it works great until
I
add
list
boxes. It's the only example I've found where I can type "between
100
and
200" in a text control and/or use wildcards (see IsOperator
Function).

However I need to add 3 multiselect listboxes to the form. How
do I
change
the code below to allow for the results of selections of the
three
multiselect list boxes?

1. Function: BuildSQLString: How to add list box selections to
the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections
to
the
Where clause of the where clause?
Or, does the code for the list boxes need to be in Function
QBFDoHide?

***I am not asking for advice on changing the code behind the
form
that
creates the QBF, just in the resulting form.

Thanks so much in advance,
Jenny

*********CODE FOLLOWS*********
Option Compare Database 'Use database order for string
comparisons
Option Explicit

' REQUIRES A REFERENCE TO Microsoft DAO 3.6.

Const QUOTE = """"

' This string is the text that gets appended
' to the chosen form name, once it's become a
' QBF form. It's completely arbitrary, and can be
' anything you like.
Public Const conQBFSuffix = "_QBF"

Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)

' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.

Dim strTemp As String

On Error GoTo HandleErrors

If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If

' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS
with
the
text we got.
' This is probably a LOT slower. If you want
direct
matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE &
varFieldValue &
QUOTE
strTemp = strTemp & " LIKE " & QUOTE &
varFieldValue
&
"*"
&
QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle,
dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue &
"#"
Case Else
' This function really can't handle any of the
other
data
types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If

BuildSQLString = strTemp

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")",
vbExclamation, "BuildSQLString"
strTemp = vbNullString

 

[jeninOk]

Thanks, Doug. Not sure what you mean by that--do you mean to use the Dynamic
Query method?

I'm sorry, it's very difficult for me to tell you where you need to put the
code as I don't know what your form looks like, what events you're trying to
tie into and so on.

Your best bet is probably to add a button that will allow the user to
generate the SQL.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm sorry I wasn't very clear with my questions -- I'm not asking for what
you may think I'm asking for. I do not care to alter the code to BUILD
the
query forms, just to add a section to each query form to get the values
from
my list boxes and add them to the query string. Also, I don't care to take
the time to make the code dynamic -- I will address each list box control
individually, rather that expecting the form to find all my list boxes
like
it does with simple controls.

Yes (unfortunately) I have to alter that code because I've spent
considerable time using it to build search forms. I used the qbf2k.exe
because the readme file that came with it says, "You can enter text into
these controls, make choices from list boxes, or click check boxes just as
though you're entering data". This project is due on Monday, so no time
to
completely rebuild the qbf forms.

I think my questions are just :

Where in the code do I insert the code you suggested?

How do I add the value of the where clause built by the list box
selections
to the query string the form uses?

Or at least I hope it's that simple, if not, I'm in trouble.

Thanks again for your time, Doug,
Jenny

Are you trying to use the code you posted (BuildWhereClause,
BuildSQLString
and so on) or not?

As I've tried to indicate, it looks like a non-trivial task to alter that
code to handle multi-select.

On the other hand, if you're going to write your own custom code, you can
use what I suggested as a starting point.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug, No I don't want to dynamically determine if the control is a
list
box, etc. I just want to query my list boxes on my form from within
the
code.

Please, where would you insert the code just as it is?

Thanks, Jenny

:

You could probably mess with BuildWhereClause, but it will take a fair
bit
of effort.

Where that routine calls BuildSQLString, you'd need to determine
whether
the
control is a multiselect listbox or not. If it isn't, you'd continue
to
call
BuildSQLString. If it is, you'd want to use the code I showed instead.
And,
since I was lazy, the code I gave you isn't nearly as comprehensive as
BuildSQLString: you'll need to expand it to determine whether you need
quotes or not and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks, Doug--especially for such a fast response! One question:
Where
do I
put this code?

How do I alter these sections of the form to include this code --
sorry
pretty new to VB, but I've read enough that I do get your code.
1. Function: BuildSQLString: How to add list box selections to
the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections to
the
Where clause of the where clause?

:

http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" is
an
example of how to use a multiselect list box, although I have to
admit
I
prefer using the IN operator to repeating the field name multiple
times:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & ", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


If EmpID were text, you'd need

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & Chr$(34) & ctl.ItemData(varItem) &
Chr$(34)
&
", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have created a form to filter records using the download at MS,
QBF.exe.
The code for the generated form is below and it works great until
I
add
list
boxes. It's the only example I've found where I can type "between
100
and
200" in a text control and/or use wildcards (see IsOperator
Function).

However I need to add 3 multiselect listboxes to the form. How
do I
change
the code below to allow for the results of selections of the
three
multiselect list boxes?

1. Function: BuildSQLString: How to add list box selections to
the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections
to
the
Where clause of the where clause?
Or, does the code for the list boxes need to be in Function
QBFDoHide?

***I am not asking for advice on changing the code behind the
form
that
creates the QBF, just in the resulting form.

Thanks so much in advance,
Jenny

*********CODE FOLLOWS*********
Option Compare Database 'Use database order for string
comparisons
Option Explicit

' REQUIRES A REFERENCE TO Microsoft DAO 3.6.

Const QUOTE = """"

' This string is the text that gets appended
' to the chosen form name, once it's become a
' QBF form. It's completely arbitrary, and can be
' anything you like.
Public Const conQBFSuffix = "_QBF"

Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)

' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.

Dim strTemp As String

On Error GoTo HandleErrors

If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If

' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS
with
the
text we got.
' This is probably a LOT slower. If you want
direct
matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE &
varFieldValue &
QUOTE
strTemp = strTemp & " LIKE " & QUOTE &
varFieldValue
&
"*"
&
QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle,
dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue &
"#"
Case Else
' This function really can't handle any of the
other
data
types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If

BuildSQLString = strTemp

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")",
vbExclamation, "BuildSQLString"
strTemp = vbNullString

 

[Douglas J. Steele]

Change

where = where & ") AND([County] In (' & lbOptions & ')"

to

where = where & ") AND([County] In (" & lbOptions & "))"

You need to use double quotes there rather than the single quotes you were
using. Also, since you're putting two opening parentheses (one between AND
and [County] and one after In), you need to have two closing parentheses.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need to allow the user to enter criteria using operators into any text
control and am using Ken Getz's qbf2k.ext
-- NOTE: The text controls and combo controls work like a charm, but --
there is an error thrown whenever I select from the list control. Why
would
it show &lbOptions& rather than my selections? Why is there an extra (?

ERROR:
Run-time error '3075'
Missing ),], or Item in query expression '(([County] In ('&lbOptions &'))


CODE COURTESY KEN GETZ:

Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)

' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.

Dim strTemp As String

On Error GoTo HandleErrors

If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If

' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS with the text we got.
' This is probably a LOT slower. If you want direct matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE & varFieldValue & QUOTE
strTemp = strTemp & " LIKE " & QUOTE & varFieldValue & "*" & QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue & "#"
Case Else
' This function really can't handle any of the other data types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If

BuildSQLString = strTemp

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")",
vbExclamation,
"BuildSQLString"
strTemp = vbNullString
Resume ExitHere
End Function

Private Function BuildWHEREClause(frm As Form) As String

' Build the full WHERE clause based on fields
' on the passed-in form. This function attempts
' to look at all controls that have the correct
' settings in the Tag properties.

Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer
Dim varControlSource As Variant
Dim ctl As Control
'var for list control reference
' ********************BEGIN LBO CODE--THIS IS NEW CODE AND NOT WORKING
Dim lbOptions As Variant
Dim varItem As Variant
Dim where As Variant

lbOptions = Null

For Each varItem In frm.lstCounty.ItemsSelected
lbOptions = (lbOptions + ",") & "'" & frm.lstCounty.ItemData(varItem) &
"'"
Next varItem

If Not IsNull(lbOptions) Then
where = where & ") AND([County] In (' & lbOptions & ')"
'***changed " ' around & lbOptions &
'***added ( before [County]
'***added ) before the AND
Debug.Print "LBOwhere:"; where
End If
'' END LBO CODE


Const conAND As String = " AND "



For Each ctl In frm.Controls
' Get the original control source.
varControlSource = adhCtlTagGetItem(ctl, "qbfField")
If Not IsNull(varControlSource) Then
' If the value of the control isn't null...
If Not IsNull(ctl) Then
' then get the value.
varDataType = adhCtlTagGetItem(ctl, "qbfType")
If Not IsNull(varDataType) Then
strTemp = "(" & BuildSQLString(varControlSource, ctl, varDataType) & ")"
Debug.Print "strTemp:"; strTemp '****trying to see the sql

strLocalSQL = strLocalSQL & conAND & strTemp
Debug.Print "strLocalSQL:"; strLocalSQL '****added to see the sql
End If
End If
End If
Next ctl
'****try adding var 'where' from LBO code here:
strLocalSQL = strLocalSQL & where '***********THIS IS NEW CODE


' Trim off the leading " AND "
If Len(strLocalSQL) > 0 Then
BuildWHEREClause = "(" & Mid$(strLocalSQL, Len(conAND) + 1) & ")"
Debug.Print "BuildWHEREClause:"; BuildWHEREClause '**** to see the sql
End If

End Function

Public Function DoQBF(ByVal strFormName As String, _
Optional blnCloseIt As Boolean = True) As String

' Load the specified form as a QBF form. If
' the form is still loaded when control returns
' to this function, then it will attempt to
' build an SQL WHERE clause describing the
' values in the fields. DoQBF() will return
' either that SQL string or an empty string,
' depending on what the user chose to do and
' whether or not any fields were filled in.

' In:
' strFormName: Name of the form to load
' blnCloseIt: Close the form, if the user didn't?
' Out:
' Return Value: The calculated SQL string.

Dim strSQL As String

DoCmd.OpenForm strFormName, WindowMode:=acDialog
'*******************************************
Private Function IsOperator(varValue As Variant) As Boolean

' Return a logical value indicating whether a
' value passed in is an operator or not.
' This is NOT infallible, and may need correcting.

Dim strTemp As String

strTemp = Trim$(UCase(varValue))
IsOperator = False

' Check first character for <,>, or =
If InStr(1, "<>=", Left$(strTemp, 1)) > 0 Then
IsOperator = True
' Check for IN (x,y,z)
ElseIf ((Left$(strTemp, 4) = "IN (") And (Right$(strTemp, 1) = ")")) Then
IsOperator = True
' Check for BETWEEN ... AND ...
ElseIf ((Left$(strTemp, 8) = "BETWEEN ") And (InStr(1, strTemp, " AND ") >
0)) Then
IsOperator = True
' Check for NOT xxx
ElseIf (Left$(strTemp, 4) = "NOT ") Then
IsOperator = True
' Check for LIKE xxx
ElseIf (Left$(strTemp, 5) = "LIKE ") Then
IsOperator = True
End If
End Function

I'm sorry, it's very difficult for me to tell you where you need to put
the
code as I don't know what your form looks like, what events you're trying
to
tie into and so on.

Your best bet is probably to add a button that will allow the user to
generate the SQL.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm sorry I wasn't very clear with my questions -- I'm not asking for
what
you may think I'm asking for. I do not care to alter the code to BUILD
the
query forms, just to add a section to each query form to get the values
from
my list boxes and add them to the query string. Also, I don't care to
take
the time to make the code dynamic -- I will address each list box
control
individually, rather that expecting the form to find all my list boxes
like
it does with simple controls.

Yes (unfortunately) I have to alter that code because I've spent
considerable time using it to build search forms. I used the qbf2k.exe
because the readme file that came with it says, "You can enter text
into
these controls, make choices from list boxes, or click check boxes just
as
though you're entering data". This project is due on Monday, so no
time
to
completely rebuild the qbf forms.

I think my questions are just :

Where in the code do I insert the code you suggested?

How do I add the value of the where clause built by the list box
selections
to the query string the form uses?

Or at least I hope it's that simple, if not, I'm in trouble.

Thanks again for your time, Doug,
Jenny


:

Are you trying to use the code you posted (BuildWhereClause,
BuildSQLString
and so on) or not?

As I've tried to indicate, it looks like a non-trivial task to alter
that
code to handle multi-select.

On the other hand, if you're going to write your own custom code, you
can
use what I suggested as a starting point.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug, No I don't want to dynamically determine if the control is
a
list
box, etc. I just want to query my list boxes on my form from within
the
code.

Please, where would you insert the code just as it is?

Thanks, Jenny

:

You could probably mess with BuildWhereClause, but it will take a
fair
bit
of effort.

Where that routine calls BuildSQLString, you'd need to determine
whether
the
control is a multiselect listbox or not. If it isn't, you'd
continue
to
call
BuildSQLString. If it is, you'd want to use the code I showed
instead.
And,
since I was lazy, the code I gave you isn't nearly as comprehensive
as
BuildSQLString: you'll need to expand it to determine whether you
need
quotes or not and so on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks, Doug--especially for such a fast response! One question:
Where
do I
put this code?

How do I alter these sections of the form to include this code --
sorry
pretty new to VB, but I've read enough that I do get your code.
1. Function: BuildSQLString: How to add list box selections to
the
sqlstring?
2. Function: BuildWhereClause: How to add list box selections
to
the
Where clause of the where clause?

:

http://www.mvps.org/access/forms/frm0007.htm at "The Access Web"
is
an
example of how to use a multiselect list box, although I have to
admit
I
prefer using the IN operator to repeating the field name
multiple
times:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & ", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


If EmpID were text, you'd need

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID] IN ("
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & Chr$(34) & ctl.ItemData(varItem) &
Chr$(34)
&
", "
Next varItem

strSQL=left$(strSQL,len(strSQL)-2))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have created a form to filter records using the download at
MS,
QBF.exe.
The code for the generated form is below and it works great
until
I
add
list
boxes. It's the only example I've found where I can type
"between
100
and
200" in a text control and/or use wildcards (see IsOperator
Function).

However I need to add 3 multiselect listboxes to the form.
How
do I
change
the code below to allow for the results of selections of the
three
multiselect list boxes?

1. Function: BuildSQLString: How to add list box selections
to
the
sqlstring?
2. Function: BuildWhereClause: How to add list box
selections
to
the
Where clause of the where clause?
Or, does the code for the list boxes need to be in Function
QBFDoHide?

***I am not asking for advice on changing the code behind the
form
that
creates the QBF, just in the resulting form.

Thanks so much in advance,
Jenny

*********CODE FOLLOWS*********
Option Compare Database 'Use database order for string
comparisons
Option Explicit

' REQUIRES A REFERENCE TO Microsoft DAO 3.6.

Const QUOTE = """"

' This string is the text that gets appended
' to the chosen form name, once it's become a
' QBF form. It's completely arbitrary, and can be
' anything you like.
Public Const conQBFSuffix = "_QBF"

Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)

' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.

Dim strTemp As String

On Error GoTo HandleErrors

If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If

' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.

Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS
with
the
text we got.
' This is probably a LOT slower. If you want
direct
matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE &
varFieldValue &
QUOTE
strTemp = strTemp & " LIKE " & QUOTE &
varFieldValue
&
"*"
&
QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency,
dbSingle,
dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue
&
"#"
Case Else
' This function really can't handle any of the
other
data
types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If

BuildSQLString = strTemp

ExitHere:
Exit Function

HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number &
")",
vbExclamation, "BuildSQLString"
strTemp = vbNullString