QBF on Steroids with List Control --almost there, but problem

  • Thread starter Thread starter jeninOk
  • Start date Start date
J

jeninOk

Can anyone see what's wrong with the code for handling the list controls. I
would so much appreciate help with this. It is so close.

Codee below (look for ' ********************BEGIN LBO CODE)

The text controls and combo controls work like AWESOME, but --

there is an error thrown whenever I select from the list control.

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

Why would it show &lbOptions& rather than my selections? Why is there an
extra ( ?

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
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
 
Answered in the original thread.

I realize you said you've got a imminent deadline, but that's really no
reason to post the same question multiple times!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jeninOk said:
Can anyone see what's wrong with the code for handling the list controls.
I
would so much appreciate help with this. It is so close.

Codee below (look for ' ********************BEGIN LBO CODE)

The text controls and combo controls work like AWESOME, but --

there is an error thrown whenever I select from the list control.

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

Why would it show &lbOptions& rather than my selections? Why is there an
extra ( ?

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
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
Click to expand...
 
Doug, can you please look at the error and the code and let me know where the
problem is rather than suggesting an entirely different way to do this?
I do appreciate you and your time.
ERROR:
Run-time error '3075'
Missing ),], or Item in query expression '(([County] In ('&lbOptions &'))

Why would it show &lbOptions& rather than my selections? Why is there an
extra ( ?
Click to expand...
Click to expand...

Douglas J. Steele said:
Answered in the original thread.

I realize you said you've got a imminent deadline, but that's really no
reason to post the same question multiple times!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jeninOk said:
Can anyone see what's wrong with the code for handling the list controls.
I
would so much appreciate help with this. It is so close.

Codee below (look for ' ********************BEGIN LBO CODE)

The text controls and combo controls work like AWESOME, but --

there is an error thrown whenever I select from the list control.

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

Why would it show &lbOptions& rather than my selections? Why is there an
extra ( ?

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
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
Click to expand...
Click to expand...
 
As I said in the other thread

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!)



jeninOk said:
Doug, can you please look at the error and the code and let me know where
the
problem is rather than suggesting an entirely different way to do this?
I do appreciate you and your time.
ERROR:
Run-time error '3075'
Missing ),], or Item in query expression '(([County] In ('&lbOptions
&'))

Why would it show &lbOptions& rather than my selections? Why is there
an
extra ( ?
Click to expand...
Click to expand...

Douglas J. Steele said:
Answered in the original thread.

I realize you said you've got a imminent deadline, but that's really no
reason to post the same question multiple times!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jeninOk said:
Can anyone see what's wrong with the code for handling the list
controls.
I
would so much appreciate help with this. It is so close.

Codee below (look for ' ********************BEGIN LBO CODE)

The text controls and combo controls work like AWESOME, but --

there is an error thrown whenever I select from the list control.

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

Why would it show &lbOptions& rather than my selections? Why is there
an
extra ( ?

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
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top