Running date queries and disregards years

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

Guest

I have set up an access queries with data ranging from year 2001 - 2007, the
date criteria of the query is based on a Form.

I want to set the form with Months selection only (no date or year) and when
I run the query, the program will automatically select all the data that
falls within the months selected for all year. i.e. if I select Feb to Mar,
the query will filter the data and obtain all record between Feb and Mar of
every year from 2001-2007. Is this possible? Can anyone will me any insight
or hints on how to set this up?
 
You can create a calculated column in your query like:
Mth: Month([Unnamed Date Field])
Set the criteria under this column to:
Between Forms!NoName!txtStartMth and Forms!NoName!txtEndMth
 
The method seems to work fine, except the facts that the filter doesn't seem
to work too well.

In my query: I've inserted a new calculation colum : Month ([Month])
with criteria: Between [Forms]![frReportFilter]![frMonth] And
[Forms]![frReportFilter]![toMonth]

When I run the query in the form, the filter results does not always
represent the selected period - i.e. when I select Jan to Dec, it only shows
a few of the months (namely Jan, Feb, Oct, Nov and Dec) that fall in this
period over those years. I have tried to use numbers and alphabet to
represents the months in the dropdown box but the result is the same in both
case.

Do you know what the problem is?

Regards
Sin

Duane Hookom said:
You can create a calculated column in your query like:
Mth: Month([Unnamed Date Field])
Set the criteria under this column to:
Between Forms!NoName!txtStartMth and Forms!NoName!txtEndMth

--
Duane Hookom
Microsoft Access MVP


Sin said:
I have set up an access queries with data ranging from year 2001 - 2007, the
date criteria of the query is based on a Form.

I want to set the form with Months selection only (no date or year) and when
I run the query, the program will automatically select all the data that
falls within the months selected for all year. i.e. if I select Feb to Mar,
the query will filter the data and obtain all record between Feb and Mar of
every year from 2001-2007. Is this possible? Can anyone will me any insight
or hints on how to set this up?
Click to expand...
Click to expand...
 
Apparently your month numbers are being treated as if they were text. You
could select Query->Parameters and enter t

Forms!NoName!txtStartMth Integer
Forms!NoName!txtEndMth Integer

--
Duane Hookom
Microsoft Access MVP


Sin said:
The method seems to work fine, except the facts that the filter doesn't seem
to work too well.

In my query: I've inserted a new calculation colum : Month ([Month])
with criteria: Between [Forms]![frReportFilter]![frMonth] And
[Forms]![frReportFilter]![toMonth]

When I run the query in the form, the filter results does not always
represent the selected period - i.e. when I select Jan to Dec, it only shows
a few of the months (namely Jan, Feb, Oct, Nov and Dec) that fall in this
period over those years. I have tried to use numbers and alphabet to
represents the months in the dropdown box but the result is the same in both
case.

Do you know what the problem is?

Regards
Sin

Duane Hookom said:
You can create a calculated column in your query like:
Mth: Month([Unnamed Date Field])
Set the criteria under this column to:
Between Forms!NoName!txtStartMth and Forms!NoName!txtEndMth

--
Duane Hookom
Microsoft Access MVP


Sin said:
I have set up an access queries with data ranging from year 2001 - 2007, the
date criteria of the query is based on a Form.

I want to set the form with Months selection only (no date or year) and when
I run the query, the program will automatically select all the data that
falls within the months selected for all year. i.e. if I select Feb to Mar,
the query will filter the data and obtain all record between Feb and Mar of
every year from 2001-2007. Is this possible? Can anyone will me any insight
or hints on how to set this up?
Click to expand...
Click to expand...
Click to expand...
 
Works great.

Thanks.

Duane Hookom said:
Apparently your month numbers are being treated as if they were text. You
could select Query->Parameters and enter t

Forms!NoName!txtStartMth Integer
Forms!NoName!txtEndMth Integer

--
Duane Hookom
Microsoft Access MVP


Sin said:
The method seems to work fine, except the facts that the filter doesn't seem
to work too well.

In my query: I've inserted a new calculation colum : Month ([Month])
with criteria: Between [Forms]![frReportFilter]![frMonth] And
[Forms]![frReportFilter]![toMonth]

When I run the query in the form, the filter results does not always
represent the selected period - i.e. when I select Jan to Dec, it only shows
a few of the months (namely Jan, Feb, Oct, Nov and Dec) that fall in this
period over those years. I have tried to use numbers and alphabet to
represents the months in the dropdown box but the result is the same in both
case.

Do you know what the problem is?

Regards
Sin

Duane Hookom said:
You can create a calculated column in your query like:
Mth: Month([Unnamed Date Field])
Set the criteria under this column to:
Between Forms!NoName!txtStartMth and Forms!NoName!txtEndMth

--
Duane Hookom
Microsoft Access MVP


:

I have set up an access queries with data ranging from year 2001 - 2007, the
date criteria of the query is based on a Form.

I want to set the form with Months selection only (no date or year) and when
I run the query, the program will automatically select all the data that
falls within the months selected for all year. i.e. if I select Feb to Mar,
the query will filter the data and obtain all record between Feb and Mar of
every year from 2001-2007. Is this possible? Can anyone will me any insight
or hints on how to set this up?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top