If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Populating "Month" field from "Date" field
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! |
#2
|
|||
|
|||
Populating "Month" field from "Date" field
Ummmm answered in your other post in another newsgroup.
Please stick to one post per issue. -- Rick B "Astello" wrote in message oups.com... 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! |
#3
|
|||
|
|||
Populating "Month" field from "Date" field
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 "Astello" wrote: 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! |
#4
|
|||
|
|||
Populating "Month" field from "Date" field
Sprinks wrote:
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. -- Smartin |
#5
|
|||
|
|||
Populating "Month" field from "Date" field
Sorry, I posted in one place and then realized the question was more
appropriate for a different group. Thanks! On Jan 24, 8:54 pm, Smartin wrote: Sprinks wrote: 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. -- Smartin- Hide quoted text -- Show quoted text - |
#6
|
|||
|
|||
Populating "Month" field from "Date" field
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 "Smartin" wrote: Sprinks wrote: 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. -- Smartin |
#7
|
|||
|
|||
Populating "Month" field from "Date" field
Smartin wrote in news:O#Y1UPCQHHA.4992
@TK2MSFTNGP04.phx.gbl: 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 |
#8
|
|||
|
|||
Populating "Month" field from "Date" field
Tim Ferguson wrote:
Smartin wrote in news:O#Y1UPCQHHA.4992 @TK2MSFTNGP04.phx.gbl: 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 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 |
#9
|
|||
|
|||
Populating "Month" field from "Date" field
Smartin wrote:
Tim Ferguson wrote: Smartin wrote in news:O#Y1UPCQHHA.4992 @TK2MSFTNGP04.phx.gbl: 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 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. -- Smartin |
Thread Tools | |
Display Modes | |
|
|