help with date query

[steve goodrich]

I have a date field on my form (bound to a table) - What is the best way to
list all dates in
a given month?. I know I could do a parameter query and enter the first and
last dates but Ideally I would like a drop down combo box on my form with a
list of all 12 months.
Selecting a month would run a query/report showing all dates in
that month. Am I right to think that I would need a calculated field in my
query and the criteria pointing to a unbound combo box on the form something
like:
[forms]![frmqueries].[comboMonth]

Also would I create a new table to list the months and use the table to
populate the combo box?

How would I enter the text in the calculated field if the above is the right
approach?

Does any of the above make sense or am I way off base?

Any help would be appreciated

Steve

 

[Douglas J. Steele]

Set comboMonth up so that it returns the month number (1, 2, 3, ...) even
though it displays the month description (January, February, March, ...)

Set your criteria as:

BETWEEN DateSerial(Year(Date), [forms]![frmqueries].[comboMonth], 1)
AND DateSerial(Year(Date), [forms]![frmqueries].[comboMonth] + 1, 0)

Alternatively, add a computed field Month([YourDateField]) to the query (you
don't have to display it...), and set the criteria for that computed field
to [forms]![frmqueries].[comboMonth]

The first approach is preferable, because it doesn't involve executing the
Month function for each row in the table.

 

[steve goodrich]

I'm struggling with the combo box. I just want to display the month ie Jan
- Dec.

What am I doing wrong?

I created a new table with two columns
One column list numbers 1 to 12 and the other lists months Jan to Dec

I created the unbound combo box on my form using my new table as the
source. I set the criteria to point to the combo box. Tried using both
columns and each one in turn.
if my combo box shows both columns then the query runs ok,

How do I get the combo box to show just the months.

Set comboMonth up so that it returns the month number (1, 2, 3, ...) even
though it displays the month description (January, February, March, ...)

Set your criteria as:

BETWEEN DateSerial(Year(Date), [forms]![frmqueries].[comboMonth], 1)
AND DateSerial(Year(Date), [forms]![frmqueries].[comboMonth] + 1, 0)

Alternatively, add a computed field Month([YourDateField]) to the query
(you don't have to display it...), and set the criteria for that computed
field to [forms]![frmqueries].[comboMonth]

The first approach is preferable, because it doesn't involve executing the
Month function for each row in the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a date field on my form (bound to a table) - What is the best way
to list all dates in
a given month?. I know I could do a parameter query and enter the first
and last dates but Ideally I would like a drop down combo box on my form
with a list of all 12 months.
Selecting a month would run a query/report showing all dates in
that month. Am I right to think that I would need a calculated field in
my query and the criteria pointing to a unbound combo box on the form
something like:
[forms]![frmqueries].[comboMonth]

Also would I create a new table to list the months and use the table to
populate the combo box?

How would I enter the text in the calculated field if the above is the
right approach?

Does any of the above make sense or am I way off base?

Any help would be appreciated

Steve

 

[Steve]

Hi Steve,

Create a query based on your table. Pull the number field down into the
first field and the month name field down into the second field. Set the
sort on the number field to ascending. Go to design view of your form and
select the combobox. Open properties and go to the Data tab. Set the
rowsource property to your query. Still under the Data tab, set the Bound
Column property to 1. Go to the Format tab. Set Column Count to 2 and set
Column Width to 0;1. Close Properties. Close your form to save changes. Open
your form and try the combobox; you should display the name of the month but
save the month number for the selected month.

Steve
[email protected]

I'm struggling with the combo box. I just want to display the month ie
Jan
- Dec.

What am I doing wrong?

I created a new table with two columns
One column list numbers 1 to 12 and the other lists months Jan to Dec

I created the unbound combo box on my form using my new table as the
source. I set the criteria to point to the combo box. Tried using both
columns and each one in turn.
if my combo box shows both columns then the query runs ok,

How do I get the combo box to show just the months.

Set comboMonth up so that it returns the month number (1, 2, 3, ...) even
though it displays the month description (January, February, March, ...)

Set your criteria as:

BETWEEN DateSerial(Year(Date), [forms]![frmqueries].[comboMonth], 1)
AND DateSerial(Year(Date), [forms]![frmqueries].[comboMonth] + 1, 0)

Alternatively, add a computed field Month([YourDateField]) to the query
(you don't have to display it...), and set the criteria for that computed
field to [forms]![frmqueries].[comboMonth]

The first approach is preferable, because it doesn't involve executing
the Month function for each row in the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a date field on my form (bound to a table) - What is the best way
to list all dates in
a given month?. I know I could do a parameter query and enter the first
and last dates but Ideally I would like a drop down combo box on my form
with a list of all 12 months.
Selecting a month would run a query/report showing all dates in
that month. Am I right to think that I would need a calculated field in
my query and the criteria pointing to a unbound combo box on the form
something like:
[forms]![frmqueries].[comboMonth]

Also would I create a new table to list the months and use the table to
populate the combo box?

How would I enter the text in the calculated field if the above is the
right approach?

Does any of the above make sense or am I way off base?

Any help would be appreciated

Steve

 

[steve goodrich]

Steve,
Thanks for you detailed reply - I've nearly got it!!

I followed your instructions and it does work, however, the months listed in
the combo box are out of order. Not sure where I've gone wrong.
My numbers in my table are numbered from 1 to 12 and when sorted asc they
sort 1,10,11,12,,2,3,4,5,6,7,8,9
This is the order the months are sorted in the combo box. i.e
Jan,Oct,Nov,Dec.Feb etc.

I tried altering the numbers in the table to 01,02,etc. and that worked when
sorting them in order but when using the comboo box, the months were in the
correct order but running the query returned no results.

Please help!!

Steve

Hi Steve,

Create a query based on your table. Pull the number field down into the
first field and the month name field down into the second field. Set the
sort on the number field to ascending. Go to design view of your form and
select the combobox. Open properties and go to the Data tab. Set the
rowsource property to your query. Still under the Data tab, set the Bound
Column property to 1. Go to the Format tab. Set Column Count to 2 and set
Column Width to 0;1. Close Properties. Close your form to save changes.
Open your form and try the combobox; you should display the name of the
month but save the month number for the selected month.

Steve
[email protected]

I'm struggling with the combo box. I just want to display the month ie
Jan
- Dec.

What am I doing wrong?

I created a new table with two columns
One column list numbers 1 to 12 and the other lists months Jan to Dec

I created the unbound combo box on my form using my new table as the
source. I set the criteria to point to the combo box. Tried using both
columns and each one in turn.
if my combo box shows both columns then the query runs ok,

How do I get the combo box to show just the months.

Set comboMonth up so that it returns the month number (1, 2, 3, ...)
even though it displays the month description (January, February, March,
...)

Set your criteria as:

BETWEEN DateSerial(Year(Date), [forms]![frmqueries].[comboMonth], 1)
AND DateSerial(Year(Date), [forms]![frmqueries].[comboMonth] + 1, 0)

Alternatively, add a computed field Month([YourDateField]) to the query
(you don't have to display it...), and set the criteria for that
computed field to [forms]![frmqueries].[comboMonth]

The first approach is preferable, because it doesn't involve executing
the Month function for each row in the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a date field on my form (bound to a table) - What is the best way
to list all dates in
a given month?. I know I could do a parameter query and enter the first
and last dates but Ideally I would like a drop down combo box on my
form with a list of all 12 months.
Selecting a month would run a query/report showing all dates in
that month. Am I right to think that I would need a calculated field
in my query and the criteria pointing to a unbound combo box on the
form something like:
[forms]![frmqueries].[comboMonth]

Also would I create a new table to list the months and use the table to
populate the combo box?

How would I enter the text in the calculated field if the above is the
right approach?

Does any of the above make sense or am I way off base?

Any help would be appreciated

Steve

 

[Gina Whipp]

Steve,

The field you have as the Month Number in your table... that field should be
Numeric not Text. (Text will always sort like that.) Is there some reason
you need to have it Text?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Steve,
Thanks for you detailed reply - I've nearly got it!!

I followed your instructions and it does work, however, the months listed
in the combo box are out of order. Not sure where I've gone wrong.
My numbers in my table are numbered from 1 to 12 and when sorted asc they
sort 1,10,11,12,,2,3,4,5,6,7,8,9
This is the order the months are sorted in the combo box. i.e
Jan,Oct,Nov,Dec.Feb etc.

I tried altering the numbers in the table to 01,02,etc. and that worked
when sorting them in order but when using the comboo box, the months were
in the correct order but running the query returned no results.

Please help!!

Steve

Hi Steve,

Create a query based on your table. Pull the number field down into the
first field and the month name field down into the second field. Set the
sort on the number field to ascending. Go to design view of your form and
select the combobox. Open properties and go to the Data tab. Set the
rowsource property to your query. Still under the Data tab, set the Bound
Column property to 1. Go to the Format tab. Set Column Count to 2 and set
Column Width to 0;1. Close Properties. Close your form to save changes.
Open your form and try the combobox; you should display the name of the
month but save the month number for the selected month.

Steve
[email protected]

I'm struggling with the combo box. I just want to display the month ie
Jan
- Dec.

What am I doing wrong?

I created a new table with two columns
One column list numbers 1 to 12 and the other lists months Jan to Dec

I created the unbound combo box on my form using my new table as the
source. I set the criteria to point to the combo box. Tried using both
columns and each one in turn.
if my combo box shows both columns then the query runs ok,

How do I get the combo box to show just the months.


Set comboMonth up so that it returns the month number (1, 2, 3, ...)
even though it displays the month description (January, February,
March, ...)

Set your criteria as:

BETWEEN DateSerial(Year(Date), [forms]![frmqueries].[comboMonth], 1)
AND DateSerial(Year(Date), [forms]![frmqueries].[comboMonth] + 1, 0)

Alternatively, add a computed field Month([YourDateField]) to the query
(you don't have to display it...), and set the criteria for that
computed field to [forms]![frmqueries].[comboMonth]

The first approach is preferable, because it doesn't involve executing
the Month function for each row in the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a date field on my form (bound to a table) - What is the best
way to list all dates in
a given month?. I know I could do a parameter query and enter the
first and last dates but Ideally I would like a drop down combo box on
my form with a list of all 12 months.
Selecting a month would run a query/report showing all dates in
that month. Am I right to think that I would need a calculated field
in my query and the criteria pointing to a unbound combo box on the
form something like:
[forms]![frmqueries].[comboMonth]

Also would I create a new table to list the months and use the table
to populate the combo box?

How would I enter the text in the calculated field if the above is the
right approach?

Does any of the above make sense or am I way off base?

Any help would be appreciated

Steve

 

[steve goodrich]

Gina,
Thanks for the advice - It was set as text. Changed it to number and
everything works great
Many Thanks to you both
Steve

Steve,

The field you have as the Month Number in your table... that field should
be Numeric not Text. (Text will always sort like that.) Is there some
reason you need to have it Text?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Steve,
Thanks for you detailed reply - I've nearly got it!!

I followed your instructions and it does work, however, the months listed
in the combo box are out of order. Not sure where I've gone wrong.
My numbers in my table are numbered from 1 to 12 and when sorted asc they
sort 1,10,11,12,,2,3,4,5,6,7,8,9
This is the order the months are sorted in the combo box. i.e
Jan,Oct,Nov,Dec.Feb etc.

I tried altering the numbers in the table to 01,02,etc. and that worked
when sorting them in order but when using the comboo box, the months were
in the correct order but running the query returned no results.

Please help!!

Steve

Hi Steve,

Create a query based on your table. Pull the number field down into the
first field and the month name field down into the second field. Set the
sort on the number field to ascending. Go to design view of your form
and select the combobox. Open properties and go to the Data tab. Set the
rowsource property to your query. Still under the Data tab, set the
Bound Column property to 1. Go to the Format tab. Set Column Count to 2
and set Column Width to 0;1. Close Properties. Close your form to save
changes. Open your form and try the combobox; you should display the
name of the month but save the month number for the selected month.

Steve
[email protected]


I'm struggling with the combo box. I just want to display the month ie
Jan
- Dec.

What am I doing wrong?

I created a new table with two columns
One column list numbers 1 to 12 and the other lists months Jan to Dec

I created the unbound combo box on my form using my new table as the
source. I set the criteria to point to the combo box. Tried using both
columns and each one in turn.
if my combo box shows both columns then the query runs ok,

How do I get the combo box to show just the months.


Set comboMonth up so that it returns the month number (1, 2, 3, ...)
even though it displays the month description (January, February,
March, ...)

Set your criteria as:

BETWEEN DateSerial(Year(Date), [forms]![frmqueries].[comboMonth], 1)
AND DateSerial(Year(Date), [forms]![frmqueries].[comboMonth] + 1, 0)

Alternatively, add a computed field Month([YourDateField]) to the
query (you don't have to display it...), and set the criteria for that
computed field to [forms]![frmqueries].[comboMonth]

The first approach is preferable, because it doesn't involve executing
the Month function for each row in the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a date field on my form (bound to a table) - What is the best
way to list all dates in
a given month?. I know I could do a parameter query and enter the
first and last dates but Ideally I would like a drop down combo box
on my form with a list of all 12 months.
Selecting a month would run a query/report showing all dates in
that month. Am I right to think that I would need a calculated field
in my query and the criteria pointing to a unbound combo box on the
form something like:
[forms]![frmqueries].[comboMonth]

Also would I create a new table to list the months and use the table
to populate the combo box?

How would I enter the text in the calculated field if the above is
the right approach?

Does any of the above make sense or am I way off base?

Any help would be appreciated

Steve

 

[Steve]

Sreve,

Go to your table in design view. Select the number field and change the data
type to Number. At the bottom, change the Format to Integer.

Steve

Steve,
Thanks for you detailed reply - I've nearly got it!!

I followed your instructions and it does work, however, the months listed
in the combo box are out of order. Not sure where I've gone wrong.
My numbers in my table are numbered from 1 to 12 and when sorted asc they
sort 1,10,11,12,,2,3,4,5,6,7,8,9
This is the order the months are sorted in the combo box. i.e
Jan,Oct,Nov,Dec.Feb etc.

I tried altering the numbers in the table to 01,02,etc. and that worked
when sorting them in order but when using the comboo box, the months were
in the correct order but running the query returned no results.

Please help!!

Steve

Hi Steve,

Create a query based on your table. Pull the number field down into the
first field and the month name field down into the second field. Set the
sort on the number field to ascending. Go to design view of your form and
select the combobox. Open properties and go to the Data tab. Set the
rowsource property to your query. Still under the Data tab, set the Bound
Column property to 1. Go to the Format tab. Set Column Count to 2 and set
Column Width to 0;1. Close Properties. Close your form to save changes.
Open your form and try the combobox; you should display the name of the
month but save the month number for the selected month.

Steve
[email protected]

I'm struggling with the combo box. I just want to display the month ie
Jan
- Dec.

What am I doing wrong?

I created a new table with two columns
One column list numbers 1 to 12 and the other lists months Jan to Dec

I created the unbound combo box on my form using my new table as the
source. I set the criteria to point to the combo box. Tried using both
columns and each one in turn.
if my combo box shows both columns then the query runs ok,

How do I get the combo box to show just the months.


Set comboMonth up so that it returns the month number (1, 2, 3, ...)
even though it displays the month description (January, February,
March, ...)

Set your criteria as:

BETWEEN DateSerial(Year(Date), [forms]![frmqueries].[comboMonth], 1)
AND DateSerial(Year(Date), [forms]![frmqueries].[comboMonth] + 1, 0)

Alternatively, add a computed field Month([YourDateField]) to the query
(you don't have to display it...), and set the criteria for that
computed field to [forms]![frmqueries].[comboMonth]

The first approach is preferable, because it doesn't involve executing
the Month function for each row in the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a date field on my form (bound to a table) - What is the best
way to list all dates in
a given month?. I know I could do a parameter query and enter the
first and last dates but Ideally I would like a drop down combo box on
my form with a list of all 12 months.
Selecting a month would run a query/report showing all dates in
that month. Am I right to think that I would need a calculated field
in my query and the criteria pointing to a unbound combo box on the
form something like:
[forms]![frmqueries].[comboMonth]

Also would I create a new table to list the months and use the table
to populate the combo box?

How would I enter the text in the calculated field if the above is the
right approach?

Does any of the above make sense or am I way off base?

Any help would be appreciated

Steve