 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / January 2006Multiple date parameters...simplify
Greetings... I have two subforms embedded into my main "Metrics Form"...however, both subforms are based on a query w/ Beginning & Ending Date parameters...both queries are actually sorting the same data..one sorts for "completed suicides" and the other sorts for "attempted suicides." So, my question is this ... is there a better way to do this? One that does not requrie the user to enter the Beginning & Ending dates (twice)....fore each subform (query)? Hope that wasn't toooo confusing... Thanks in advance for your help... V/R, Steve Create two fields in the main form [Start Date] And [End Date] In the Query add the criteria Select * From TableName Where [Start Date] >= Forms![MainFormName]![Start date] And [End date] <= Forms![MainFormName]![End date] The same in the second query. On the After update of both Date field in the main form write If Not IsNull(Me.[Start Date]) And Not IsNull(Me.[End Date]) Then Me.SubForm1Name.Requery Me.SubForm2Name.Requery End If  Signature\\// Live Long and Prosper \\// BS"D > Greetings... > [quoted text clipped - 12 lines] > > Steve Greetings Ofer and thank you for the reply...however, I seem to be having some difficulty implementing your suggestion...I've added the two fields [Start Date] &[End Date] to my main form.... Regarding the criteria to be added to my query...this is what I've added: Select * From Tbl2_Member_History Where [Start Date] >= Forms![Frm_Both]![Start Date] and [End Date]<= Frm![Frm_Both]![End Date] That being said, I'm not able to tab out of the criteria field (of my date field in the query) w/ out receiving a syntax error message... Have I misunderstood your direction? V/R, Steve > Create two fields in the main form [Start Date] And [End Date] > [quoted text clipped - 26 lines] > > > > Steve You got a syntax error in the Forms word, you have Frm Yours Select * From Tbl2_Member_History Where [Start Date] >= Forms![Frm_Both]![Start Date] and [End Date]<= Frm![Frm_Both]![End Date] Need to be Select * From Tbl2_Member_History Where [Start Date] >= Forms![Frm_Both]![Start Date] and [End Date]<= Forms![Frm_Both]![End Date] Signature\\// Live Long and Prosper \\// BS"D > Greetings Ofer and thank you for the reply...however, I seem to be having > some difficulty implementing your suggestion...I've added the two fields [quoted text clipped - 44 lines] > > > > > > Steve Ofer... I appreciate your patience; however, I'm just not getting this...I did correct the spelling issue below...but no luck...perhaps I've not communicated myself clearly...is your direction assuming that I have two date fields (a starting and ending date) in my table? Because I don't, I only have one..."Date of Incident" ....and in my query I have the "Between [Start Date] and [End Date]" statement for returning my desired dates...consequently each of my embedded subforms are attached to these two queries (w/ the same "Between [Start Date] and [End Date]" statement...)...so I'm entering Start & End Dates twice... Not sure what I'm doing wrong...any thoughts would be appreciated... V/R, Steve > You got a syntax error in the Forms word, you have Frm > [quoted text clipped - 54 lines] > > > > > > > > Steve If you have only one date field then try this Select * From Tbl2_Member_History Where [Date of Incident] Between Forms![Frm_Both]![Start Date] and Forms![Frm_Both]![End Date] Signature\\// Live Long and Prosper \\// BS"D > Ofer... > [quoted text clipped - 72 lines] > > > > > > > > > > Steve Finally, it worked....I thank you very much for your help AND patience....after following your instruction (as outlined below) I had to actually remove and re-embed my subforms before this worked....any thoughts on why this was necessary...just curious? This is what I finally ended up w/:=========================== Main Form (w/ two embedded subforms) has two text boxes for [txt_StartDate] and [txt_EndDate] w/ the following on the After Update event (of both date fields): If Not IsNull(Me.[txt_StartDate]) And Not IsNull(Me.[txt_EndDate]) Then Me.Frm_ZZZ_Completed.Requery Me.Frm_ZZZ_Attempted.Requery End If ================================================== Query #1: SELECT Tbl2_Member_History.[2Tbl_Successful], * FROM Tbl2_Member_History WHERE ((([2Tbl_DateOfAttempt]) Between Forms!Frm_ZZZ_Both!txt_StartDate And Forms!Frm_ZZZ_Both!txt_EndDate) And (([2Tbl_Successful])="Attempted")); Query #2: SELECT Tbl2_Member_History.[2Tbl_Successful], * FROM Tbl2_Member_History WHERE ((([2Tbl_DateOfAttempt]) Between Forms!Frm_ZZZ_Both!txt_StartDate And Forms!Frm_ZZZ_Both!txt_EndDate) And (([2Tbl_Successful])="Completed")); ======================================= > If you have only one date field then try this > [quoted text clipped - 77 lines] > > > > > > > > > > > > Steve |
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
|
©2008 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.