advanced filter option ms access 2007

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
 
sahi0002Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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
 
Rey Obrero (Capricorn1)Commented:
change this

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

to

Forms!NameOfForm!NameOFDropDown
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can't do that directly in a query ... you would have to use a Form.

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
sahi0002Author Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
sahi0002Author Commented:

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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
sahi0002Author Commented:
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
 
sahi0002Author Commented:
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
 
sahi0002Author Commented:
Perfect. Thanks a lot!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.