Using the month function?

[Chris]

Hi, I have a table that contains specific dates in each record based on an
entry by multiple users in a form. I want to add a field in the table that
will automatically convert this date to just the "month" only. For example,
if the date in a record is 2/20/2009 then I want the additional field to show
"February".

Any ideas for how to do this?

 

[Golfinray]

Month([yourfield]) will give you the number of the month, like March will be
3. Then add a table or a field in your existing table with 2 fields.
1 January
2 February
3 March, etc

 

[Chris]

That works, but when the value shows up in my form it is not writing it to
the same field in its table. How do I fix this? I created this field in my
table and brought it into my form and then entered your formula and the
correct month shows, but its not showing up in the table.

It's a calculated value based on the value in another column, so don't store
it in the table. You can't guarantee both columns will be updated when one
changes. Calculate it in queries or forms using these formulas:

In a query:
MonthPaid: Format([dtpaid],"mmmm")

In a control:
=Format([fieldname],"mmmm")

Chris

Hi, I have a table that contains specific dates in each record based on an
entry by multiple users in a form. I want to add a field in the table that
will automatically convert this date to just the "month" only. For example,
if the date in a record is 2/20/2009 then I want the additional field to show
"February".

Any ideas for how to do this?

 

[Douglas J. Steele]

There's no need to store it in the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That works, but when the value shows up in my form it is not writing it to
the same field in its table. How do I fix this? I created this field in
my
table and brought it into my form and then entered your formula and the
correct month shows, but its not showing up in the table.

It's a calculated value based on the value in another column, so don't
store
it in the table. You can't guarantee both columns will be updated when
one
changes. Calculate it in queries or forms using these formulas:

In a query:
MonthPaid: Format([dtpaid],"mmmm")

In a control:
=Format([fieldname],"mmmm")

Chris

Hi, I have a table that contains specific dates in each record based on
an
entry by multiple users in a form. I want to add a field in the table
that
will automatically convert this date to just the "month" only. For
example,
if the date in a record is 2/20/2009 then I want the additional field to
show
"February".

Any ideas for how to do this?

 

[John W. Vinson]

That works, but when the value shows up in my form it is not writing it to
the same field in its table. How do I fix this? I created this field in my
table and brought it into my form and then entered your formula and the
correct month shows, but its not showing up in the table.

The field *SHOULD NOT EXIST* in your table.

Storing derived data such as this in your table accomplishes three things: it
wastes disk space; it wastes time (almost any calculation will be MUCH faster
than a disk fetch); and most importantly, it risks data corruption. If the
date field iis subsequently edited, you will have data in your table WHICH IS
WRONG, and no automatic way to detect that fact.

Just redo the calculation whenever you need it, either as a calculated field
in a Query or in the control source of a Form or a Report textbox.