Search form and SQL problem - minor tweak required

  • Thread starter Thread starter Tony Scullion
  • Start date Start date
T

Tony Scullion

Hi Group,

I have built an advanced search form and need help with
the following.

My search form allows an user to select a field from
combo 'cbxFld', select an operator from
combo 'cboOperator' and to enter text using a text box
called 'txtVal'. I wrote some VB code to 'join' this
search together to make a Where statement, which is used
to filter records on a subform.

It works fine when I perform searches using numbers but
when I try to perform a search using a text entry it
throws up 2 grey paramater boxes asking for info!? I know
the problem is a parenthesis problem but after hours of
trying I can't work it out. Can someone help me to get
the SQL to work all searches (text, numbers & like etc)?

Manu thanks for your expertise

Tony

PS here is the VB code used to build the SQL

strTable = "Part"
strSQL = "SELECT Part.ProductID, Part.PartID,
Part.PartName, Part.Price, Part.Date "

If Not IsNull(Me("txtVal" & i)) Then 'Peform
search if textbox is not blank
strWhere = strWhere & strJoinType & "[" & Me
("cbxFld" & i) & "]" & Me("cboOperator" & i) & Me("txtVal"
& i)
Else
strWhere = " FROM " & strTable & ""
End If

strSQL = strSQL & " FROM " & strTable & " Where " &
strWhere

'*** change the recordsource of the subform
Me.PartSubformNew.Form.RecordSource = strSQL
Me.PartSubformNew.Form.Requery
 
Hi,
Text criteria must be delimited with single quotes,
dates with #. It works for numbers because they don't
require a delimiter.
You'll have to put some logic in your code to determine
what delimiter you need.
 
Dan,

Thank you for your reply - I know what you mean by adding
logic to work out the delimiter but I don't know how to do
it.

The code I developed was part-coded and part-downloaded
from these Newsgroups and I've very little experience of
coding 'raw-code' from scratch. Can you help?

Thanks and appreciated

Tony
-----Original Message-----
Hi,
Text criteria must be delimited with single quotes,
dates with #. It works for numbers because they don't
require a delimiter.
You'll have to put some logic in your code to determine
what delimiter you need.

--
HTH
Dan Artuso, Access MVP


"Tony Scullion" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
Hi Group,

I have built an advanced search form and need help with
the following.

My search form allows an user to select a field from
combo 'cbxFld', select an operator from
combo 'cboOperator' and to enter text using a text box
called 'txtVal'. I wrote some VB code to 'join' this
search together to make a Where statement, which is used
to filter records on a subform.

It works fine when I perform searches using numbers but
when I try to perform a search using a text entry it
throws up 2 grey paramater boxes asking for info!? I know
the problem is a parenthesis problem but after hours of
trying I can't work it out. Can someone help me to get
the SQL to work all searches (text, numbers & like etc)?

Manu thanks for your expertise

Tony

PS here is the VB code used to build the SQL

strTable = "Part"
strSQL = "SELECT Part.ProductID, Part.PartID,
Part.PartName, Part.Price, Part.Date "

If Not IsNull(Me("txtVal" & i)) Then 'Peform
search if textbox is not blank
strWhere = strWhere & strJoinType & "[" & Me
("cbxFld" & i) & "]" & Me("cboOperator" & i) & Me ("txtVal"
& i)
Else
strWhere = " FROM " & strTable & ""
End If

strSQL = strSQL & " FROM " & strTable & " Where " &
strWhere

'*** change the recordsource of the subform
Me.PartSubformNew.Form.RecordSource = strSQL
Me.PartSubformNew.Form.Requery
Click to expand...


.
Click to expand...
 
Hi Tony,
Something like this should work:

If IsDate(Me("txtVal" & i)) = True Then
'delimit accordingly
ElseIf IsNumeric(Me("txtVal" & i)) = True Then

'delimit accordingly
Else
'delimit accordingly
End If

--
HTH
Dan Artuso, Access MVP


Tony Scullion said:
Dan,

Thank you for your reply - I know what you mean by adding
logic to work out the delimiter but I don't know how to do
it.

The code I developed was part-coded and part-downloaded
from these Newsgroups and I've very little experience of
coding 'raw-code' from scratch. Can you help?

Thanks and appreciated

Tony
-----Original Message-----
Hi,
Text criteria must be delimited with single quotes,
dates with #. It works for numbers because they don't
require a delimiter.
You'll have to put some logic in your code to determine
what delimiter you need.

--
HTH
Dan Artuso, Access MVP


"Tony Scullion" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
Hi Group,

I have built an advanced search form and need help with
the following.

My search form allows an user to select a field from
combo 'cbxFld', select an operator from
combo 'cboOperator' and to enter text using a text box
called 'txtVal'. I wrote some VB code to 'join' this
search together to make a Where statement, which is used
to filter records on a subform.

It works fine when I perform searches using numbers but
when I try to perform a search using a text entry it
throws up 2 grey paramater boxes asking for info!? I know
the problem is a parenthesis problem but after hours of
trying I can't work it out. Can someone help me to get
the SQL to work all searches (text, numbers & like etc)?

Manu thanks for your expertise

Tony

PS here is the VB code used to build the SQL

strTable = "Part"
strSQL = "SELECT Part.ProductID, Part.PartID,
Part.PartName, Part.Price, Part.Date "

If Not IsNull(Me("txtVal" & i)) Then 'Peform
search if textbox is not blank
strWhere = strWhere & strJoinType & "[" & Me
("cbxFld" & i) & "]" & Me("cboOperator" & i) & Me ("txtVal"
& i)
Else
strWhere = " FROM " & strTable & ""
End If

strSQL = strSQL & " FROM " & strTable & " Where " &
strWhere

'*** change the recordsource of the subform
Me.PartSubformNew.Form.RecordSource = strSQL
Me.PartSubformNew.Form.Requery
Click to expand...


.
Click to expand...
Click to expand...
 
Dan,

Thanks for your help. Done a bit of research and came up
with the following working method, which works out the
field type and adds a default delimiter. I'm posting the
solution here for other users.


'Work out field type
Dim sDelim As String

Select Case CurrentDb.TableDefs("Part").Fields(Me("cbxFld"
& i)).Type
Case dbText, dbMemo
sDelim = """"
Case dbDate, dbTime
sDelim = "#"
Case Else
sDelim = ""
End Select

'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 & "[" & Me
("cbxFld" & i) & "]" & Me("cboOperator" & i) & sDelim & Me
("txtVal" & i) & sDelim
Else
strWhere = ""
End If

-----Original Message-----
Hi Tony,
Something like this should work:

If IsDate(Me("txtVal" & i)) = True Then
'delimit accordingly
ElseIf IsNumeric(Me("txtVal" & i)) = True Then

'delimit accordingly
Else
'delimit accordingly
End If

--
HTH
Dan Artuso, Access MVP


"Tony Scullion" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
Dan,

Thank you for your reply - I know what you mean by adding
logic to work out the delimiter but I don't know how to do
it.

The code I developed was part-coded and part-downloaded
from these Newsgroups and I've very little experience of
coding 'raw-code' from scratch. Can you help?

Thanks and appreciated

Tony
-----Original Message-----
Hi,
Text criteria must be delimited with single quotes,
dates with #. It works for numbers because they don't
require a delimiter.
You'll have to put some logic in your code to determine
what delimiter you need.

--
HTH
Dan Artuso, Access MVP


"Tony Scullion" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
Hi Group,

I have built an advanced search form and need help with
the following.

My search form allows an user to select a field from
combo 'cbxFld', select an operator from
combo 'cboOperator' and to enter text using a text box
called 'txtVal'. I wrote some VB code to 'join' this
search together to make a Where statement, which is used
to filter records on a subform.

It works fine when I perform searches using numbers but
when I try to perform a search using a text entry it
throws up 2 grey paramater boxes asking for info!? I know
the problem is a parenthesis problem but after hours of
trying I can't work it out. Can someone help me to get
the SQL to work all searches (text, numbers & like etc)?

Manu thanks for your expertise

Tony

PS here is the VB code used to build the SQL

strTable = "Part"
strSQL = "SELECT Part.ProductID, Part.PartID,
Part.PartName, Part.Price, Part.Date "

If Not IsNull(Me("txtVal" & i)) Then 'Peform
search if textbox is not blank
strWhere = strWhere & strJoinType & "[" & Me
("cbxFld" & i) & "]" & Me("cboOperator" & i) & Me ("txtVal"
& i)
Else
strWhere = " FROM " & strTable & ""
End If

strSQL = strSQL & " FROM " & strTable & " Where " &
strWhere

'*** change the recordsource of the subform
Me.PartSubformNew.Form.RecordSource = strSQL
Me.PartSubformNew.Form.Requery


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top