[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

advanced filter option ms access 2007

Posted on 2009-02-23
10
Medium Priority
?
607 Views
Last Modified: 2012-05-06
Hi,

I have a database in MS Access 2007. Using the advanced filter option I created a query which asks the user to key in "Approval Status" (which is a field in my table).
The sql query I used is:  Like "*" & [Pls enter Approval status] & "*"
The approval status is a list box basically in my form which when you drop down shows: Approved/Expired/Terminated.
Now when i open the query as a button in my form, I have to key in a parameter value for the approval status. i.e I have to type appr or expir or whatever (is close to Approved/Expired/Terminated).
I dont wish to type in but select from a drop down box Approved/Expired/Terminated.
How to go about doing this?
please help
 
0
Comment
Question by:sahi0002
  • 5
  • 4
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 23717885
change this

Like "*" & [Pls enter Approval status] & "*"

to

Forms!NameOfForm!NameOFDropDown
0
 
LVL 85
ID: 23717896
You can't do that directly in a query ... you would have to use a Form.

0
 

Author Comment

by:sahi0002
ID: 23718100
Oh really there is no way?!

Basically I want that the user should be able to select in the datahsheet, if he wants to see approved/expired or terminated records within a period of time.

I am attaching a sample database. When opening the mian form.. there is a button "SAMPLE"
The user should be able to have an option to be able to select what start date he wants (i.e field "LA Commencing") and what end date he wants (i.e field "LA Expiry)
And should also able to select if the record is approved/expired /terminated for that period of time.
I think a subform with these fields will have to be created but i could not accomplish the task.
Please help me do this.
I would be really thankful :)
PB-Listing-sample.zip
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 85
ID: 23720623
It'd be much better if you do this yourself, so that you could learn what to do (and why you do it) for the next time that you need to build something like this. We're glad to help, but we're not really here to do your work for you.

It's pretty simple to build a form that your query can use to filter. Personally I'd have a mainform/subform setup. Your Subform would be based on the query, and would output all data from the query as needed (in Datasheet view), where your Main form would include your filtering items:

1) Build a form (name it "fFilter"). This would be your Main form.
2) Build a form (name if "sfQuery"), base it on your Query, set the Form for Datasheet view, and embed it in fFilter as a subform by dropping it onto fFilter in Design view (this will set the name of the Subform control correctly). Don't set Master/Child links
3) On fFilter, add your combos/textboxes: txtStartDate, txtEndDate, and cboStatus. Obviously "txt" indicates a Textbox and "cbo" indicates a Combo. Base cboStatus on whatever table holds your Status info, or a Value List if need be.
4) On fFilter, add a button that will filter your subform:

Sub YourButton_Click()
  Dim sFilter As STring

  If Nz(me.txtStartDate,0)<>0 Then
    If IsDate(Me.txtStartDate) Then
      sFilter = "[LA Commencing]=#" & Me.txtStartDate & "#"
    End If
  End If

  If Nz(me.txtEndDate,0)<>0 Then
    If IsDate(Me.txtEndDate) Then
      If Len(sFilter)>0 Then sFilter = sFilter & " AND "
      sFilter = "[LA Expiry]=#" & Me.txtEndDate & "#"
    End If
  End If

  If Nz(Me.cboStatus, "") <> "" Then
      If Len(sFilter)>0 Then sFilter = sFilter & " AND "
      sFilter = sFilter & " AND YourStatusField='" & Me.cboStatus & "'"
  End If

'/now open your query
If Len(sFilter)> 0 Then
  Me.sfQuery.Form.Recordsource = "SELECT * FROM YourQuery WHERE " & sfilter
End If
End Sub

You will obviously have to change Field names and such to match those in your project. If YourStatusField is a Numeric value, you'll need to remove the single quotes.
0
 

Author Comment

by:sahi0002
ID: 23729681

1)"Base cboStatus on whatever table holds your Status info, or a Value List if need be"

How do i base it on  whatever table holds your Status info?

This is what I have done so far:
1.I created a query PBListingQuery in datasheet view. Put all the required fields in it.
2.Created form sfQuery, put all fields of PBListingQuery in it.
3.Created blank form fFilter. Dragged sfQuery into it as subfrom.
4.Placed a txtStartDate, txtEndDate, and cboStatus (cboStatus I used as a list box and in its record source type,put value list and record source placed the Approved/Expired/Terminated for selection (how do i base it on  whatever table holds my Status info?)
5. Created a command button, in its build event placed the code (you can see the code below i placed)
6. Where am i suppose to place the code for my query?
 By "your query" you mean the PBListingQuery i created as datasheet view which has all fields needed?

Private Sub Command11_Click()
 
  Dim sFilter As String
 
  If Nz(Me.txtStartDate, 0) <> 0 Then
    If IsDate(Me.txtStartDate) Then
      sFilter = "[LA Commencing]=#" & Me.txtStartDate & "#"
    End If
  End If
 
  If Nz(Me.txtEndDate, 0) <> 0 Then
    If IsDate(Me.txtEndDate) Then
      If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
      sFilter = "[LA Expiry]=#" & Me.txtEndDate & "#"
    End If
  End If
 
  If Nz(Me.cboStatus, "") <> "" Then
      If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
      sFilter = sFilter & " AND [Approval Status]='" & Me.cboStatus & "'"
  End If
 
End Sub

Open in new window

0
 
LVL 85
ID: 23729736
I wasn't sure where you were getting your Status values from. If you're using a Value List that's fine so long as those items match EXACTLY the values in the [Approval Status] field ... that is, if you have aApproved/Expired/Terminated as  your Values in the combo, but you have Appv, Expd and Fired in your table, the find won't work.
Private Sub Command11_Click()
 
  Dim sFilter As String
 
  If Nz(Me.txtStartDate, 0) <> 0 Then
    If IsDate(Me.txtStartDate) Then
      sFilter = "[LA Commencing]=#" & Me.txtStartDate & "#"
    End If
  End If
 
  If Nz(Me.txtEndDate, 0) <> 0 Then
    If IsDate(Me.txtEndDate) Then
      If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
      sFilter = "[LA Expiry]=#" & Me.txtEndDate & "#"
    End If
  End If
 
  If Nz(Me.cboStatus, "") <> "" Then
      If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
      sFilter = sFilter & " AND [Approval Status]='" & Me.cboStatus & "'"
  End If
 If Len(sFilter)> 0 Then
  Me.sfQuery.Form.Recordsource = "SELECT * FROM PBListingQuery WHERE " & sfilter
End If
End Sub

Open in new window

0
 

Author Comment

by:sahi0002
ID: 23729747
Oh an btw, the code you have written. Will that give me just those records that have there start date and end date exactly what i enter. I want that every record that have there period in between the start date and end date should also appear.
For eg: if i enter start date:2/2/2008 and end date: 2/2/2009
all records that have there period (or contract between these dates should show up) i.e a record with a start date: 2/3/2008 and end date:2/4/2008 should also show.
please help
0
 

Author Comment

by:sahi0002
ID: 23729892
Trying the new code, its giving me asyntax error:- syntax error(missing operator) in query expression '[LA Expiry]=#14/2/2010# AND AND [Approval Status]='Expired"
(14/2/2010 is the end date i entered )
debgging it shows me error at line:
 Me.sfQuery.Form.Recordsource = "SELECT * FROM PBListingQuery WHERE " & sfilter
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 23732976
One too many ANDs in there, and I changed this to show inclusive dates (noticed I added the Greater Than and Less Than signs).
Private Sub Command11_Click()
 
  Dim sFilter As String
 
  If Nz(Me.txtStartDate, 0) <> 0 Then
    If IsDate(Me.txtStartDate) Then
      sFilter = "[LA Commencing]>=#" & Me.txtStartDate & "#"
    End If
  End If
 
  If Nz(Me.txtEndDate, 0) <> 0 Then
    If IsDate(Me.txtEndDate) Then
      If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
      sFilter = "[LA Expiry]<=#" & Me.txtEndDate & "#"
    End If
  End If
 
  If Nz(Me.cboStatus, "") <> "" Then
      If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
      sFilter = sFilter & " [Approval Status]='" & Me.cboStatus & "'"
  End If
 If Len(sFilter)> 0 Then
  Me.sfQuery.Form.Recordsource = "SELECT * FROM PBListingQuery WHERE " & sfilter
End If

Open in new window

0
 

Author Comment

by:sahi0002
ID: 23751707
Perfect. Thanks a lot!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

868 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