Multi-field search: SQL string formatting issues?

[thesaurusrex]

I'm trying to implement multi-field search of a single table. Here is what I have now:

Private Sub cmdSearch_Click()
    Dim strFind As String, strWhere As String
    If Len(Me.entryidsearch & vbNullString) > 0 Then
        strWhere = strWhere & "tblEntry.entryid=" & Me.entryidsearch
    End If
    If Len(Me.divisionidsearch & vbNullString) > 0 Then
        strWhere = strWhere & "tblEntry.divisionid=" & Me.divisionidsearch
    End If
    If Len(Me.constituencyidsearch & vbNullString) > 0 Then
        strWhere = strWhere & "tblentry.constituencyid=" & Me.constituencyidsearch
    End If
    If Len(Me.statusidsearch & vbNullString) > 0 Then
        strWhere = strWhere & "tblentry.statusid=" & Me.statusidsearch
    End If
    If Len(Me.summarysearch & vbNullString) > 0 Then
        strWhere = strWhere & "tblEntry.summary LIKE '%" & Me.summarysearch & "%' AND "
    End If
    If Len(Me.keywordsearch & vbNullString) > 0 Then
        strWhere = strWhere & "tblentry.keywordflag = true"
    End If
    If Len(Me.facultysearch & vbNullString) > 0 Then
        strWhere = strWhere & "tblentry.facultyflag = true"
    End If
    If Len(strWhere) > 0 Then
        strWhere = " WHERE " & Left(strWhere, Len(strWhere) - 8)
        strFind = "SELECT tblEntry.entryid, tblentry.divisionid, tblentry.constituencyid, tblentry.statusid, " & _
            "tblentry.dateopened, tblentry.notes, tblentry.action, tblentry.summary " & _
            "FROM tblentry " & strWhere & ";"
        Dim stdocname As String
        stdocname = "frmdocumentation"
        DoCmd.OpenForm stdocname, , , strFind
    Else
        MsgBox "No matching records found"
    End If
End Sub

I get an error (run-time 3075) every time I run this. The error is:

Syntax error. in query expression 'SELECT tblEntry.entryid,
tblentry.divisionid, tblentry.constituencyid, tblentry.statusid,
tblentry.dateopened, tblentry.notes, tblentry.action,
tblentry.summary FROM tblentry  WHERE *(see below)

and following the WHERE it says tblentry.contitu;'. for example if I have chosen the constituencyid for searching. Or tblentry.st;' if I chose statusid.....it's always the last six characters cut off. If I enter data into both constituencyidsearch (in this case 4) and statusidsearch, I get the following after WHERE: tblentry.constituencyid=4tblentry.st;'.

I've looked at other examples of this type of search and can't figure out what is causing my syntax issues. Any help would be appreciated.

 

[Bat17]

Without working it all ut I think that
strWhere = strWhere & "tblEntry.divisionid=" & Me.divisionidsearch
should be
strWhere = strWhere & "tblEntry.divisionid=" & Me.divisionidsearch & " AND "

same for all clauses in the where string

then chop the spare AND off with Left(strWhere, Len(strWhere) - 5)

depending on the search though you may want to use OR's rather than AND's

HTH

Peter

 

[thesaurusrex]

Oops, completely looked over that and forgot to change it when I added in more fields. Thanks for pointing that out. I am still getting the syntax error, though the string looks correct.

Syntax error. in query expression 'SELECT tblEntry.entryid,
tblentry.divisionid, tblentry.constituencyid, tblentry.statusid,
tblentry.dateopened, tblentry.notes, tblentry.action,
tblentry.summary FROM tblentry WHERE tblentry.constituencyid=4 AND tblentry.statusid=2;'

I think it's somewhere in my docmd.openform statement. I'll have to look at it some more.

 

[Bat17]

Your "where clause" shoud just be the where clause from the SQL without the word "Where". See Help

HTH

Peter

 

[thesaurusrex]

Much thanks again. The string seems to be accepted now. So I'm on to figuring out what I need to do to get this form to open to the selected records.

 

[thesaurusrex]

I got it opening up to the correct record now

If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere) - 5)
Dim stdocname As String
stdocname = "frmEntry"
DoCmd.OpenForm stdocname, , , strWhere
Else
MsgBox "No entries found."
End If

Problems left: making msgbox open when 0 records match.....right now it opens to a new record when none are found and the msgbox only pops up when no criteria is entered. And summary search doesn't work....I think I just need some wildcards in there.

 

[Bat17]

before trying to open the form you need to use your SQL string to create a recordset so that you can check how many records are returned. if none then skip form.

Possibly slower, but eaiser, is to use dcount with the strWhere created for the form. You will need to look up the details in help

HTH

Peter

 

[thesaurusrex]

Thanks Bat, you've been a huge help. Here's what I ended up implementing:

    If Len(strWhere) > 0 Then
    If Nz(DCount("*", "qrySearchCriteria", Left(strWhere, Len(strWhere) - 5)), 0) > 0 Then
       strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 5)
       strFind = "SELECT Distinct [entryid], [divisionid], [constituencyid], [statusid], " & _
            "[dateopened], [notes], [action], [summary] " & _
            "FROM qrySearchCriteria " & strWhere & ";"
        
        Dim strDocName As String
        strDocName = "frmEntry"
        DoCmd.OpenForm strDocName
        Forms![frmEntry].Form.RecordSource = strFind
        Forms![frmEntry].Form.Requery
        Dim nTotalRecords As Integer
        nTotalRecords = DCount("*", "qrySearchCriteria", Right(strWhere, Len(strWhere) - 6))
        If nTotalRecords = 1 Then
        MsgBox nTotalRecords & " entry found."
        Else
        MsgBox nTotalRecords & " entries found."
        End If
        Else
        MsgBox "No entries matching your criteria were found."
        End If
    Else
    MsgBox "Please enter search criteria."
    End If

I just created a query to base the recordsource off of that contains all the fields from the source table.