SQL Contains query

[ashg657]

Hello all,
I have a few years experience in developing Access MDB applications. I have
now moved on to start looking at Access 2007 and SQL (.ADP)

I have begun by creating an extremely simple knowledgebase database but am
stuck on creating a SQL query to perform a search. Here's what I need to
do...:

I have a single table (tbl-KBItems) and a single search form (frm-SearchKB)
On the search form I have 3 controls. The first one is a textbox which the
user will be able to type any text into.

The second control labelled 'Within' will specify which field this text
should be found in. This is a Value List combobox.

The third control is labelled 'For Product' - again a predefined Value List
combobox.

Once the user has filled out these 3 controls, we have enough information to
search the database (hope you're following me here). So, what we end up with
is any text from control 1, contained within field from control 2 and for
product assigned in control 3 - should be returned in my results panel.

I need a SQL query to perform this. Previously in MDB's I have thrown in a
couple of IIF statements and bang job done nice & easy. However with ADP's
I'm under the impression it's all very different! Any suggestions on how to
achieve this and how to get started?!

Many Thanks.

 

[Dale Fye]

First, Access 2007 does not support ADPs, and "SQL" <> "ADP"

My recommendation would be to write a user defined function (UDF) that will
return your SQL string based on the elements of your controls. This is by
no means complete, but will point you in the right direction:

Private Function fnBuildCriteria(SearchFor as String, _
SearchIn as String, _
ProductID as long) as String

Dim astrSearchFor as string
Dim intLoop as integer
Dim strSearch as String
Dim strSearchIn as String

aSearchFor = Split(SearchFor, " ")
strSearchIn = "[" & SearchIn & "] Like "
for intLoop = lbound(aSearchFor) to Ubound(aSearchFor)
strSearch = strSearch & " AND " & strSearchIn _
& "'*" & aSearchFor(intLoop) & "*'"
Next
'strip off the first " AND "
strSearch = "(" & Mid(strSearch, 6) & ")"
strSearch = strSearch & " AND [ProductID] = " & ProductID

fnBuildCriteria = strSearch

End Function

Then, in a command button on your form, you could write something like:

Private Sub cmdFilter_Click

strSQL = "SELECT * FROM yourTableName " _
& "WHERE " & fnBuildCriteria(me.txtSearchFor, _
me.cboSearchIn, _
me.cboProductID)


'Do something with this SQL

End Sub

 

[NG]

Hi,

I have an adp project running in access 2007, and my solution is to write a
procedure on the SQl server like:

ALTER PROCEDURE p_myProc
@inCrit bigint
AS
SELECT dbo.tblMyTable.myField1, dbo.tblMyTable.myField1, ....
FROM dbo.MyTable
WHERE where poID = @inPO


In the Access code you can then create a command object that executes the
procedure like

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
With cmd
.CommandText = "dbo.p_myProc"
.CommandType = adCmdStoredProc
Set prm = .CreateParameter("inCrit", adBigInt, adParamInput, ,
Me!MyAccessControl)
.Parameters.Append prm
Set prm = .CreateParameter("inStatus", adInteger, adParamInput,
, lngStatus)
.Parameters.Append prm
End With
cmd.Execute

 

[NG]

Correction

With cmd

.CommandText = "dbo.p_myProc"
.CommandType = adCmdStoredProc
Set prm = .CreateParameter("inCrit", adBigInt, adParamInput, ,
Me!MyAccessControl)
.Parameters.Append prm

'-- no second par required for example (copy/paste is never a good idea)