Date/Time to Display only Month

[StacyM]

I have a table that feeds into a form where one of the fields is labeled
"Settleing_Month". I would like Access to read this field as a date/time
property so I can use this for sorting my report.The problem is if I add in
my own format (mmmm) into the table data type, it still wants me to enter in
the full date (3/12/09) and gives me the month back. This is fine except in
the form, users need to be able to enter the name of the month only because
the months are in a drop down list that is controlled by another field. How
do I get Access to recognize the month only as a date and allow users to
select the month from the drop down list on the form without recieving an
error message? I am quite new at Access, and any help is greatly
appreciated! Thank you.

 

[Douglas J. Steele]

You can't. Dates in Access must be complete dates.

If all you need is month, why not use a text field instead of a date field?

 

[StacyM]

I hoped to just use a text field, but I would like to be able to sort my data
on a report by month, if I do it using a text field, it sorts the months
alphabetically which is understandable but clumsy. Is there a way I could
make it sort by month non alphbetically while using a text field? Thank you
for your time.

 

[Bob Quintal]

I hoped to just use a text field, but I would like to be able to
sort my data on a report by month, if I do it using a text field,
it sorts the months alphabetically which is understandable but
clumsy. Is there a way I could make it sort by month non
alphbetically while using a text field? Thank you for your time.

Make the field in the table an integer.type of number.
Create a small lookup table with a MonthID field, integer type, and
MonthName, text.type
Create the combobox, using the table as the rowsource. Make the width
of the numeric column = 0. so all you see is the name. Make the bound
column of the combobox the numeric one. This will store the number in
the table. sort on the number and use a Dlookup or query to show the
table name in reports and other forms.

Q

 

[Douglas J. Steele]

Add a computed field to your query that has a computed field along the lines
of CDate("1 " & [YourMonthField] & " 2000") and sort on that field.

 

[StacyM]

This is what I typed for the Field in the query: Expr1: CDate("1" & [Settling
Month]& "2000"). It works when I take out the CDate part, but then it
doesn't stand as a date. When I use CDate, it gives me an error in each
record (#Error). Maybe there is a better function to use because the users
would not be typing in the current month, it should always be a month in the
future? Thank you again for helping me and remember I am really new at this.

Add a computed field to your query that has a computed field along the lines
of CDate("1 " & [YourMonthField] & " 2000") and sort on that field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I hoped to just use a text field, but I would like to be able to sort my
data
on a report by month, if I do it using a text field, it sorts the months
alphabetically which is understandable but clumsy. Is there a way I could
make it sort by month non alphbetically while using a text field? Thank
you
for your time.

 

[Douglas J. Steele]

Try with a space between the 1 and the double quote, and a space between the
double quote and 2000, as in my original suggestion.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This is what I typed for the Field in the query: Expr1: CDate("1" &
[Settling
Month]& "2000"). It works when I take out the CDate part, but then it
doesn't stand as a date. When I use CDate, it gives me an error in each
record (#Error). Maybe there is a better function to use because the
users
would not be typing in the current month, it should always be a month in
the
future? Thank you again for helping me and remember I am really new at
this.

Add a computed field to your query that has a computed field along the
lines
of CDate("1 " & [YourMonthField] & " 2000") and sort on that field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I hoped to just use a text field, but I would like to be able to sort my
data
on a report by month, if I do it using a text field, it sorts the
months
alphabetically which is understandable but clumsy. Is there a way I
could
make it sort by month non alphbetically while using a text field? Thank
you
for your time.

:

You can't. Dates in Access must be complete dates.

If all you need is month, why not use a text field instead of a date
field?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a table that feeds into a form where one of the fields is
labeled
"Settleing_Month". I would like Access to read this field as a
date/time
property so I can use this for sorting my report.The problem is if I
add
in
my own format (mmmm) into the table data type, it still wants me to
enter
in
the full date (3/12/09) and gives me the month back. This is fine
except
in
the form, users need to be able to enter the name of the month only
because
the months are in a drop down list that is controlled by another
field.
How
do I get Access to recognize the month only as a date and allow
users
to
select the month from the drop down list on the form without
recieving
an
error message? I am quite new at Access, and any help is greatly
appreciated! Thank you.

 

[StacyM]

It worked perfectly, thank you so much for your help and patience, having
this solved will make my weekend so much better! Thanks again!

Try with a space between the 1 and the double quote, and a space between the
double quote and 2000, as in my original suggestion.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This is what I typed for the Field in the query: Expr1: CDate("1" &
[Settling
Month]& "2000"). It works when I take out the CDate part, but then it
doesn't stand as a date. When I use CDate, it gives me an error in each
record (#Error). Maybe there is a better function to use because the
users
would not be typing in the current month, it should always be a month in
the
future? Thank you again for helping me and remember I am really new at
this.

Add a computed field to your query that has a computed field along the
lines
of CDate("1 " & [YourMonthField] & " 2000") and sort on that field.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I hoped to just use a text field, but I would like to be able to sort my
data
on a report by month, if I do it using a text field, it sorts the
months
alphabetically which is understandable but clumsy. Is there a way I
could
make it sort by month non alphbetically while using a text field? Thank
you
for your time.

:

You can't. Dates in Access must be complete dates.

If all you need is month, why not use a text field instead of a date
field?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a table that feeds into a form where one of the fields is
labeled
"Settleing_Month". I would like Access to read this field as a
date/time
property so I can use this for sorting my report.The problem is if I
add
in
my own format (mmmm) into the table data type, it still wants me to
enter
in
the full date (3/12/09) and gives me the month back. This is fine
except
in
the form, users need to be able to enter the name of the month only
because
the months are in a drop down list that is controlled by another
field.
How
do I get Access to recognize the month only as a date and allow
users
to
select the month from the drop down list on the form without
recieving
an
error message? I am quite new at Access, and any help is greatly
appreciated! Thank you.