Filter button to Filter by 2 fields

dee

I'd like to filter by the following criteria:

left(LeadDisposition,3) = "Sit" AND Appt_Date = Text767

I have no idea how to do this. Appreciate help.

Feb 6 '07 #1

Rich P

Are you trying to filter a query that is the recordsource for a form?

You have a button on a form. When you click the button, something is
supposed to happen. Could you describe what is supposed to happen?
like some date gets displayed on the form, or the data that is being
displayed on the form changes ater you click the button.

>>left(LeadDisposition,3) = "Sit" AND Appt_Date = Text767

This code would usually have something before it like

If left(LeadDisposition,3) = "Sit" AND Appt_Date = Text767 Then
'something is supposed to happen'

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Feb 6 '07 #2

dee

On Feb 6, 2:37 pm, Rich P <rpng...@aol.comwrote:

Are you trying tofiltera query that is the recordsource for a form?

You have a button on a form. When you click the button, something is
supposed to happen. Could you describe what is supposed to happen?
like some date gets displayed on the form, or the data that is being
displayed on the form changes ater you click the button.

>left(LeadDisposition,3) = "Sit" AND Appt_Date = Text767

This code would usually have something before it like

If left(LeadDisposition,3) = "Sit" AND Appt_Date = Text767 Then
'something is supposed to happen'

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

I'm trying to filter a form to display only certain records.

Show only those records of the form 'FmLeads' where the leftmost 3
characters of the field 'LeadDisposition' ="Sit", and where the value
of the field 'Appt_Date' = the value of the field 'Text767'.
The form is already open. To activate the filter, a button is
pressed.

Sorry for my ambiguity.

Feb 6 '07 #3

Rich P

OK. To filter your form do this:

Private Sub Command1_click()
Me.Filter = "left(LeadDisposition,3) = 'Sit' " _
& "And Appt_Date = #" & Text767 & "*"

Me.FilterOn = True

End Sub
Use the Form's Filter property - which you then have to turn on. Note
that I delimit the text 'Sit' with single quotes - this is required.
All text in Sql is delimited with single quotes. And then I delimit
your date field with # symbol. In Access sql you delimit date values
with # # -- required. Note also that I am assuming that LeadDisposition
is a field in the underlying datasource of your form. I can include
that inside the sql statement. If LeadDisposition is a text field then
I have to change the Filter statement like this

Me.Filter = "Left(fldname, 3 = 'Sit' " _
& "And Appt_Date = #" & Text767 & "*"

Look in the Access Help files for more explanations - place the mouse
over the word Filter in your code (Me.Filter) and click it so that the
cursor is on the word. Then press the F1 key. This will bring up help
on that word

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Feb 6 '07 #4

Rich P

Here are some syntax corrections (I left out a paren and there is no *)
Me.Filter = "Left(fldname, 3) = 'Sit' " _
& "And Appt_Date = #" & Text767 & "#"

Or

Me.Filter = "left(LeadDisposition,3) = 'Sit' " _
& "And Appt_Date = #" & Text767 & "#"

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Feb 6 '07 #5

dee

On Feb 6, 5:21 pm, Rich P <rpng...@aol.comwrote:

Here are some syntax corrections (I left out a paren and there is no *)

Me.Filter= "Left(fldname, 3) = 'Sit' " _
& "And Appt_Date = #" & Text767 & "#"

Or

Me.Filter= "left(LeadDisposition,3) = 'Sit' " _
& "And Appt_Date = #" & Text767 & "#"

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Thanks Rich, but it does not seem to work.

The first part works by itself:
Me.Filter = "left(LeadDisposition,3) = 'Sit'"

The second part does not work by itself:
Me.Filter = "Appt_Date = #" & Text767 & "#"

Enter Parameter Value comes up asking for Appt_Date input.

I figured since they did not work together, I would try to make each
work individually first.

By the way, Appt_Date is short date field and is from a query.
Text767 is also a short text field and is unbound.

Feb 6 '07 #6

Rich P

>The first part works by itself:
Me.Filter = "left(LeadDisposition,3) = 'Sit'"

>The second part does not work by itself:
Me.Filter = "Appt_Date = #" & Text767 & "#"

>Enter Parameter Value comes up asking for Appt_Date input.

>I figured since they did not work together, I would try to >make each
work individually first.

>By the way, Appt_Date is short date field and is from a >query.
Text767 is also a short text field and is unbound.

Well, the code assumes that there is a Date value in Text767. If the
textbox is empty then the code will complain. If the textbox is emtpy
try typing a datevalue in there and then run the code.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Feb 7 '07 #7

dee

On Feb 6, 7:07 pm, Rich P <rpng...@aol.comwrote:

The first part works by itself:
Me.Filter= "left(LeadDisposition,3) = 'Sit'"
The second part does not work by itself:
Me.Filter= "Appt_Date = #" & Text767 & "#"
Enter Parameter Value comes up asking for Appt_Date input.
I figured since they did not work together, I would try to >make each
work individually first.
By the way, Appt_Date is short date field and is from a >query.
Text767 is also a short text field and is unbound.

Well, the code assumes that there is a Date value in Text767. If the
textbox is empty then the code will complain. If the textbox is emtpy
try typing a datevalue in there and then run the code.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

There is of course a date value in Text767.
There are also records with matching date values in Appt_Date.
Both fields are formatted identically as short dates.

Feb 7 '07 #8

Rich P

>>There is of course a date value in Text767.

>>There are also records with matching date values in ??>>Appt_Date.
Both fields are formatted identically as short dates.

here are a few things you can try. First, create a test query to test
the datasource of your form (query the table or query that your form is
based on). In the criteria section for Appt_Date add a test date value
and run the query and see if it returns any records. If the query
returns records, then your table/query is OK, the fieldname is OK...the
datasource is working

If your datasourece is OK, then perform a similar test in the Filter
statement -- try hardcoding a test date value like this:
...
Me.Filter= "Appt_Date = #10/31/06#"
Me. FilterOn = True
...
or whatever your test date value is. If your test date value worked in
the Test query then it should work in the Filter statement. If it does
work, then the problem is with Text767. You can test Text767 by adding
this line of code to your Button procedure

Private Sub Command1_Click()
Debug.Print "*" & Text767 & "*"
Me.Filter= "Appt_Date = #10/31/06#"
Me. FilterOn = True
End Sub

You still have the hardcoded test date value, but you are using the
Debug.Print statement to test if any value is being read from Text767.
Note that I surround Text767 with "*" "*" This is so that if nothing is
being read you can at least see that the Debug.Print statement did run
because you will see ** in the Debug window. If the app can read
Text767 then you will see *10/31/2006* (or whatever your date value is).

If you aren't getting anything in the Debug window other than ** then
maybe the textbox you are referencing is the wrong textbox which could
be the case because it sounds like you have several textboxes on your
form - Text767? I usually see like Text0, Text1...Text10...Text20, but
Text767? That is a pretty high number. You know, you can rename a
textbox to something more meaningful like txtApptAte. Then there will
be no question what textbox you are referring too. Note, convention has
it that textbox names should be preceded by txt, comboboxes, cbo,
listboxes, lst, labels lbl, checkboxes, chk, radiobuttons, rad (wait -
that's in VB.Net) I guess they are called option buttons in Access -
opt.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Feb 7 '07 #9

dee

On Feb 7, 2:33 am, Rich P <rpng...@aol.comwrote:

>There is of course a date value in Text767.
There are also records with matching date values in ??>>Appt_Date.
Both fields are formatted identically as short dates.

here are a few things you can try. First, create a test query to test
the datasource of your form (query the table or query that your form is
based on). In the criteria section for Appt_Date add a test date value
and run the query and see if it returns any records. If the query
returns records, then your table/query is OK, the fieldname is OK...the
datasource is working

If your datasourece is OK, then perform a similar test in the Filter
statement -- try hardcoding a test date value like this:
..
Me.Filter= "Appt_Date = #10/31/06#"
Me. FilterOn = True
..
or whatever your test date value is. If your test date value worked in
the Test query then it should work in the Filter statement. If it does
work, then the problem is with Text767. You can test Text767 by adding
this line of code to your Button procedure

Private Sub Command1_Click()
Debug.Print "*" & Text767 & "*"
Me.Filter= "Appt_Date = #10/31/06#"
Me. FilterOn = True
End Sub

You still have the hardcoded test date value, but you are using the
Debug.Print statement to test if any value is being read from Text767.
Note that I surround Text767 with "*" "*" This is so that if nothing is
being read you can at least see that the Debug.Print statement did run
because you will see ** in the Debug window. If the app can read
Text767 then you will see *10/31/2006* (or whatever your date value is).

If you aren't getting anything in the Debug window other than ** then
maybe the textbox you are referencing is the wrong textbox which could
be the case because it sounds like you have several textboxes on your
form - Text767? I usually see like Text0, Text1...Text10...Text20, but
Text767? That is a pretty high number. You know, you can rename a
textbox to something more meaningful like txtApptAte. Then there will
be no question what textbox you are referring too. Note, convention has
it that textbox names should be preceded by txt, comboboxes, cbo,
listboxes, lst, labels lbl, checkboxes, chk, radiobuttons, rad (wait -
that's in VB.Net) I guess they are called option buttons in Access -
opt.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Me.Filter= "Appt_Date = #10/31/06#"
Does not work but:
Me.Filter= "[Appt Date] = #10/31/06#"

Goes to show that it is best to never use spaces in field names.
I thought that using an underline to connect was required when spaces
are used.
but in this case the space must stay.

Hope this will help others.

Thanks for your help.

Feb 8 '07 #10