Solved

Form field compile error

Posted on 2013-01-18
16
334 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
  • 11
  • 5
16 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
Update: the County field is working properly.

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

Author Comment

by:IEHP1
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
0
 

Author Comment

by:IEHP1
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
<(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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:IEHP1
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
delete the table for zip code  then do a compact and repair so the app will be small.
0
 

Author Comment

by:IEHP1
Comment Utility
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
Comment Utility
it didn't attach, here you go again....
Form-Building-Using-FiltersRev-2.accdb
0
 

Author Comment

by:IEHP1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now