Solved

Form field compile error

Posted on 2013-01-18
16
337 Views
Last Modified: 2013-01-21
Hi I have a form that gives me a compile error when I enter the County name in the County text box and press the Filter button.

Also, the term_date and From date fields don't filter any of the records out. The from field is actually giving me errors (one came up with a parameter saying Entered On and the other said "The value you entered for this field isn't valid"?

I don't know VB so I need help to correct the issue please.

Please see attached.
Form-Building-Using-FiltersRev-2.accdb
0
Comment
Question by:IEHP1
[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
  • 11
  • 5
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38795131
there is no field named "[EnteredOn]" in the recordsource of your form..

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

Author Comment

by:IEHP1
ID: 38795135
Update: the County field is working properly.

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

Author Comment

by:IEHP1
ID: 38795138
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?
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38795148
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
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38795187
0
 

Author Comment

by:IEHP1
ID: 38795667
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.
0
 

Author Comment

by:IEHP1
ID: 38795746
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).
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38796254
<(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]  ?
0
 

Author Comment

by:IEHP1
ID: 38796754
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38796949
delete the table for zip code  then do a compact and repair so the app will be small.
0
 

Author Comment

by:IEHP1
ID: 38797031
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......
0
 

Author Comment

by:IEHP1
ID: 38797033
it didn't attach, here you go again....
Form-Building-Using-FiltersRev-2.accdb
0
 

Author Comment

by:IEHP1
ID: 38800124
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.
0
 

Author Comment

by:IEHP1
ID: 38800128
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].
0
 

Author Comment

by:IEHP1
ID: 38802421
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.......
0
 

Author Comment

by:IEHP1
ID: 38802424
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.......
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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: …

724 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