Application.BuildCriteria & search form problems

[Tony Scullion]

Hello Group,

I am using a modified search form originally designed by
Dev Ashish & Terry Kreft. I have come across a problem
with the function 'Application.BuildCriteria', which I am
unable to solve and I was looking for expert advice.

The search form itself works well but fails under
conditions when searches are performed using reserved SQL
statements such as 'and' and '&'. My staff use the search
form daily and is vital in returning records of companies
such as - Seashore & Harbour Fisheries Ltd. Quite a lot
of my staff are not very knowledgeable with SQL etc and
much prefer to use the front end search form, which does
most of the work.

I have already done a lot of research in finding a
solution and I know it lies in adding delimiters around
entered criteria but I am unable to code and work it all
out. Would love some help with this as I can see myself
working for hours/days on this one and will probably get
nowhere.

Here is the code that the search form uses to build the
SQL statement, maybe of use.

Regards

Tony


Sub sBuildSQL()
' Take what's currently selected on the form
' and create a dynamic SQL statement for the
' lstResults listbox.
'

On Error GoTo ErrHandler 'Pick up and handle errors
Dim strSQL As String
Dim strWhere As String
Dim strJoinType As String
Dim strTable As String
Dim i As Integer
Dim j As Integer
Dim db As Database
Dim rs As Recordset
Dim tdf As TableDef
Dim qdf As QueryDef
Dim rsQdf As Recordset
Dim fld As Field
Const conMAXCONTROLS = 5

Set db = DBEngine(0)(0)
strSQL = "Select * "
'Right now we have five combo/textbox sets
'so set up the master loop to go through these controls
For i = 0 To conMAXCONTROLS - 1
strJoinType = vbNullString
'there might be some unused sets, so don't bother
'going through the disabled controls
If Me("cbxFld" & i).Enabled Then
'The Or/And set starts with the second
combo/textbox set
'so if there's only one criteria specified, don't
need to
'concatenate additional stuff.
If i > 0 Then
If Me("opgClauseType" & i) = 1 Then
strJoinType = " OR "
Else
strJoinType = " AND "
End If
End If

'Work out field type
'Get the a reference to the field in the
table/Query as
'we'll need it for BuildCriteria later on
If cstrSearchQuery = "Table" Then
Set tdf = db.TableDefs(cstrSearchQuery)
Set fld = tdf.Fields(Me("cbxFld" & i))
Else
Set rsQdf = db.OpenRecordset( _
"Select * from [" & cstrSearchQuery & "]
Where 1=2", dbOpenSnapshot)
Set fld = rsQdf.Fields(Me("cbxFld" & i))
End If

'Only build a criteria if something's typed in
the textbox
'otherwise assume all records
If Not IsNull(Me("txtVal" & i)) Then
strWhere = strWhere & strJoinType &
Application.BuildCriteria( _
"[" & Me("cbxFld" & i) & "]", _
fld.Type, Me("cboOperator" & i) & Me("txtVal" & i) & "")
Else
strWhere = strWhere & strJoinType & "[" & Me
("cbxFld" & i) & "] like '*'"
End If
End If
Next
Me.txtSQLWhere = strWhere
'The final all important SQL statement

strSQL = strSQL & " from [" & cstrSearchQuery & "]
Where " & strWhere

Me.txtSQL = strSQL


ExitHere:
Set rsQdf = Nothing
Set rs = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
Select Case Err.Number
'we're trying to open a parameter query
Case 3061:
MsgBox "The query you've selected " _
& " is a Parameter Query." & vbCrLf &
Err.Description, vbExclamation + vbOKOnly, _
"Missing parameters"
Case Else:
'Either invalid SQL or some other error
End Select

Resume ExitHere

End Sub

 

[Kevin K. Sullivan]

Evidently, BuildCriteria does some funny stuff with the word AND or an
ampersand. For example,

? BuildCriteria("MyFieldToSearch", dbText, "Atlantic And Pacific")
MyFieldToSearch="Atlantic" And MyFieldToSearch="Pacific"

? BuildCriteria("MyFieldToSearch", dbText, "A & P")
MyFieldToSearch="A" & "P"

The first one makes some sense, but it won't work out in your routine of
combining criteria. The second example doesn't make any sense to me.
Another problem that may crop up is that BuildCriteria doesn't do anything
to double quotes in the expression. They must be doubled up to form proper
SQL.

Why don't you use a replacement function for BuildCriteria that avoids its
clumsiness on text fields:
-----------------
Public Function myBuildCriteria(strField As String, intType As Integer,
strValue As String) As String
'Wrapper function for Application.BuildCriteria to avoid 'and' , '&', and
"*"*" cases
Select Case intType
Case dbText, dbMemo
'Double up any internal " characters and surround value with double
quotes
myBuildCriteria = strField & " = " & Chr(34) & Replace(strValue,
Chr(34), Chr(34) & Chr(34)) & Chr(34)
Exit Function
Case Else
'not a text comparison, use built in function
myBuildCriteria = Application.BuildCriteria(strField, intType, strValue)
End Select
End Function
-----------------
Paste this code into a new module and replace calls to
Application.BuildCriteria with myBuildCriteria.

HTH,

Kevin

Hello Group,

I am using a modified search form originally designed by
Dev Ashish & Terry Kreft. I have come across a problem
with the function 'Application.BuildCriteria', which I am
unable to solve and I was looking for expert advice.

The search form itself works well but fails under
conditions when searches are performed using reserved SQL
statements such as 'and' and '&'. My staff use the search
form daily and is vital in returning records of companies
such as - Seashore & Harbour Fisheries Ltd. Quite a lot
of my staff are not very knowledgeable with SQL etc and
much prefer to use the front end search form, which does
most of the work.

I have already done a lot of research in finding a
solution and I know it lies in adding delimiters around
entered criteria but I am unable to code and work it all
out. Would love some help with this as I can see myself
working for hours/days on this one and will probably get
nowhere.

Here is the code that the search form uses to build the
SQL statement, maybe of use.

Regards

Tony


Sub sBuildSQL()
' Take what's currently selected on the form
' and create a dynamic SQL statement for the
' lstResults listbox.
'

On Error GoTo ErrHandler 'Pick up and handle errors
Dim strSQL As String
Dim strWhere As String
Dim strJoinType As String
Dim strTable As String
Dim i As Integer
Dim j As Integer
Dim db As Database
Dim rs As Recordset
Dim tdf As TableDef
Dim qdf As QueryDef
Dim rsQdf As Recordset
Dim fld As Field
Const conMAXCONTROLS = 5

Set db = DBEngine(0)(0)
strSQL = "Select * "
'Right now we have five combo/textbox sets
'so set up the master loop to go through these controls
For i = 0 To conMAXCONTROLS - 1
strJoinType = vbNullString
'there might be some unused sets, so don't bother
'going through the disabled controls
If Me("cbxFld" & i).Enabled Then
'The Or/And set starts with the second
combo/textbox set
'so if there's only one criteria specified, don't
need to
'concatenate additional stuff.
If i > 0 Then
If Me("opgClauseType" & i) = 1 Then
strJoinType = " OR "
Else
strJoinType = " AND "
End If
End If

'Work out field type
'Get the a reference to the field in the
table/Query as
'we'll need it for BuildCriteria later on
If cstrSearchQuery = "Table" Then
Set tdf = db.TableDefs(cstrSearchQuery)
Set fld = tdf.Fields(Me("cbxFld" & i))
Else
Set rsQdf = db.OpenRecordset( _
"Select * from [" & cstrSearchQuery & "]
Where 1=2", dbOpenSnapshot)
Set fld = rsQdf.Fields(Me("cbxFld" & i))
End If

'Only build a criteria if something's typed in
the textbox
'otherwise assume all records
If Not IsNull(Me("txtVal" & i)) Then
strWhere = strWhere & strJoinType &
Application.BuildCriteria( _
"[" & Me("cbxFld" & i) & "]", _
fld.Type, Me("cboOperator" & i) & Me("txtVal" & i) & "")
Else
strWhere = strWhere & strJoinType & "[" & Me
("cbxFld" & i) & "] like '*'"
End If
End If
Next
Me.txtSQLWhere = strWhere
'The final all important SQL statement

strSQL = strSQL & " from [" & cstrSearchQuery & "]
Where " & strWhere

Me.txtSQL = strSQL


ExitHere:
Set rsQdf = Nothing
Set rs = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
Select Case Err.Number
'we're trying to open a parameter query
Case 3061:
MsgBox "The query you've selected " _
& " is a Parameter Query." & vbCrLf &
Err.Description, vbExclamation + vbOKOnly, _
"Missing parameters"
Case Else:
'Either invalid SQL or some other error
End Select

Resume ExitHere

End Sub

 

[Tony Scullion]

Kevin,

Thank you for replying with a very impressive solution to
a problem that I thought would take ages to solve. It is
almost working perfect - just a minor issue with
operators :-(

I have implemented your code but it has caused a slight
problem as it includes my operator in the Where SQL
string. Let me explain, for example, if I want to search
for records that match Seashore & Harbour Fisheries Ltd, I
would first of all select field called Company from a
combo box called cbxFld, I would then select the
operator '=' from a combo box called cboOperator and then
I would select criteria 'Seashore & Harbour Fisheries Ltd'
from another combo box called txtVal. The Where string of
the SQL should return Select * from [FarmersAndMeasures]
Where [Company] = "Seashore & Harbour Fisheries Ltd" but
instead it returns Select * from [FarmersAndMeasures]
Where [Company] = "=Seashore & Harbour Fisheries Ltd".

I have tried fixing it myself but I am limiting myself to
trial and error and am, once again, getting nowhere. I
would really appreciate your expertise again.

Best Regards

Tony

-----Original Message-----
Evidently, BuildCriteria does some funny stuff with the word AND or an
ampersand. For example,

? BuildCriteria("MyFieldToSearch", dbText, "Atlantic And Pacific")
MyFieldToSearch="Atlantic" And MyFieldToSearch="Pacific"

? BuildCriteria("MyFieldToSearch", dbText, "A & P")
MyFieldToSearch="A" & "P"

The first one makes some sense, but it won't work out in your routine of
combining criteria. The second example doesn't make any sense to me.
Another problem that may crop up is that BuildCriteria doesn't do anything
to double quotes in the expression. They must be doubled up to form proper
SQL.

Why don't you use a replacement function for BuildCriteria that avoids its
clumsiness on text fields:
-----------------
Public Function myBuildCriteria(strField As String, intType As Integer,
strValue As String) As String
'Wrapper function for Application.BuildCriteria to avoid 'and' , '&', and
"*"*" cases
Select Case intType
Case dbText, dbMemo
'Double up any internal " characters and surround value with double
quotes
myBuildCriteria = strField & " = " & Chr(34) & Replace(strValue,
Chr(34), Chr(34) & Chr(34)) & Chr(34)
Exit Function
Case Else
'not a text comparison, use built in function
myBuildCriteria = Application.BuildCriteria(strField, intType, strValue)
End Select
End Function
-----------------
Paste this code into a new module and replace calls to
Application.BuildCriteria with myBuildCriteria.

HTH,

Kevin

Hello Group,

I am using a modified search form originally designed by
Dev Ashish & Terry Kreft. I have come across a problem
with the function 'Application.BuildCriteria', which I am
unable to solve and I was looking for expert advice.

The search form itself works well but fails under
conditions when searches are performed using reserved SQL
statements such as 'and' and '&'. My staff use the search
form daily and is vital in returning records of companies
such as - Seashore & Harbour Fisheries Ltd. Quite a lot
of my staff are not very knowledgeable with SQL etc and
much prefer to use the front end search form, which does
most of the work.

I have already done a lot of research in finding a
solution and I know it lies in adding delimiters around
entered criteria but I am unable to code and work it all
out. Would love some help with this as I can see myself
working for hours/days on this one and will probably get
nowhere.

Here is the code that the search form uses to build the
SQL statement, maybe of use.

Regards

Tony


Sub sBuildSQL()
' Take what's currently selected on the form
' and create a dynamic SQL statement for the
' lstResults listbox.
'

On Error GoTo ErrHandler 'Pick up and handle errors
Dim strSQL As String
Dim strWhere As String
Dim strJoinType As String
Dim strTable As String
Dim i As Integer
Dim j As Integer
Dim db As Database
Dim rs As Recordset
Dim tdf As TableDef
Dim qdf As QueryDef
Dim rsQdf As Recordset
Dim fld As Field
Const conMAXCONTROLS = 5

Set db = DBEngine(0)(0)
strSQL = "Select * "
'Right now we have five combo/textbox sets
'so set up the master loop to go through these controls
For i = 0 To conMAXCONTROLS - 1
strJoinType = vbNullString
'there might be some unused sets, so don't bother
'going through the disabled controls
If Me("cbxFld" & i).Enabled Then
'The Or/And set starts with the second
combo/textbox set
'so if there's only one criteria specified, don't
need to
'concatenate additional stuff.
If i > 0 Then
If Me("opgClauseType" & i) = 1 Then
strJoinType = " OR "
Else
strJoinType = " AND "
End If
End If

'Work out field type
'Get the a reference to the field in the
table/Query as
'we'll need it for BuildCriteria later on
If cstrSearchQuery = "Table" Then
Set tdf = db.TableDefs(cstrSearchQuery)
Set fld = tdf.Fields(Me("cbxFld" & i))
Else
Set rsQdf = db.OpenRecordset( _
"Select * from [" & cstrSearchQuery & "]
Where 1=2", dbOpenSnapshot)
Set fld = rsQdf.Fields(Me("cbxFld" & i))
End If

'Only build a criteria if something's typed in
the textbox
'otherwise assume all records
If Not IsNull(Me("txtVal" & i)) Then
strWhere = strWhere & strJoinType &
Application.BuildCriteria( _
"[" & Me("cbxFld" & i) & "]", _
fld.Type, Me("cboOperator" & i) & Me("txtVal" & i) & "")
Else
strWhere = strWhere & strJoinType & "[" & Me
("cbxFld" & i) & "] like '*'"
End If
End If
Next
Me.txtSQLWhere = strWhere
'The final all important SQL statement

strSQL = strSQL & " from [" & cstrSearchQuery & "]
Where " & strWhere

Me.txtSQL = strSQL


ExitHere:
Set rsQdf = Nothing
Set rs = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
Select Case Err.Number
'we're trying to open a parameter query
Case 3061:
MsgBox "The query you've selected " _
& " is a Parameter Query." & vbCrLf &
Err.Description, vbExclamation + vbOKOnly, _
"Missing parameters"
Case Else:
'Either invalid SQL or some other error
End Select

Resume ExitHere

End Sub

.

 

[Kevin K. Sullivan]

Tony,
I missed out on how your base code was using the operator. Replace the
custom function with this definition.

-----------------
Public Function myBuildCriteria(strField As String, intType As Integer,
strValue As String) As String
'Wrapper function for Application.BuildCriteria to avoid 'and' , '&', and
'"' cases
'strValue should contain an operator and a field

Select Case intType
Case dbText, dbMemo
'Double up any internal " characters and surround value with double
quotes
myBuildCriteria = strField & Chr(34) & Replace(strValue, Chr(34),
Chr(34) & Chr(34)) & Chr(34)
Exit Function
Case Else
'not a text comparison, use built in function
myBuildCriteria = Application.BuildCriteria(strField, intType, strValue)
End Select
End Function
-----------------


HTH,

Kevin

Kevin,

Thank you for replying with a very impressive solution to
a problem that I thought would take ages to solve. It is
almost working perfect - just a minor issue with
operators :-(

I have implemented your code but it has caused a slight
problem as it includes my operator in the Where SQL
string. Let me explain, for example, if I want to search
for records that match Seashore & Harbour Fisheries Ltd, I
would first of all select field called Company from a
combo box called cbxFld, I would then select the
operator '=' from a combo box called cboOperator and then
I would select criteria 'Seashore & Harbour Fisheries Ltd'
from another combo box called txtVal. The Where string of
the SQL should return Select * from [FarmersAndMeasures]
Where [Company] = "Seashore & Harbour Fisheries Ltd" but
instead it returns Select * from [FarmersAndMeasures]
Where [Company] = "=Seashore & Harbour Fisheries Ltd".

I have tried fixing it myself but I am limiting myself to
trial and error and am, once again, getting nowhere. I
would really appreciate your expertise again.

Best Regards

Tony

-----Original Message-----
Evidently, BuildCriteria does some funny stuff with the word AND or an
ampersand. For example,

? BuildCriteria("MyFieldToSearch", dbText, "Atlantic And Pacific")
MyFieldToSearch="Atlantic" And MyFieldToSearch="Pacific"

? BuildCriteria("MyFieldToSearch", dbText, "A & P")
MyFieldToSearch="A" & "P"

The first one makes some sense, but it won't work out in your routine of
combining criteria. The second example doesn't make any sense to me.
Another problem that may crop up is that BuildCriteria doesn't do anything
to double quotes in the expression. They must be doubled up to form proper
SQL.

Why don't you use a replacement function for BuildCriteria that avoids its
clumsiness on text fields:
-----------------
Public Function myBuildCriteria(strField As String, intType As Integer,
strValue As String) As String
'Wrapper function for Application.BuildCriteria to avoid 'and' , '&', and
"*"*" cases
Select Case intType
Case dbText, dbMemo
'Double up any internal " characters and surround value with double
quotes
myBuildCriteria = strField & " = " & Chr(34) & Replace(strValue,
Chr(34), Chr(34) & Chr(34)) & Chr(34)
Exit Function
Case Else
'not a text comparison, use built in function
myBuildCriteria = Application.BuildCriteria(strField, intType, strValue)
End Select
End Function
-----------------
Paste this code into a new module and replace calls to
Application.BuildCriteria with myBuildCriteria.

HTH,

Kevin

Hello Group,

I am using a modified search form originally designed by
Dev Ashish & Terry Kreft. I have come across a problem
with the function 'Application.BuildCriteria', which I am
unable to solve and I was looking for expert advice.

The search form itself works well but fails under
conditions when searches are performed using reserved SQL
statements such as 'and' and '&'. My staff use the search
form daily and is vital in returning records of companies
such as - Seashore & Harbour Fisheries Ltd. Quite a lot
of my staff are not very knowledgeable with SQL etc and
much prefer to use the front end search form, which does
most of the work.

I have already done a lot of research in finding a
solution and I know it lies in adding delimiters around
entered criteria but I am unable to code and work it all
out. Would love some help with this as I can see myself
working for hours/days on this one and will probably get
nowhere.

Here is the code that the search form uses to build the
SQL statement, maybe of use.

Regards

Tony


Sub sBuildSQL()
' Take what's currently selected on the form
' and create a dynamic SQL statement for the
' lstResults listbox.
'

On Error GoTo ErrHandler 'Pick up and handle errors
Dim strSQL As String
Dim strWhere As String
Dim strJoinType As String
Dim strTable As String
Dim i As Integer
Dim j As Integer
Dim db As Database
Dim rs As Recordset
Dim tdf As TableDef
Dim qdf As QueryDef
Dim rsQdf As Recordset
Dim fld As Field
Const conMAXCONTROLS = 5

Set db = DBEngine(0)(0)
strSQL = "Select * "
'Right now we have five combo/textbox sets
'so set up the master loop to go through these controls
For i = 0 To conMAXCONTROLS - 1
strJoinType = vbNullString
'there might be some unused sets, so don't bother
'going through the disabled controls
If Me("cbxFld" & i).Enabled Then
'The Or/And set starts with the second
combo/textbox set
'so if there's only one criteria specified, don't
need to
'concatenate additional stuff.
If i > 0 Then
If Me("opgClauseType" & i) = 1 Then
strJoinType = " OR "
Else
strJoinType = " AND "
End If
End If

'Work out field type
'Get the a reference to the field in the
table/Query as
'we'll need it for BuildCriteria later on
If cstrSearchQuery = "Table" Then
Set tdf = db.TableDefs(cstrSearchQuery)
Set fld = tdf.Fields(Me("cbxFld" & i))
Else
Set rsQdf = db.OpenRecordset( _
"Select * from [" & cstrSearchQuery & "]
Where 1=2", dbOpenSnapshot)
Set fld = rsQdf.Fields(Me("cbxFld" & i))
End If

'Only build a criteria if something's typed in
the textbox
'otherwise assume all records
If Not IsNull(Me("txtVal" & i)) Then
strWhere = strWhere & strJoinType &
Application.BuildCriteria( _
"[" & Me("cbxFld" & i) & "]", _
fld.Type, Me("cboOperator" & i) & Me("txtVal" & i) & "")
Else
strWhere = strWhere & strJoinType & "[" & Me
("cbxFld" & i) & "] like '*'"
End If
End If
Next
Me.txtSQLWhere = strWhere
'The final all important SQL statement

strSQL = strSQL & " from [" & cstrSearchQuery & "]
Where " & strWhere

Me.txtSQL = strSQL


ExitHere:
Set rsQdf = Nothing
Set rs = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
Select Case Err.Number
'we're trying to open a parameter query
Case 3061:
MsgBox "The query you've selected " _
& " is a Parameter Query." & vbCrLf &
Err.Description, vbExclamation + vbOKOnly, _
"Missing parameters"
Case Else:
'Either invalid SQL or some other error
End Select

Resume ExitHere

End Sub

.

 

[Kevin K. Sullivan]

Sorry again! This should work for =,<,>,<>,<=,>=
-------------------

Public Function myBuildCriteria(strField As String, intType As Integer,
strValue As String) As String
'Wrapper function for Application.BuildCriteria to avoid 'and' , '&', and
'"' cases
'strValue should contain an operator and a field

Select Case intType
Case dbText, dbMemo
'Double up any internal " characters and surround value with double
quotes
Select Case Left(strValue, 2)
Case "<>", "<=", ">=" ' two character operator
myBuildCriteria = strField & Left(strValue, 2) & Chr(34) &
Replace(Mid(strValue, 3), Chr(34), Chr(34) & Chr(34)) & Chr(34)
Case Else ' single character operator
myBuildCriteria = strField & Chr(34) & Left(strValue, 1) &
Replace(Mid(strValue, 2), Chr(34), Chr(34) & Chr(34)) & Chr(34)
End Select
Exit Function
Case Else
'not a text comparison, use built in function
myBuildCriteria = Application.BuildCriteria(strField, intType, strValue)
End Select
End Function
----------------


HTH,

Kevin

Tony,
I missed out on how your base code was using the operator. Replace the
custom function with this definition.

-----------------
Public Function myBuildCriteria(strField As String, intType As Integer,
strValue As String) As String
'Wrapper function for Application.BuildCriteria to avoid 'and' , '&', and
'"' cases
'strValue should contain an operator and a field

Select Case intType
Case dbText, dbMemo
'Double up any internal " characters and surround value with double
quotes
myBuildCriteria = strField & Chr(34) & Replace(strValue, Chr(34),
Chr(34) & Chr(34)) & Chr(34)
Exit Function
Case Else
'not a text comparison, use built in function
myBuildCriteria = Application.BuildCriteria(strField, intType, strValue)
End Select
End Function
-----------------


HTH,

Kevin

Kevin,

Thank you for replying with a very impressive solution to
a problem that I thought would take ages to solve. It is
almost working perfect - just a minor issue with
operators :-(

I have implemented your code but it has caused a slight
problem as it includes my operator in the Where SQL
string. Let me explain, for example, if I want to search
for records that match Seashore & Harbour Fisheries Ltd, I
would first of all select field called Company from a
combo box called cbxFld, I would then select the
operator '=' from a combo box called cboOperator and then
I would select criteria 'Seashore & Harbour Fisheries Ltd'
from another combo box called txtVal. The Where string of
the SQL should return Select * from [FarmersAndMeasures]
Where [Company] = "Seashore & Harbour Fisheries Ltd" but
instead it returns Select * from [FarmersAndMeasures]
Where [Company] = "=Seashore & Harbour Fisheries Ltd".

I have tried fixing it myself but I am limiting myself to
trial and error and am, once again, getting nowhere. I
would really appreciate your expertise again.

Best Regards

Tony

-----Original Message-----
Evidently, BuildCriteria does some funny stuff with the word AND or an
ampersand. For example,

? BuildCriteria("MyFieldToSearch", dbText, "Atlantic And Pacific")
MyFieldToSearch="Atlantic" And MyFieldToSearch="Pacific"

? BuildCriteria("MyFieldToSearch", dbText, "A & P")
MyFieldToSearch="A" & "P"

The first one makes some sense, but it won't work out in your routine of
combining criteria. The second example doesn't make any sense to me.
Another problem that may crop up is that BuildCriteria doesn't do anything
to double quotes in the expression. They must be doubled up to form proper
SQL.

Why don't you use a replacement function for BuildCriteria that avoids its
clumsiness on text fields:
-----------------
Public Function myBuildCriteria(strField As String, intType As Integer,
strValue As String) As String
'Wrapper function for Application.BuildCriteria to avoid 'and' , '&', and
"*"*" cases
Select Case intType
Case dbText, dbMemo
'Double up any internal " characters and surround value with double
quotes
myBuildCriteria = strField & " = " & Chr(34) & Replace(strValue,
Chr(34), Chr(34) & Chr(34)) & Chr(34)
Exit Function
Case Else
'not a text comparison, use built in function
myBuildCriteria = Application.BuildCriteria(strField, intType, strValue)
End Select
End Function
-----------------
Paste this code into a new module and replace calls to
Application.BuildCriteria with myBuildCriteria.

HTH,

Kevin

Hello Group,

I am using a modified search form originally designed by
Dev Ashish & Terry Kreft. I have come across a problem
with the function 'Application.BuildCriteria', which I am
unable to solve and I was looking for expert advice.

The search form itself works well but fails under
conditions when searches are performed using reserved SQL
statements such as 'and' and '&'. My staff use the search
form daily and is vital in returning records of companies
such as - Seashore & Harbour Fisheries Ltd. Quite a lot
of my staff are not very knowledgeable with SQL etc and
much prefer to use the front end search form, which does
most of the work.

I have already done a lot of research in finding a
solution and I know it lies in adding delimiters around
entered criteria but I am unable to code and work it all
out. Would love some help with this as I can see myself
working for hours/days on this one and will probably get
nowhere.

Here is the code that the search form uses to build the
SQL statement, maybe of use.

Regards

Tony


Sub sBuildSQL()
' Take what's currently selected on the form
' and create a dynamic SQL statement for the
' lstResults listbox.
'

On Error GoTo ErrHandler 'Pick up and handle errors
Dim strSQL As String
Dim strWhere As String
Dim strJoinType As String
Dim strTable As String
Dim i As Integer
Dim j As Integer
Dim db As Database
Dim rs As Recordset
Dim tdf As TableDef
Dim qdf As QueryDef
Dim rsQdf As Recordset
Dim fld As Field
Const conMAXCONTROLS = 5

Set db = DBEngine(0)(0)
strSQL = "Select * "
'Right now we have five combo/textbox sets
'so set up the master loop to go through these controls
For i = 0 To conMAXCONTROLS - 1
strJoinType = vbNullString
'there might be some unused sets, so don't bother
'going through the disabled controls
If Me("cbxFld" & i).Enabled Then
'The Or/And set starts with the second
combo/textbox set
'so if there's only one criteria specified, don't
need to
'concatenate additional stuff.
If i > 0 Then
If Me("opgClauseType" & i) = 1 Then
strJoinType = " OR "
Else
strJoinType = " AND "
End If
End If

'Work out field type
'Get the a reference to the field in the
table/Query as
'we'll need it for BuildCriteria later on
If cstrSearchQuery = "Table" Then
Set tdf = db.TableDefs(cstrSearchQuery)
Set fld = tdf.Fields(Me("cbxFld" & i))
Else
Set rsQdf = db.OpenRecordset( _
"Select * from [" & cstrSearchQuery & "]
Where 1=2", dbOpenSnapshot)
Set fld = rsQdf.Fields(Me("cbxFld" & i))
End If

'Only build a criteria if something's typed in
the textbox
'otherwise assume all records
If Not IsNull(Me("txtVal" & i)) Then
strWhere = strWhere & strJoinType &
Application.BuildCriteria( _
"[" & Me("cbxFld" & i) & "]", _
fld.Type, Me("cboOperator" & i) & Me("txtVal" & i) & "")
Else
strWhere = strWhere & strJoinType & "[" & Me
("cbxFld" & i) & "] like '*'"
End If
End If
Next
Me.txtSQLWhere = strWhere
'The final all important SQL statement

strSQL = strSQL & " from [" & cstrSearchQuery & "]
Where " & strWhere

Me.txtSQL = strSQL


ExitHere:
Set rsQdf = Nothing
Set rs = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
Select Case Err.Number
'we're trying to open a parameter query
Case 3061:
MsgBox "The query you've selected " _
& " is a Parameter Query." & vbCrLf &
Err.Description, vbExclamation + vbOKOnly, _
"Missing parameters"
Case Else:
'Either invalid SQL or some other error
End Select

Resume ExitHere

End Sub


.

 

[Tony Scullion]

Kevin,

Your new code only worked for 2 character operators so I
used this technique and amended it slightly to work with
single character operator. The code now looks like this
and I am glad to report is working fine.

Thanks for all your help - you are one smart guy!

All the best

Tony

'Code starts here
Public Function myBuildCriteria(strField As String,
intType As Integer, strValue As String) As String
'Wrapper function for Application.BuildCriteria to
avoid 'and' , '&', and
'"' cases
'strValue should contain an operator and a field

Select Case intType
Case dbText, dbMemo
'Double up any internal " characters and surround
value with double quotes
Select Case Left(strValue, 2)
Case "<>", "<=", ">=" ' two character operator
myBuildCriteria = strField & Left(strValue, 2)
& Chr(34) & Replace(Mid(strValue, 3), Chr(34), Chr(34) &
Chr(34)) & Chr(34)
Case Else ' single character operator
myBuildCriteria = strField & Left(strValue, 1)
& Chr(34) & Replace(Mid(strValue, 2), Chr(34), Chr(34) &
Chr(34)) & Chr(34)
End Select
Exit Function
Case Else
'not a text comparison, use built in function
myBuildCriteria = Application.BuildCriteria(strField,
intType, strValue)
End Select
End Function
'Code ends here

-----Original Message-----
Sorry again! This should work for =,<,>,<>,<=,>=
-------------------

Public Function myBuildCriteria(strField As String, intType As Integer,
strValue As String) As String
'Wrapper function for Application.BuildCriteria to avoid 'and' , '&', and
'"' cases
'strValue should contain an operator and a field

Select Case intType
Case dbText, dbMemo
'Double up any internal " characters and surround value with double
quotes
Select Case Left(strValue, 2)
Case "<>", "<=", ">=" ' two character operator
myBuildCriteria = strField & Left(strValue, 2) & Chr(34) &
Replace(Mid(strValue, 3), Chr(34), Chr(34) & Chr(34)) & Chr(34)
Case Else ' single character operator
myBuildCriteria = strField & Chr(34) & Left (strValue, 1) &
Replace(Mid(strValue, 2), Chr(34), Chr(34) & Chr(34)) & Chr(34)
End Select
Exit Function
Case Else
'not a text comparison, use built in function
myBuildCriteria = Application.BuildCriteria(strField, intType, strValue)
End Select
End Function
----------------


HTH,

Kevin

Tony,
I missed out on how your base code was using the operator. Replace the
custom function with this definition.

-----------------
Public Function myBuildCriteria(strField As String, intType As Integer,
strValue As String) As String
'Wrapper function for Application.BuildCriteria to avoid 'and' , '&', and
'"' cases
'strValue should contain an operator and a field

Select Case intType
Case dbText, dbMemo
'Double up any internal " characters and surround value with double
quotes
myBuildCriteria = strField & Chr(34) & Replace (strValue, Chr(34),
Chr(34) & Chr(34)) & Chr(34)
Exit Function
Case Else
'not a text comparison, use built in function
myBuildCriteria = Application.BuildCriteria

(strField, intType,
strValue)

End Select
End Function
-----------------


HTH,

Kevin

Kevin,

Thank you for replying with a very impressive solution to
a problem that I thought would take ages to solve. It is
almost working perfect - just a minor issue with
operators :-(

I have implemented your code but it has caused a slight
problem as it includes my operator in the Where SQL
string. Let me explain, for example, if I want to search
for records that match Seashore & Harbour Fisheries Ltd, I
would first of all select field called Company from a
combo box called cbxFld, I would then select the
operator '=' from a combo box called cboOperator and then
I would select criteria 'Seashore & Harbour Fisheries Ltd'
from another combo box called txtVal. The Where string of
the SQL should return Select * from [FarmersAndMeasures]
Where [Company] = "Seashore & Harbour Fisheries Ltd" but
instead it returns Select * from [FarmersAndMeasures]
Where [Company] = "=Seashore & Harbour Fisheries Ltd".

I have tried fixing it myself but I am limiting myself to
trial and error and am, once again, getting nowhere. I
would really appreciate your expertise again.

Best Regards

Tony


-----Original Message-----
Evidently, BuildCriteria does some funny stuff with the
word AND or an
ampersand. For example,

? BuildCriteria("MyFieldToSearch", dbText, "Atlantic And
Pacific")
MyFieldToSearch="Atlantic" And MyFieldToSearch="Pacific"

? BuildCriteria("MyFieldToSearch", dbText, "A & P")
MyFieldToSearch="A" & "P"

The first one makes some sense, but it won't work out in
your routine of
combining criteria. The second example doesn't make any
sense to me.
Another problem that may crop up is that BuildCriteria
doesn't do anything
to double quotes in the expression. They must be doubled
up to form proper
SQL.

Why don't you use a replacement function for
BuildCriteria that avoids its
clumsiness on text fields:
-----------------
Public Function myBuildCriteria(strField As String,
intType As Integer,
strValue As String) As String
'Wrapper function for Application.BuildCriteria to
avoid 'and' , '&', and
"*"*" cases
Select Case intType
Case dbText, dbMemo
'Double up any internal " characters and surround
value with double
quotes
myBuildCriteria = strField & " = " & Chr(34) &
Replace(strValue,
Chr(34), Chr(34) & Chr(34)) & Chr(34)
Exit Function
Case Else
'not a text comparison, use built in function
myBuildCriteria = Application.BuildCriteria (strField,
intType, strValue)
End Select
End Function
-----------------
Paste this code into a new module and replace calls to
Application.BuildCriteria with myBuildCriteria.

HTH,

Kevin

"Tony Scullion" <[email protected]>
wrote in message
Hello Group,

I am using a modified search form originally designed by
Dev Ashish & Terry Kreft. I have come across a problem
with the function 'Application.BuildCriteria', which I
am
unable to solve and I was looking for expert advice.

The search form itself works well but fails under
conditions when searches are performed using reserved
SQL
statements such as 'and' and '&'. My staff use the
search
form daily and is vital in returning records of
companies
such as - Seashore & Harbour Fisheries Ltd. Quite a lot
of my staff are not very knowledgeable with SQL etc and
much prefer to use the front end search form, which does
most of the work.

I have already done a lot of research in finding a
solution and I know it lies in adding delimiters around
entered criteria but I am unable to code and work it all
out. Would love some help with this as I can see myself
working for hours/days on this one and will probably get
nowhere.

Here is the code that the search form uses to build the
SQL statement, maybe of use.

Regards

Tony


Sub sBuildSQL()
' Take what's currently selected on the form
' and create a dynamic SQL statement for the
' lstResults listbox.
'

On Error GoTo ErrHandler 'Pick up and handle errors
Dim strSQL As String
Dim strWhere As String
Dim strJoinType As String
Dim strTable As String
Dim i As Integer
Dim j As Integer
Dim db As Database
Dim rs As Recordset
Dim tdf As TableDef
Dim qdf As QueryDef
Dim rsQdf As Recordset
Dim fld As Field
Const conMAXCONTROLS = 5

Set db = DBEngine(0)(0)
strSQL = "Select * "
'Right now we have five combo/textbox sets
'so set up the master loop to go through these
controls
For i = 0 To conMAXCONTROLS - 1
strJoinType = vbNullString
'there might be some unused sets, so don't bother
'going through the disabled controls
If Me("cbxFld" & i).Enabled Then
'The Or/And set starts with the second
combo/textbox set
'so if there's only one criteria specified,
don't
need to
'concatenate additional stuff.
If i > 0 Then
If Me("opgClauseType" & i) = 1 Then
strJoinType = " OR "
Else
strJoinType = " AND "
End If
End If

'Work out field type
'Get the a reference to the field in the
table/Query as
'we'll need it for BuildCriteria later on
If cstrSearchQuery = "Table" Then
Set tdf = db.TableDefs(cstrSearchQuery)
Set fld = tdf.Fields(Me("cbxFld" & i))
Else
Set rsQdf = db.OpenRecordset( _
"Select * from [" & cstrSearchQuery & "]
Where 1=2", dbOpenSnapshot)
Set fld = rsQdf.Fields(Me("cbxFld" & i))
End If

'Only build a criteria if something's typed in
the textbox
'otherwise assume all records
If Not IsNull(Me("txtVal" & i)) Then
strWhere = strWhere & strJoinType &
Application.BuildCriteria( _
"[" & Me("cbxFld" & i) & "]", _
fld.Type, Me("cboOperator" & i) & Me("txtVal" & i)
& "")
Else
strWhere = strWhere & strJoinType & "[" & Me
("cbxFld" & i) & "] like '*'"
End If
End If
Next
Me.txtSQLWhere = strWhere
'The final all important SQL statement

strSQL = strSQL & " from [" & cstrSearchQuery & "]
Where " & strWhere

Me.txtSQL = strSQL


ExitHere:
Set rsQdf = Nothing
Set rs = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
Select Case Err.Number
'we're trying to open a parameter query
Case 3061:
MsgBox "The query you've selected " _
& " is a Parameter Query." & vbCrLf &
Err.Description, vbExclamation + vbOKOnly, _
"Missing parameters"
Case Else:
'Either invalid SQL or some other error
End Select

Resume ExitHere

End Sub


.

.