Filter Query by Year on a form

[idtjes3]

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

 

[Ron2006]

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

 

[Ron2006]

the field would more probably be stated as

YeartoCompare:Year([tableorqueryname]![datefieldname])

 

[KARL DEWEY]

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)

 

[idtjes3]

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?

 

[idtjes3]

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.

[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)
--
KARL DEWEY
Build a little - Test a little

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

 

[KARL DEWEY]

Post your complete SQL statement.
--
KARL DEWEY
Build a little - Test a little

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.
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)
--
KARL DEWEY
Build a little - Test a little

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

 

[Klatuu]

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
--
Dave Hargis, Microsoft Access MVP

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

 

[idtjes3]

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
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
--
Dave Hargis, Microsoft Access MVP

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

 

[Klatuu]

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.
--
Dave Hargis, Microsoft Access MVP

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
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
--
Dave Hargis, Microsoft Access MVP

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

 

[KARL DEWEY]

Looks like an extra parenthesis on the end.
--
KARL DEWEY
Build a little - Test a little

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
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
--
Dave Hargis, Microsoft Access MVP

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

 

[idtjes3]

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
be filtered using the same Where condition, then first build the string, then
open the report(s) you want. No problem.
--
Dave Hargis, Microsoft Access MVP

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
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
--
Dave Hargis, Microsoft Access MVP


:

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

 

[idtjes3]

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 !

 

[Rick Brandt]

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

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.