Fill "Month" field from "Date" field

[Astello]

I have a table full of shipping data, where each line has it's own date
in the format mm/dd/yyyy

I need to extract the month and put it into its own field in the same
table (ex: date field 9/29/2006, month field 9 or september)

Is there a way to do this with an update query? I know my way around
access, but i'm not great at VB.

 

[Rick B]

There is a way to pull the month out of the date field, but you don't want
to store it in a separate field. That violates normalization rules and
causes redundant (and unneeded) data in the table.

When you need the month (in a report, form, or query) just pull it out of
your date. How you do it depends on where you want to do so. In a form or
report, you can use the control's format field. Or you can add an unbound
textbox and put something like the following in the control source for that
control:

=Format([WorkDate],"MM")

or

=Format([WorkDate],"MMMM")

But, in any case, you don't write the data back to your table.

Hope that helps.

 

[raskew via AccessMonster.com]

Hi -
It's poor programming practice to store data which can be extracted on-the-
fly with a simple query. Here's a sample query that returns the month as
month as both an integer and the alpha month.

SELECT
Orders3.OrderID
, Orders3.OrderDate
, Month([orderdate]) AS MyMonth
, Format([orderdate],"mmm") AS MyMonth2
FROM
Orders3;

HTH - Bob

 

[Astello]

Thanks so much! Works perfectly for pulling the month number out of
the date field. Bravo!

Hi -
It's poor programming practice to store data which can be extracted on-the-
fly with a simple query. Here's a sample query that returns the month as
month as both an integer and the alpha month.

SELECT
Orders3.OrderID
, Orders3.OrderDate
, Month([orderdate]) AS MyMonth
, Format([orderdate],"mmm") AS MyMonth2
FROM
Orders3;

HTH - Bob

I have a table full of shipping data, where each line has it's own date
in the format mm/dd/yyyy

I need to extract the month and put it into its own field in the same
table (ex: date field 9/29/2006, month field 9 or september)

Is there a way to do this with an update query? I know my way around
access, but i'm not great at VB.--

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200701/1