Multiple date parameters...simplify

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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


Ofer said:
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
--
\\// Live Long and Prosper \\//
BS"D


Shek5150 said:
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
Click to expand...
Click to expand...
 
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]

--
\\// Live Long and Prosper \\//
BS"D


Shek5150 said:
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


Ofer said:
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
--
\\// Live Long and Prosper \\//
BS"D


Shek5150 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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



Ofer said:
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]

--
\\// Live Long and Prosper \\//
BS"D


Shek5150 said:
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


Ofer said:
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
--
\\// Live Long and Prosper \\//
BS"D


:

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
Click to expand...
Click to expand...
Click to expand...
 
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]

--
\\// Live Long and Prosper \\//
BS"D


Shek5150 said:
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



Ofer said:
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]

--
\\// Live Long and Prosper \\//
BS"D


Shek5150 said:
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]

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
--
\\// Live Long and Prosper \\//
BS"D


:

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
Click to expand...
Click to expand...
Click to expand...
 
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"));


=======================================





Ofer said:
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]

--
\\// Live Long and Prosper \\//
BS"D


Shek5150 said:
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



Ofer said:
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]

--
\\// 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
[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]

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
--
\\// Live Long and Prosper \\//
BS"D


:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top