Form field compile error

there is no field named "[EnteredOn]" in the recordsource of your form..

revised the query to include the fields [from_date] and [term_date]

Update: the County field is working properly.

The FromDate and the Term Date are not working at all?

I added [from_date] and [term_date] but there isn't suppose to be any parameterization as far as the [EnteredOn] that pops up?

That is probably in the VB code somewhere right?

you have to change

[EnteredOn] with [from_date]  
and

[EnteredOn] with [term_date]


like this

    If Not IsNull(Me.FromDate) Then
        strWhere = strWhere & "([from_date] >= " & Format(Me.FromDate, conJetDate) & ") AND "
    End If
'
    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.TermDate) Then   'Less than the next day.
        strWhere = strWhere & "([term_date] < " & Format(Me.TermDate + 1, conJetDate) & ") AND "
    End If

Thank you for updating the [from_date] and [term_date] field values so that one person has one [from_date] and one [term_date]. I see more of how the logic is structured for the form.

I notice, though, that both the [from_date] and [term_date] have to be filled in in order to get correct results (if I enter in only [from_date], results don't match query results and same for [term_date]).

I tried to modify the VBA code, but that didn't work (I tried to make it have the same syntax as the fields that do work correctly with different field names of course).

Also, I tried to put in another combo box for just the Provider KeyID so that if a user only knows the Provider KeyID, he/she can simply put that into the combo box and filter the results, but it doesn't work as the other combo box even though I don't see anything different among the two.

So you can go ahead and take the first 2 paragraphs of my last post more seriously than my last 2 paragraphs. I got any new field I want to add (combo box or text box) to work the way I want it to work (used the Wizards).

I then copied an individual If statement in the VBA code and pasted it in order and modified the field name. Then I tested it to see if when I input a real value in the new field and clicked on the Filter button, it worked, showing the correct results.

The date fields still have that problem mentioned in my last post above, though?

I will post the new revised version of the database when I get to work on Monday (my home internet is so slow for even attaching the database).

<(if I enter in only [from_date], results don't match query results and same for [term_date]).>

explain what results yo want to see for different scenarios

enter [from_date] only ?

enter [term_date] only ?

enter [from_date] and [term_date]  ?

yes, so (managers check contracts regularly and the contracts are messy with a lot of missing information and anomalies),

Scenario 1: manager A only knows that the Provider he/she is looking for contracted with us on August 8, 2012. So manager A puts 8/8/12 into the [from_date] text box and can see a list of Providers that meet the criteria (so that would be an exact match on that date field).

Scenario 2: manager B only knows that the Provider he/she is looking for has termed with us on December 20, 2011. So manager B puts 12/20/11 into the [term_date] text box and can see a list of Providers that meet the criteria (so that would be an exact match on that date field).


Please let me know if you would like me to create another question for the following Scenario 3 and Scenario 4 and I will.
(I just thought of this now so I will throw it out there for Scenario 3)

Scenario 3: manager C wants to know who all the Providers are that have been contracted with us from #any date# (let's say since January 1st, 2012). So manager A puts 1/1/12 into the [from_date] text box and can see a list of Providers that meet the criteria (so that would be >= 1/1/12 (#any date#) I believe). Can we also make it so that he/she can see all Providers with a [from_date] <=1/1/12?

Scenario 4: manager D wants to know who all the Provider are that have been terminated from #any date# (let's say since February 1st, 2012). So manager D puts 2/1/12 into the [term_date] text box and can see a list of Providers that meet the criteria (so that would be >= 1/1/12 (#any date#) I believe). Can we also make it so that he/she can see all Providers with a [term_date] <=1/1/12?

I really want to attach the database I have edited (added to really) so that we can both be on the same page, but it takes forever and then some for it to attach (my home internet speed is so slow). Nonetheless, I will try to do it and wait to see if it will do it after this post.

delete the table for zip code  then do a compact and repair so the app will be small.

Ok I ran a delete query where Not In (the zip codes where the Providers matched) and then I deleted the delete query. So that brought it down to 652KB after a Compact & Repair.

Here you go......

it didn't attach, here you go again....
Form-Building-Using-FiltersRev-2.accdb

I got the dates to filter just on their fields (should have known all along it was just the comparison operators >= and <= where I just deleted the > and the <.

So I will keep messing with it to see what I can do with it to make it better and better.

Thank you so very much for your help with this one capricorn1!!!

I will post another question if I can't figure something else out with the form.

I think I will try to build a third filter type of command button to add on to the functionality of the form.

So by changing the date fields to = only I will build a third button with the sam code as the other date fields but the third button will have the >= for the [from_date] and the <= for [term_date].

I made quite a few additions to the attached database so I understand the If statments in the VB code now, but dealing with the dates is a little bit tricky and I am not sure if I know how to handle it in the VBA.

Please take a look at what I am trying to do here.

Scenario1: manager A would like to see all Providers with a [from_date] greater than or equal to 1/1/2003 and a [term date] less than or equal to 8/12/2008, for example.

As I have it right now, I added filter buttons that don't account for the different categories of dates and if I click one filter button, it will work, then I will click the other filter button and it will also work. The problem is that if I put in dates for different fields, the filter buttons won't account for the other fields. You will see when you open my attached database.

Can we get it so that each of the filter buttons filters the entire date range criteria (from_date, term_date, fromfrom_date, tofrom_date, fromterm_date, toterm_date)??

It is not allowing me to attach so I will try again in my next post here now.......

Well, not allowing me to attach the sample database, so I will have to ask another question to have you take a look at it capricorn1.......