Search form and SQL problem - minor tweak required

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
 
D

Dan Artuso

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.
 
T

Tony Scullion

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...
 
D

Dan Artuso

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...
 
T

Tony Scullion

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Application.BuildCriteria & search form problems 5
Problem with Search Button and SQL Statement 5
SQL problem 9
Search form problem 1
when a search form opens a result form I'm being asked about aparameter? 1
Access 2007 Search Form 2
strSQL in Search Form 4
Checkboxes in a search form and VBA code 3

Top