Re: Using combo box to search form

Daniel

Have you tried building one of your WHERE strings and using that in a query?

You can open a new query in design view, switch to SQL view, and paste (or
type) in a SQL string, then run the query.

I suspect you may be having an issue with too many "AND"s...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Daniel" <Daniel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:18E6F713-EF8A-4408-89CC-499992397D12@xxxxxxxxxxxxxxxx
Hi, I am creating a search area in the Header section of my form to
display
the results in the Detail section. The code I have so far is below. It
isn't really working properly, so I am hoping someone can look at it and
see
why.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim strA As String
Dim strB As String
Dim lngLen As Long


If Me.cboLevel = "A" Then
strA = "A"
strWhere = strWhere & "([Level] = strA ) AND "
ElseIf Me.cboLevel = "B" Then
strB = "B"
strWhere = strWhere & "([Level] = strB) AND "
Else
strWhere = strWhere & "([Level] = FALSE) AND "
End If

If Not IsNull(Me.cboCompanyName) Then
strWhere = strWhere & "([CompanyName] = TRUE) AND "
Else
strWhere = strWhere & "([CompanyName] = False) AND "
End If

If Not IsNull(Me.cboCountry) Then
strWhere = strWhere & "([Country] = TRUE) AND "
Else
strWhere = strWhere & "([Country] = FALSE) AND "
End If

If Not IsNull(Me.cboArch) Then
strWhere = strWhere & "([ArchFixtures] = TRUE) AND "
Else
strWhere = strWhere & "([ArchFixtures] = FALSE) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If


End Sub


.

Relevant Pages

  • Re: SQL help
    ... query and output the record count to another text box on the same form ... Dim strTotalships as string ... Can you help with this portion of the SQL statement? ...
    (comp.databases.ms-access)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Use ADO to retrieve data from Access Parameter Query
    ... I want to run an existing Access *Parameter* Query and pass 2 params to the ... Dim Param1 As ADODB.Parameter ... > Here is a function that returns a recordset based on a SQL statement. ... > Public Function RunQuery(ByVal strSelect As String, ...
    (microsoft.public.excel.programming)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
Loading