Solved

BuildCriteria

Posted on 2000-02-18
3
436 Views
Last Modified: 2008-03-03
I'm running a popup box that allows the user to enter some criteria to filter out a form...The problem is with strInput1 and strInput2. If the user doesn't input anything in this field then I want the BuildCriteria to run the command with the following
Like "*" or is null
I've left the latest concatenation in for example purposes..

Dim frm As Form
    Dim strInput As String, strFilter As String, strInput2 As String, strinput3 As String

    Dim rs As Recordset
   
    Set frm = Forms!frmBasePlanMain
     
            If IsNull(Forms!frmBasePlanMainSearch!TxtSiteName) Then
                MsgBox "You Must Enter a Site Name"
                Exit Sub
            Else
            strInput = Forms!frmBasePlanMainSearch!TxtSiteName
            End If
           
            If IsNull(Forms!frmBasePlanMainSearch!txtExchUnit) Then
                strInput2 = "*" & "' or is null '"
            Else
            strInput2 = Forms!frmBasePlanMainSearch!txtExchUnit
            End If
           
            If IsNull(Forms!frmBasePlanMainSearch!txtExchExt) Then
                strinput3 = "*" & "' or is null '"
            Else
            strinput3 = Forms!frmBasePlanMainSearch!txtExchExt
            End If
           
        strFilter = BuildCriteria("exchname", dbText, strInput)
        strFilter = strFilter & " AND " & BuildCriteria("ExchUnit", dbText, strInput2)
        strFilter = strFilter & " AND " & BuildCriteria("ExchExt", dbText, strinput3)
       
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM BasePlan WHERE " & strFilter, dbOpenSnapshot)
   
    If Not rs.EOF Then
        frm.Filter = strFilter
        frm.FilterOn = True
        frm.OrderBy = "apack DESC"
        frm.OrderByOn = True
        rs.Close
    Else
         MsgBox "No Records Returned"
         rs.Close
    End If

Any help??

Scottsanoedro
0
Comment
Question by:scottsanpedro
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
paasky earned 50 total points
ID: 2535096
Hello scottsanpedro,

Try this:

....
    Set frm = Forms!frmBasePlanMain
     
            If IsNull(Forms!frmBasePlanMainSearch!TxtSiteName) Then
                MsgBox "You Must Enter a Site Name"
                Exit Sub
            Else
            strInput = Forms!frmBasePlanMainSearch!TxtSiteName
            End If
           
            strFilter = BuildCriteria("exchname", dbText, strInput)
             
            If Not IsNull(Forms!frmBasePlanMainSearch!txtExchUnit) Then
                strFilter = strFilter & " AND " & BuildCriteria("ExchUnit", dbText, strInput2)
            End If
             
            If Not IsNull(Forms!frmBasePlanMainSearch!txtExchExt) Then
                strFilter = strFilter & " AND " & BuildCriteria("ExchExt", dbText, strinput3)
            End If
       
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM BasePlan WHERE " & strFilter, dbOpenSnapshot)
....
....
     
Regards,
Paasky
0
 
LVL 1

Author Comment

by:scottsanpedro
ID: 2535124
Of course...

Thanks very much

How it should be..Nice and quick

Cheers

Scott
0
 
LVL 1

Author Comment

by:scottsanpedro
ID: 2535128
Cheers

Scott
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ODBC driver not showing up in access 2 43
Export  Access Query To Excell 16 43
Access query with left expression 9 30
Format a Field AFTER UPDATE 5 18
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now