Format date "yyyy-mm" on a form

[Guest]

Hi,
I thought I had posted that question yesterday, but this morning, I can't
find it. So I post again. Here is the problem.
I have a table with 2 date fields: StartDate and EndDate. On the
corresponding form, I want the user to enter only the year and the month. I
also want the dates to be displayed in the format "yyyy-mm" no matter how
they are stored in the table.
I tried different approaches but none worked. I always get an error message.
Is that possible? Do I have to program so code to make it work?
Thank you.

 

[Duane Hookom]

You must include a day in your stored value. I would create a table of month
dates with values for each month:

tblMonths
=============
TheDate
1/1/2006
2/1/2006
3/1/2006
....

The use combo boxes with row sources like:

SELECT tblMonths.TheDate,
Format([TheDate],"yyyy-mm") AS Expr1
FROM tblMonths;

Set the number of columns to 2, bound column to 1, and column widths to
0",.75".

 

[Guest]

Hi Duane,
Thank you for this quick answer, but I am not sure that this is a good
solution for my problem. The user will have to enter dates all the way back
to 1900 or so. The data will be used to store a person's work history.
There are many other dates in the system and all are displayed in the format
"yyyy-mm-dd" and that is fine.
For that specific case, the user does not want to enter the day because it
is sort of useless. I do not mind adding the day part to the date by code but
I must first be able to enter something, a valid date in this case.
Maybe I should use an unbound field and a hidden field bound to the date in
the table. I would then check that the input is in a valid format
("yyyy-mm"), check that the input is a valid date, and if all is right, add
the day part and transfer the result to be displayed in the hidden field. The
user would only see the unbound field.
What do you think of that idea?
I will try that and come back with something.
Thank you again, Duane.
--
Jac Tremblay

You must include a day in your stored value. I would create a table of month
dates with values for each month:

tblMonths
=============
TheDate
1/1/2006
2/1/2006
3/1/2006
....

The use combo boxes with row sources like:

SELECT tblMonths.TheDate,
Format([TheDate],"yyyy-mm") AS Expr1
FROM tblMonths;

Set the number of columns to 2, bound column to 1, and column widths to
0",.75".

--
Duane Hookom
MS Access MVP
--

Hi,
I thought I had posted that question yesterday, but this morning, I can't
find it. So I post again. Here is the problem.
I have a table with 2 date fields: StartDate and EndDate. On the
corresponding form, I want the user to enter only the year and the month.
I
also want the dates to be displayed in the format "yyyy-mm" no matter how
they are stored in the table.
I tried different approaches but none worked. I always get an error
message.
Is that possible? Do I have to program so code to make it work?
Thank you.

 

[Duane Hookom]

Your "hidden" control would have been my next option.

--
Duane Hookom
MS Access MVP
--

Hi Duane,
Thank you for this quick answer, but I am not sure that this is a good
solution for my problem. The user will have to enter dates all the way
back
to 1900 or so. The data will be used to store a person's work history.
There are many other dates in the system and all are displayed in the
format
"yyyy-mm-dd" and that is fine.
For that specific case, the user does not want to enter the day because it
is sort of useless. I do not mind adding the day part to the date by code
but
I must first be able to enter something, a valid date in this case.
Maybe I should use an unbound field and a hidden field bound to the date
in
the table. I would then check that the input is in a valid format
("yyyy-mm"), check that the input is a valid date, and if all is right,
add
the day part and transfer the result to be displayed in the hidden field.
The
user would only see the unbound field.
What do you think of that idea?
I will try that and come back with something.
Thank you again, Duane.
--
Jac Tremblay

You must include a day in your stored value. I would create a table of
month
dates with values for each month:

tblMonths
=============
TheDate
1/1/2006
2/1/2006
3/1/2006
....

The use combo boxes with row sources like:

SELECT tblMonths.TheDate,
Format([TheDate],"yyyy-mm") AS Expr1
FROM tblMonths;

Set the number of columns to 2, bound column to 1, and column widths to
0",.75".

--
Duane Hookom
MS Access MVP
--

Hi,
I thought I had posted that question yesterday, but this morning, I
can't
find it. So I post again. Here is the problem.
I have a table with 2 date fields: StartDate and EndDate. On the
corresponding form, I want the user to enter only the year and the
month.
I
also want the dates to be displayed in the format "yyyy-mm" no matter
how
they are stored in the table.
I tried different approaches but none worked. I always get an error
message.
Is that possible? Do I have to program so code to make it work?
Thank you.

 

[John Spencer]

An option would be to store the string in a text field. As long as the
string was always yyyy-mm you could do most things you need to do on it
(order by, between, greater than, equal, less than, etc). You couldn't do
any of the date functions without converting the string to a date by adding
"-01" to the string and then using DateValue or CDate on the resulting
string.

 

[Guest]

Hi again Duane,
Why is it not possible in Access to display a date field on a form in a
specific format? That problem should be addressed directly by the Microsoft
architects and engineers, shouldn't it?
Thanks.
--
Jac Tremblay

Your "hidden" control would have been my next option.

--
Duane Hookom
MS Access MVP
--

Hi Duane,
Thank you for this quick answer, but I am not sure that this is a good
solution for my problem. The user will have to enter dates all the way
back
to 1900 or so. The data will be used to store a person's work history.
There are many other dates in the system and all are displayed in the
format
"yyyy-mm-dd" and that is fine.
For that specific case, the user does not want to enter the day because it
is sort of useless. I do not mind adding the day part to the date by code
but
I must first be able to enter something, a valid date in this case.
Maybe I should use an unbound field and a hidden field bound to the date
in
the table. I would then check that the input is in a valid format
("yyyy-mm"), check that the input is a valid date, and if all is right,
add
the day part and transfer the result to be displayed in the hidden field.
The
user would only see the unbound field.
What do you think of that idea?
I will try that and come back with something.
Thank you again, Duane.
--
Jac Tremblay

You must include a day in your stored value. I would create a table of
month
dates with values for each month:

tblMonths
=============
TheDate
1/1/2006
2/1/2006
3/1/2006
....

The use combo boxes with row sources like:

SELECT tblMonths.TheDate,
Format([TheDate],"yyyy-mm") AS Expr1
FROM tblMonths;

Set the number of columns to 2, bound column to 1, and column widths to
0",.75".

--
Duane Hookom
MS Access MVP
--

Hi,
I thought I had posted that question yesterday, but this morning, I
can't
find it. So I post again. Here is the problem.
I have a table with 2 date fields: StartDate and EndDate. On the
corresponding form, I want the user to enter only the year and the
month.
I
also want the dates to be displayed in the format "yyyy-mm" no matter
how
they are stored in the table.
I tried different approaches but none worked. I always get an error
message.
Is that possible? Do I have to program so code to make it work?
Thank you.

 

[Guest]

That is the best idea so far.
Thank you John.

 

[Duane Hookom]

You can set the format of the control to display a wide variety of month day
and year such as
Mar 10, 2006
03 2006
10 03 2006
3-10-2006

You must key-in a valid date but the display is very flexible.

--
Duane Hookom
MS Access MVP
--

Hi again Duane,
Why is it not possible in Access to display a date field on a form in a
specific format? That problem should be addressed directly by the
Microsoft
architects and engineers, shouldn't it?
Thanks.
--
Jac Tremblay

Your "hidden" control would have been my next option.

--
Duane Hookom
MS Access MVP
--

Hi Duane,
Thank you for this quick answer, but I am not sure that this is a good
solution for my problem. The user will have to enter dates all the way
back
to 1900 or so. The data will be used to store a person's work history.
There are many other dates in the system and all are displayed in the
format
"yyyy-mm-dd" and that is fine.
For that specific case, the user does not want to enter the day because
it
is sort of useless. I do not mind adding the day part to the date by
code
but
I must first be able to enter something, a valid date in this case.
Maybe I should use an unbound field and a hidden field bound to the
date
in
the table. I would then check that the input is in a valid format
("yyyy-mm"), check that the input is a valid date, and if all is right,
add
the day part and transfer the result to be displayed in the hidden
field.
The
user would only see the unbound field.
What do you think of that idea?
I will try that and come back with something.
Thank you again, Duane.
--
Jac Tremblay


:

You must include a day in your stored value. I would create a table of
month
dates with values for each month:

tblMonths
=============
TheDate
1/1/2006
2/1/2006
3/1/2006
....

The use combo boxes with row sources like:

SELECT tblMonths.TheDate,
Format([TheDate],"yyyy-mm") AS Expr1
FROM tblMonths;

Set the number of columns to 2, bound column to 1, and column widths
to
0",.75".

--
Duane Hookom
MS Access MVP
--

Hi,
I thought I had posted that question yesterday, but this morning, I
can't
find it. So I post again. Here is the problem.
I have a table with 2 date fields: StartDate and EndDate. On the
corresponding form, I want the user to enter only the year and the
month.
I
also want the dates to be displayed in the format "yyyy-mm" no
matter
how
they are stored in the table.
I tried different approaches but none worked. I always get an error
message.
Is that possible? Do I have to program so code to make it work?
Thank you.

 

[Guest]

Hi again, Duane,
As I posted before, I need the format to be "yyyy-mm" and nothing else. But
it is impossible to enter data in a date field if the date is not complete.
How do you set the field format to accept an input such as 1999-08?
And how do you get a date such as 1999-08-01 to display as 1999-08?
That is the question...
That is why I will stick with John Spencer's solution for now.
Thanks again.
--
Jac Tremblay

You can set the format of the control to display a wide variety of month day
and year such as
Mar 10, 2006
03 2006
10 03 2006
3-10-2006

You must key-in a valid date but the display is very flexible.

--
Duane Hookom
MS Access MVP
--

Hi again Duane,
Why is it not possible in Access to display a date field on a form in a
specific format? That problem should be addressed directly by the
Microsoft
architects and engineers, shouldn't it?
Thanks.
--
Jac Tremblay

Your "hidden" control would have been my next option.

--
Duane Hookom
MS Access MVP
--

Hi Duane,
Thank you for this quick answer, but I am not sure that this is a good
solution for my problem. The user will have to enter dates all the way
back
to 1900 or so. The data will be used to store a person's work history.
There are many other dates in the system and all are displayed in the
format
"yyyy-mm-dd" and that is fine.
For that specific case, the user does not want to enter the day because
it
is sort of useless. I do not mind adding the day part to the date by
code
but
I must first be able to enter something, a valid date in this case.
Maybe I should use an unbound field and a hidden field bound to the
date
in
the table. I would then check that the input is in a valid format
("yyyy-mm"), check that the input is a valid date, and if all is right,
add
the day part and transfer the result to be displayed in the hidden
field.
The
user would only see the unbound field.
What do you think of that idea?
I will try that and come back with something.
Thank you again, Duane.
--
Jac Tremblay


:

You must include a day in your stored value. I would create a table of
month
dates with values for each month:

tblMonths
=============
TheDate
1/1/2006
2/1/2006
3/1/2006
....

The use combo boxes with row sources like:

SELECT tblMonths.TheDate,
Format([TheDate],"yyyy-mm") AS Expr1
FROM tblMonths;

Set the number of columns to 2, bound column to 1, and column widths
to
0",.75".

--
Duane Hookom
MS Access MVP
--

Hi,
I thought I had posted that question yesterday, but this morning, I
can't
find it. So I post again. Here is the problem.
I have a table with 2 date fields: StartDate and EndDate. On the
corresponding form, I want the user to enter only the year and the
month.
I
also want the dates to be displayed in the format "yyyy-mm" no
matter
how
they are stored in the table.
I tried different approaches but none worked. I always get an error
message.
Is that possible? Do I have to program so code to make it work?
Thank you.

 

[Duane Hookom]

I just did a simple test with a text box bound to a date field. I set the
format property to:

Format: yyyy-mm

This displays 1999-08
I found it interesting that typing in "1999-08" actually stored August 1,
1999 in the field. Access added the day for me.

--
Duane Hookom
MS Access MVP
--

Hi again, Duane,
As I posted before, I need the format to be "yyyy-mm" and nothing else.
But
it is impossible to enter data in a date field if the date is not
complete.
How do you set the field format to accept an input such as 1999-08?
And how do you get a date such as 1999-08-01 to display as 1999-08?
That is the question...
That is why I will stick with John Spencer's solution for now.
Thanks again.
--
Jac Tremblay

You can set the format of the control to display a wide variety of month
day
and year such as
Mar 10, 2006
03 2006
10 03 2006
3-10-2006

You must key-in a valid date but the display is very flexible.

--
Duane Hookom
MS Access MVP
--

Hi again Duane,
Why is it not possible in Access to display a date field on a form in a
specific format? That problem should be addressed directly by the
Microsoft
architects and engineers, shouldn't it?
Thanks.
--
Jac Tremblay


:

Your "hidden" control would have been my next option.

--
Duane Hookom
MS Access MVP
--

Hi Duane,
Thank you for this quick answer, but I am not sure that this is a
good
solution for my problem. The user will have to enter dates all the
way
back
to 1900 or so. The data will be used to store a person's work
history.
There are many other dates in the system and all are displayed in
the
format
"yyyy-mm-dd" and that is fine.
For that specific case, the user does not want to enter the day
because
it
is sort of useless. I do not mind adding the day part to the date by
code
but
I must first be able to enter something, a valid date in this case.
Maybe I should use an unbound field and a hidden field bound to the
date
in
the table. I would then check that the input is in a valid format
("yyyy-mm"), check that the input is a valid date, and if all is
right,
add
the day part and transfer the result to be displayed in the hidden
field.
The
user would only see the unbound field.
What do you think of that idea?
I will try that and come back with something.
Thank you again, Duane.
--
Jac Tremblay


:

You must include a day in your stored value. I would create a table
of
month
dates with values for each month:

tblMonths
=============
TheDate
1/1/2006
2/1/2006
3/1/2006
....

The use combo boxes with row sources like:

SELECT tblMonths.TheDate,
Format([TheDate],"yyyy-mm") AS Expr1
FROM tblMonths;

Set the number of columns to 2, bound column to 1, and column
widths
to
0",.75".

--
Duane Hookom
MS Access MVP
--

Hi,
I thought I had posted that question yesterday, but this morning,
I
can't
find it. So I post again. Here is the problem.
I have a table with 2 date fields: StartDate and EndDate. On the
corresponding form, I want the user to enter only the year and
the
month.
I
also want the dates to be displayed in the format "yyyy-mm" no
matter
how
they are stored in the table.
I tried different approaches but none worked. I always get an
error
message.
Is that possible? Do I have to program so code to make it work?
Thank you.

 

[Guest]

Hi Duane,
You are quite right. All one has to do is to use the Format property on the
field in the form. And it works.
It is important to mention that there is no special setting to do on the
table side. The input mask is not helpful either.
Thank you again. That makes my day.
--
Jac Tremblay

I just did a simple test with a text box bound to a date field. I set the
format property to:

Format: yyyy-mm

This displays 1999-08
I found it interesting that typing in "1999-08" actually stored August 1,
1999 in the field. Access added the day for me.

--
Duane Hookom
MS Access MVP
--

Hi again, Duane,
As I posted before, I need the format to be "yyyy-mm" and nothing else.
But
it is impossible to enter data in a date field if the date is not
complete.
How do you set the field format to accept an input such as 1999-08?
And how do you get a date such as 1999-08-01 to display as 1999-08?
That is the question...
That is why I will stick with John Spencer's solution for now.
Thanks again.
--
Jac Tremblay

You can set the format of the control to display a wide variety of month
day
and year such as
Mar 10, 2006
03 2006
10 03 2006
3-10-2006

You must key-in a valid date but the display is very flexible.

--
Duane Hookom
MS Access MVP
--

Hi again Duane,
Why is it not possible in Access to display a date field on a form in a
specific format? That problem should be addressed directly by the
Microsoft
architects and engineers, shouldn't it?
Thanks.
--
Jac Tremblay


:

Your "hidden" control would have been my next option.

--
Duane Hookom
MS Access MVP
--

Hi Duane,
Thank you for this quick answer, but I am not sure that this is a
good
solution for my problem. The user will have to enter dates all the
way
back
to 1900 or so. The data will be used to store a person's work
history.
There are many other dates in the system and all are displayed in
the
format
"yyyy-mm-dd" and that is fine.
For that specific case, the user does not want to enter the day
because
it
is sort of useless. I do not mind adding the day part to the date by
code
but
I must first be able to enter something, a valid date in this case.
Maybe I should use an unbound field and a hidden field bound to the
date
in
the table. I would then check that the input is in a valid format
("yyyy-mm"), check that the input is a valid date, and if all is
right,
add
the day part and transfer the result to be displayed in the hidden
field.
The
user would only see the unbound field.
What do you think of that idea?
I will try that and come back with something.
Thank you again, Duane.
--
Jac Tremblay


:

You must include a day in your stored value. I would create a table
of
month
dates with values for each month:

tblMonths
=============
TheDate
1/1/2006
2/1/2006
3/1/2006
....

The use combo boxes with row sources like:

SELECT tblMonths.TheDate,
Format([TheDate],"yyyy-mm") AS Expr1
FROM tblMonths;

Set the number of columns to 2, bound column to 1, and column
widths
to
0",.75".

--
Duane Hookom
MS Access MVP
--

Hi,
I thought I had posted that question yesterday, but this morning,
I
can't
find it. So I post again. Here is the problem.
I have a table with 2 date fields: StartDate and EndDate. On the
corresponding form, I want the user to enter only the year and
the
month.
I
also want the dates to be displayed in the format "yyyy-mm" no
matter
how
they are stored in the table.
I tried different approaches but none worked. I always get an
error
message.
Is that possible? Do I have to program so code to make it work?
Thank you.