Populating "Month" field from "Date" field

[Astello]

I have a table full of shipping data with a column for the date. I
need to extract the month, and put it in its own column. How should I
do this?

Thanks!

 

[Rick B]

Ummmm answered in your other post in another newsgroup.

Please stick to one post per issue.

 

[Smartin]

Astello,

Since the date field fully defines the month, you don't need to *store* an
additional field. Use the Format function to show it as you need it for
forms and/or reports by setting an Unbound textbox's ControlSource property
to:

=Format([YourDateField],"mmm") ' Displays 'Jan', 'Feb', etc.

or

= Month([YourDateField]) ' Displays '1', '2', etc.

Hope that helps.

Sprinks

At the risk of attracting flames, I will comment that it is sometimes
useful to have such derived information available in a table.

E.g., if the table is used frequently as a data source where the precise
date is not important but the month is, establishing a field with the
attenuated month (quarter, year, etc.) can improve performance.

 

[Astello]

Sorry, I posted in one place and then realized the question was more
appropriate for a different group. Thanks!

Astello,

Since the date field fully defines the month, you don't need to *store* an
additional field. Use the Format function to show it as you need it for
forms and/or reports by setting an Unbound textbox's ControlSource property
to:

=Format([YourDateField],"mmm") ' Displays 'Jan', 'Feb', etc.

or

= Month([YourDateField]) ' Displays '1', '2', etc.

Hope that helps.

SprinksAt the risk of attracting flames, I will comment that it is sometimes

useful to have such derived information available in a table.

E.g., if the table is used frequently as a data source where the precise
date is not important but the month is, establishing a field with the
attenuated month (quarter, year, etc.) can improve performance.

 

[Klatuu]

Although I would agree that there may be rare times when this is appropriate,
IMHO, this is not one of them.
--
Dave Hargis, Microsoft Access MVP

Astello,

Since the date field fully defines the month, you don't need to *store* an
additional field. Use the Format function to show it as you need it for
forms and/or reports by setting an Unbound textbox's ControlSource property
to:

=Format([YourDateField],"mmm") ' Displays 'Jan', 'Feb', etc.

or

= Month([YourDateField]) ' Displays '1', '2', etc.

Hope that helps.

Sprinks

At the risk of attracting flames, I will comment that it is sometimes
useful to have such derived information available in a table.

E.g., if the table is used frequently as a data source where the precise
date is not important but the month is, establishing a field with the
attenuated month (quarter, year, etc.) can improve performance.

 

[Tim Ferguson]

if the table is used frequently as a data source where the precise
date is not important but the month is, establishing a field with the
attenuated month (quarter, year, etc.) can improve performance.

Have you tested this with timings? Or are you just guessing that a hard
disk sector read will be faster than an integer division and an array look
up?

In any case, performance is a bit irrelevant if your database says that
12/03/2009 is in October, just because the date information was altered
after the month was calculated.

Tim F

 

[Smartin]

Have you tested this with timings? Or are you just guessing that a hard
disk sector read will be faster than an integer division and an array look
up?

In any case, performance is a bit irrelevant if your database says that
12/03/2009 is in October, just because the date information was altered
after the month was calculated.

Tim F

No, I have not tested it with timings. My proposition comes from
material I have read concerning query optimization. Paraphrasing from
memory: it is not recommended to have operations against a field in an
expression such as

[WHERE]
Month(MyDateField) = [constant]

Having said that I understand the dangers of calculated fields. Tho' I
didn't say so (and shame on me for this) I recognize that a table in
which the field that defines a calculated field is subject to updates is
not a good choice for this method.

I did say the technique is "sometimes useful", but I also said "can
improve performance". I should have said "may improve performance", with
appropriate caveats explained.

Best Regards,

 

[Smartin]

Have you tested this with timings? Or are you just guessing that a
hard disk sector read will be faster than an integer division and an
array look up?

In any case, performance is a bit irrelevant if your database says
that 12/03/2009 is in October, just because the date information was
altered after the month was calculated.
Tim F

No, I have not tested it with timings. My proposition comes from
material I have read concerning query optimization. Paraphrasing from
memory: it is not recommended to have operations against a field in an
expression such as

[WHERE]
Month(MyDateField) = [constant]

Having said that I understand the dangers of calculated fields. Tho' I
didn't say so (and shame on me for this) I recognize that a table in
which the field that defines a calculated field is subject to updates is
not a good choice for this method.

I did say the technique is "sometimes useful", but I also said "can
improve performance". I should have said "may improve performance", with
appropriate caveats explained.

Best Regards,

Sorry, it's bad form to reply to my own post, but I forgot to mention
the following damning evidence against myself...

The context in which my "advice" was given was taken from an Oracle
user's perspective. It is impossible for me to get meaningful timing
measurements in the Oracle environment, even though I work in it every
day. I did assume however that the same concepts apply in Jet/Access.