Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

BuildCriteria

Posted on 2000-02-18
3
Medium Priority
?
454 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
paasky earned 200 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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