(Msg. 1) Posted: Thu Jun 18, 2009 12:45 pm
Post subject: Date/Time to Display only Month Archived from groups: microsoft>public>access (more info?)
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.
(Msg. 2) Posted: Thu Jun 18, 2009 4:45 pm
Post subject: Re: Date/Time to Display only Month [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
http://I.Am/DougSteele
(no private e-mails, please)
"StacyM" <StacyM.TakeThisOut@discussions.microsoft.com> wrote in message
news:2D689148-AF06-450A-9724-01C11F0A8D5D@microsoft.com...
>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.
(Msg. 3) Posted: Thu Jun 18, 2009 4:45 pm
Post subject: Re: Date/Time to Display only Month [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" wrote:
> 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
> http://I.Am/DougSteele > (no private e-mails, please)
>
>
> "StacyM" <StacyM.TakeThisOut@discussions.microsoft.com> wrote in message
> news:2D689148-AF06-450A-9724-01C11F0A8D5D@microsoft.com...
> >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.
>
>
>
(Msg. 4) Posted: Thu Jun 18, 2009 7:05 pm
Post subject: Re: Date/Time to Display only Month [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
=?Utf-8?B?U3RhY3lN?= <StacyM.TakeThisOut@discussions.microsoft.com> wrote in
news:2A1B7AF9-C88D-46E6-9662-931A1DB9EFAE@microsoft.com:
> 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" wrote:
>
>> 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
>> http://I.Am/DougSteele >> (no private e-mails, please)
>>
>>
>> "StacyM" <StacyM.TakeThisOut@discussions.microsoft.com> wrote in message
>> news:2D689148-AF06-450A-9724-01C11F0A8D5D@microsoft.com...
>> >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.
>>
>>
>>
>
(Msg. 5) Posted: Thu Jun 18, 2009 7:13 pm
Post subject: Re: Date/Time to Display only Month [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
http://I.Am/DougSteele
(no private e-mails, please)
"StacyM" <StacyM.TakeThisOut@discussions.microsoft.com> wrote in message
news:2A1B7AF9-C88D-46E6-9662-931A1DB9EFAE@microsoft.com...
>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" wrote:
>
>> 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
>> http://I.Am/DougSteele >> (no private e-mails, please)
>>
>>
>> "StacyM" <StacyM.TakeThisOut@discussions.microsoft.com> wrote in message
>> news:2D689148-AF06-450A-9724-01C11F0A8D5D@microsoft.com...
>> >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.
>>
>>
>>
(Msg. 6) Posted: Fri Jun 19, 2009 7:01 am
Post subject: Re: Date/Time to Display only Month [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
"Douglas J. Steele" wrote:
> 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
> http://I.Am/DougSteele > (no private e-mails, please)
>
>
> "StacyM" <StacyM.DeleteThis@discussions.microsoft.com> wrote in message
> news:2A1B7AF9-C88D-46E6-9662-931A1DB9EFAE@microsoft.com...
> >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" wrote:
> >
> >> 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
> >> http://I.Am/DougSteele > >> (no private e-mails, please)
> >>
> >>
> >> "StacyM" <StacyM.DeleteThis@discussions.microsoft.com> wrote in message
> >> news:2D689148-AF06-450A-9724-01C11F0A8D5D@microsoft.com...
> >> >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.
> >>
> >>
> >>
>
>
>
(Msg. 7) Posted: Fri Jun 19, 2009 11:56 am
Post subject: Re: Date/Time to Display only Month [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
"StacyM" <StacyM DeleteThis @discussions.microsoft.com> wrote in message
news:F3F294C8-FEF3-44F6-8E4B-6984AED0CAA1@microsoft.com...
> 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.
>
> "Douglas J. Steele" wrote:
>
>> 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
>> http://I.Am/DougSteele >> (no private e-mails, please)
>>
>>
>> "StacyM" <StacyM DeleteThis @discussions.microsoft.com> wrote in message
>> news:2A1B7AF9-C88D-46E6-9662-931A1DB9EFAE@microsoft.com...
>> >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" wrote:
>> >
>> >> 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
>> >> http://I.Am/DougSteele >> >> (no private e-mails, please)
>> >>
>> >>
>> >> "StacyM" <StacyM DeleteThis @discussions.microsoft.com> wrote in message
>> >> news:2D689148-AF06-450A-9724-01C11F0A8D5D@microsoft.com...
>> >> >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.
>> >>
>> >>
>> >>
>>
>>
>>
(Msg. 8) Posted: Fri Jun 19, 2009 11:56 am
Post subject: Re: Date/Time to Display only Month [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
It worked perfectly, thank you so much for your help and patience, having
this solved will make my weekend so much better! Thanks again!
"Douglas J. Steele" wrote:
> 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
> http://I.Am/DougSteele > (no e-mails, please!)
>
>
> "StacyM" <StacyM.DeleteThis@discussions.microsoft.com> wrote in message
> news:F3F294C8-FEF3-44F6-8E4B-6984AED0CAA1@microsoft.com...
> > 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.
> >
> > "Douglas J. Steele" wrote:
> >
> >> 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
> >> http://I.Am/DougSteele > >> (no private e-mails, please)
> >>
> >>
> >> "StacyM" <StacyM.DeleteThis@discussions.microsoft.com> wrote in message
> >> news:2A1B7AF9-C88D-46E6-9662-931A1DB9EFAE@microsoft.com...
> >> >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" wrote:
> >> >
> >> >> 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
> >> >> http://I.Am/DougSteele > >> >> (no private e-mails, please)
> >> >>
> >> >>
> >> >> "StacyM" <StacyM.DeleteThis@discussions.microsoft.com> wrote in message
> >> >> news:2D689148-AF06-450A-9724-01C11F0A8D5D@microsoft.com...
> >> >> >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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office Other