 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008Filter Query by Year on a form
Hello all, The other day I was thinking that maybe in a few years some data would be irrelevant to my company(ie fluctuations in market price and what not) and that only the more recent information would be useful. So with that in mind, I want to be able to set up my report queries to have the option to filter by year. On the form i use to launch the report I have a check box [DisplayYr] and 2 text boxes in which the user would enter a year range; lets say 2002-2008. Those boxes are labeled [YrStart] and [YrEnd]. There maybe time however that the company does want all the years which is why I want to drive this with a check box. My plan is this. The report query will look at the form and see if the check box = True. if it does, then it will look to the [YrStart] and [YrEnd] boxes for which values it should be searching between. if the box is not checked, then the query will list every year in the database. So I was thinking this is my equation: IIF(Nz([Forms]![Project Inquiry].[DisplayYr]),*, Between [Forms]![Project Inquiry].[YrStart] And [Forms]![Project Inquiry].[YrEnd] The only problem is I'm not sure how to set the text box format for [YrStart] and [YrEnd] so that the "Year" is understood by Ms access. Like if its searching through dates, how does it know the number I enter lets say 2004 should include a date of 1/12/2004 in its query? I guess I understand the concept behind it I'm just a little unsure of the coding aspect of it. Sorry for the novel :) One part of your solution will simply require defining a new dynamic field in your query. The source will be Year([tableorqueryname]![datefieldname]) and the criteria will be the numbers and or ranges and or single value of your txtfield, depending on how you decide to go from there. Ron the field would more probably be stated as YeartoCompare:Year([tableorqueryname]![datefieldname]) Hello Ron, If it was a dynamic field wouldn't that mean it would pop up everytime I hit the Report Launch button? I don't want the user to be bombarded with a pop up dialog everytime they want to open a report, especially if they aren't concerned about the year. Thats why I thought having a nice GUI with the help of check boxes would help the user understand what it is they're going to get when they launch that report. Also How would Access be able to understand that the number I enter means year and isn't just some random general number? Is there a format I would have to follow? I could try your method. Could you maybe give me a practical example? > One part of your solution will simply require defining a new dynamic > field in your query. The source will be [quoted text clipped - 4 lines] > > Ron >>The only problem is I'm not sure how to set the text box format for [YrStart] and [YrEnd] so that the "Year" is understood by Ms access. First question is your records stored using a number or text field for the year or do you use a DateTime datatype field? If it is a DateTime datatype field then you need to add a calculated field like this -- Record_Year: Format([YourDateField], "yyyy") Have your check box [DisplayYr] default to 0 (zero). Here is one method for the criteria -- Between IIf([Forms]![Project Inquiry].[DisplayYr]=-1,[Forms]![Project Inquiry].[YrStart],0) And IIf([Forms]![Project Inquiry].[DisplayYr]=-1,[Forms]![Project Inquiry].[YrEnd],2999)  SignatureKARL DEWEY Build a little - Test a little > Hello all, > [quoted text clipped - 23 lines] > the concept behind it I'm just a little unsure of the coding aspect of it. > Sorry for the novel :) Karl, I tried you equation in the fields criteria and i keep getting an error telling me its either typed wrong or its too complex to evaluate. Thats the approach I would like to take though. In your equation it all seems to be ok, don't know why Ms Access is having difficulty with it. > >>The only problem is I'm not sure how to set the text box format for > [YrStart] and [YrEnd] so that the "Year" is understood by Ms access. [quoted text clipped - 37 lines] > > the concept behind it I'm just a little unsure of the coding aspect of it. > > Sorry for the novel :) Post your complete SQL statement. SignatureKARL DEWEY Build a little - Test a little > Karl, > [quoted text clipped - 44 lines] > > > the concept behind it I'm just a little unsure of the coding aspect of it. > > > Sorry for the novel :) This can and should all be done from the form without any filtering for years in your report's query. Also, you don't need the check box. All you need is the from year and to year controls. You do the filtering using the Where arguement of the OpenReport method: The assumptions are: If the user enters no year in either control, they want all the data If the user enters a year in the from year control and nothing in the to year control, they want data beginning with that year to the present. If the user enters nothing in the from year and a year in the to year control, they want the data from the beginning through the to year. If years are entered in both controls, they want the data from the from year through the data in the to year. To get one year, they would enter the same year in both controls. That gives the most flexible selections to the user. Now, to use it: Dim strWhere As String With Me If IsNull(.txtFromYr) And IsNull(.txtToYr) Then strWhere = vbNullString ElseIf IsNull(.txtFromYr) And Not IsNull(.txtToYr) Then strWhere = "Year([SomeDate) <= " & .txtToYr ElseIf Not IsNull(.txtFromYr) And IsNull(.txtToYr) Then strWhere = "Year([SomeDAte]) >= " & .txtFromYr Else strWhre = "Year([SomeDAte]) BETWEEN " & .txtFromYr & " AND " & .txtToYr End If End With Docmd.OpenReport "MyReport", , , strWhere Simple as that SignatureDave Hargis, Microsoft Access MVP > Hello all, > [quoted text clipped - 23 lines] > the concept behind it I'm just a little unsure of the coding aspect of it. > Sorry for the novel :) Karl, Here is my sql code: SELECT [Job Information].ProjectID, [Job Information].ProjectDescription, [Job Information].DateStarted, [Job Material Cost].CostType, [Job Material Cost].CostCode, [Job Material Cost].MatDescription, [Job Material Cost].CostAmount, [SubQuery - Total Material Cost].[Total Material Cost], Format([DateStarted],"yyyy") AS YearFilter FROM ([Job Information] INNER JOIN [SubQuery - Total Material Cost] ON [Job Information].ProjectID = [SubQuery - Total Material Cost].ProjectID) LEFT JOIN [Job Material Cost] ON [Job Information].ProjectID = [Job Material Cost].ProjectID GROUP BY [Job Information].ProjectID, [Job Information].ProjectDescription, [Job Information].DateStarted, [Job Material Cost].CostType, [Job Material Cost].CostCode, [Job Material Cost].MatDescription, [Job Material Cost].CostAmount, [SubQuery - Total Material Cost].[Total Material Cost], Format([DateStarted],"yyyy") HAVING ((([SubQuery - Total Material Cost].[Total Material Cost]) Between [Forms]![Project Inquiry]![CostStartRange] And [Forms]![Project Inquiry]![CostEndRange]) AND ((Format([DateStarted],"yyyy")) Between IIf([Forms]![Project Inquiry].[DisplayYr]=True,[Forms]![Project Inquiry].[YrStart],0) And IIf([Forms]![Project Inquiry].[DisplayYr]=True,[Forms]![Project Inquiry].[YrEnd],3999))); @ Klatuu, The reason I have the check boxes is because one button on my form may launch several different reports ( up to 5). Depending on which box is checked, it will launch that particular report say material cost report or project hrs report. Then when you choose to launch that report, there is a range you can search for by typing start and end values into text boxes.The queries look to the boxes to see if they are checked, if so then they complete the criteria specified in the start and end range on the form. I don't know if its the most efficient way, but I'm kinda teaching myself as I go along. Sorry if I'm being confusing its been a long day ha. > This can and should all be done from the form without any filtering for years > in your report's query. Also, you don't need the check box. All you need is [quoted text clipped - 57 lines] > > the concept behind it I'm just a little unsure of the coding aspect of it. > > Sorry for the novel :) If the check box does not affect what data are retrieved by the query, then the method I posted will work more easily than any other. If all reports can be filtered using the same Where condition, then first build the string, then open the report(s) you want. No problem. SignatureDave Hargis, Microsoft Access MVP > Karl, > [quoted text clipped - 93 lines] > > > the concept behind it I'm just a little unsure of the coding aspect of it. > > > Sorry for the novel :) Ok that makes sense So basically when i click the button its going to open the report after it "Runs" the strWhere then? Which is what is going to filter the report. Here is the code I have behind my button now. How would I attach the strWhere to it? The "Display"whatevers are the check boxes on the form. Private Sub cmdPreview_Click() If DisplayMaterial = True Then DoCmd.OpenReport "InquiryReport - Material Costs", acPreview End If If DisplayHrs = True Then DoCmd.OpenReport "InquiryReport - Project Hrs", acPreview End If If DisplayMix = True Then DoCmd.OpenReport "InquiryReport - Project Mixes", acPreview End If If DisplayItems = True Then DoCmd.OpenReport "InquiryReport - Project Items", acPreview End If End Sub Also I'm trying to piece your code together to try and understand how its actually arriving at an answer. Im guessing the (.txtFromYr) is one of the text boxes. im not sure what "Year([SomeDAte]) means. > If the check box does not affect what data are retrieved by the query, then > the method I posted will work more easily than any other. If all reports can [quoted text clipped - 98 lines] > > > > the concept behind it I'm just a little unsure of the coding aspect of it. > > > > Sorry for the novel :) Looks like an extra parenthesis on the end. SignatureKARL DEWEY Build a little - Test a little > Karl, > [quoted text clipped - 93 lines] > > > the concept behind it I'm just a little unsure of the coding aspect of it. > > > Sorry for the novel :) Dave, I tried your method and it worked! This morning I read through each line and I could understand what it was trying to do. I guess when it comes to programing for Access I'm still a little baffled by some of its syntax. But thanks again to you and everyone else that offered solutions to me problems. This place rocks ! > Hello all, > [quoted text clipped - 25 lines] > I guess I understand the concept behind it I'm just a little unsure > of the coding aspect of it. Sorry for the novel :) SELECT * FROM TableName WHERE (DateField >= DateSerial(Forms!FormName!YrStart, 1, 1) AND DateField < DateSerial(Forms!FormName!YrEnd + 1, 1, 1)) OR [Forms]![Project Inquiry].[DisplayYr] = False Note that the two date tests have parenthesis around them so that combined they are OR'd with the test for the CheckBox. That should result in all dates being returned when the CheckBox is not checked and the year range test being applied when it is checked. Note also that I apply the date criteria directly against the field name, not an expression based on the field name. That is much more efficient. If you apply the criteria to an expression like Year(DateField) then you eliminate any chance for the query engine to use the index that your date field should have on it. When applying criteria to an expression (no matter how simple) that expression has to be evaluated for every row in the table and the entire table scanned to look for matches. Often applying criteria to an expression results in a query that is more concise and easier to understand when looking at it, but it is seldom necessary and should be avoided in all cases where possible. There are a few situations where that is the only way to get the results you want though. SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.