Month in Numeric & Alpha

[Guest]

I checked through the posted questions but I couldn'd find any related
answers though I might have missed some questions and answers. sorry.

I have a table with various fields, 2 of them are "MonthNum" and "Month"
I use the lookup value list for the -
(here, lets name as 1st field) MonthNum (i.e. 01; 02; ...etc. till 12)
(here, lets name as 2nd field) Month (i.e. Jan; Feb; ...etc. till Dec)

I created a form from this table, these 2 fields are on the form for users
to select from the drop-down button. Yet the user complains: if they've
selected the 1st field (MonthNum), the 2nd field (Month) should automatically
display the correct month, why they have to select the 2nd field (Month)
again on the same FORM. It's true, at times they've made mistake, e.g. 1st
field, selected 09, but by mistake, clicked the Oct in the 2nd field.
The reason I have created both fields for MonthNum and Month, I need the
numeric for the sorting purposes of the reports and the word MONTH is also
needed for printing on the REPORT for accounting use.

I think there may be such a way to do in Access, but I don't know how to do
it.
Do I have to do something with the Table or on the Form ?
I want the 2nd field (Month) entered autmatically as alpha-month after the
numeric month is entered in the 1st field (MonthNum).
If there is a way to do so, I would appreciate if you could help to
elaborate the method to me.

thanks
Karen

 

[Guest]

Hi Karen
You can display the two fields and sort on the value of the month number
Select [MonthNum], [Month] From [TableName] Order By Val([MonthNum])

That way the sort will be in a numeric order of the month number
One more thing, if you have a field named Month, consider changing it
because it a reserved name in Access, it will be OK as long that you keep it
in squeare brackets

Good luck

 

[Guest]

Thanks for the prompt response, Ofer. Again thanks for the tip about the
reserved name Month, I should make a habit not to use this kind of field name.
I don't seem to get your answer, sorry.
Sorting is not a problem for me if I have 2 fields done.
I want to have MonthNum entered but the Month will automatically
shown/entered in the table.
thanks
karen

Hi Karen
You can display the two fields and sort on the value of the month number
Select [MonthNum], [Month] From [TableName] Order By Val([MonthNum])

That way the sort will be in a numeric order of the month number
One more thing, if you have a field named Month, consider changing it
because it a reserved name in Access, it will be OK as long that you keep it
in squeare brackets

Good luck

I checked through the posted questions but I couldn'd find any related
answers though I might have missed some questions and answers. sorry.

I have a table with various fields, 2 of them are "MonthNum" and "Month"
I use the lookup value list for the -
(here, lets name as 1st field) MonthNum (i.e. 01; 02; ...etc. till 12)
(here, lets name as 2nd field) Month (i.e. Jan; Feb; ...etc. till Dec)

I created a form from this table, these 2 fields are on the form for users
to select from the drop-down button. Yet the user complains: if they've
selected the 1st field (MonthNum), the 2nd field (Month) should automatically
display the correct month, why they have to select the 2nd field (Month)
again on the same FORM. It's true, at times they've made mistake, e.g. 1st
field, selected 09, but by mistake, clicked the Oct in the 2nd field.
The reason I have created both fields for MonthNum and Month, I need the
numeric for the sorting purposes of the reports and the word MONTH is also
needed for printing on the REPORT for accounting use.

I think there may be such a way to do in Access, but I don't know how to do
it.
Do I have to do something with the Table or on the Form ?
I want the 2nd field (Month) entered autmatically as alpha-month after the
numeric month is entered in the 1st field (MonthNum).
If there is a way to do so, I would appreciate if you could help to
elaborate the method to me.

thanks
Karen

 

[Guest]

The field that is saved in the table depends on the what you define in bound
column property of the combo

So if the month is the second column
Select MonthNum, Month From TableName

Then set the BoundColumn Property of the combo to 2, that will be in the
same field
==============================
If after selection you want to display the Month in another field in the
form, there are few ways you can accomplish that
1. On the after update event of the combo, you can write
Me.[Month field name in the form] = Me.[Combo Name].Column(1)

the column number start from 0

2. In the control source of the text field, you can write
=[ComboName].column(1)
================================
I hope that I'm in the right direction ths time.

Thanks for the prompt response, Ofer. Again thanks for the tip about the
reserved name Month, I should make a habit not to use this kind of field name.
I don't seem to get your answer, sorry.
Sorting is not a problem for me if I have 2 fields done.
I want to have MonthNum entered but the Month will automatically
shown/entered in the table.
thanks
karen

Hi Karen
You can display the two fields and sort on the value of the month number
Select [MonthNum], [Month] From [TableName] Order By Val([MonthNum])

That way the sort will be in a numeric order of the month number
One more thing, if you have a field named Month, consider changing it
because it a reserved name in Access, it will be OK as long that you keep it
in squeare brackets

Good luck

I checked through the posted questions but I couldn'd find any related
answers though I might have missed some questions and answers. sorry.

I have a table with various fields, 2 of them are "MonthNum" and "Month"
I use the lookup value list for the -
(here, lets name as 1st field) MonthNum (i.e. 01; 02; ...etc. till 12)
(here, lets name as 2nd field) Month (i.e. Jan; Feb; ...etc. till Dec)

I created a form from this table, these 2 fields are on the form for users
to select from the drop-down button. Yet the user complains: if they've
selected the 1st field (MonthNum), the 2nd field (Month) should automatically
display the correct month, why they have to select the 2nd field (Month)
again on the same FORM. It's true, at times they've made mistake, e.g. 1st
field, selected 09, but by mistake, clicked the Oct in the 2nd field.
The reason I have created both fields for MonthNum and Month, I need the
numeric for the sorting purposes of the reports and the word MONTH is also
needed for printing on the REPORT for accounting use.

I think there may be such a way to do in Access, but I don't know how to do
it.
Do I have to do something with the Table or on the Form ?
I want the 2nd field (Month) entered autmatically as alpha-month after the
numeric month is entered in the 1st field (MonthNum).
If there is a way to do so, I would appreciate if you could help to
elaborate the method to me.

thanks
Karen

 

[Allen Browne]

Hi Karen

You definately don't want both the MonthNum and Month fields in your
table--for exactly the reason you experienced. Whenever you store 2 copies
of the same thing in a database, you open up a maintenance nightmare: what
happens when they don't match.

Remove the Month field from the table. Access can give you that name based
on the number. All you need to is to put this into the ControlSource of the
text box on your report:
=MonthName([Month])

That might not work properly in Access 2000 or earlier, so if it gives you a
problem, try:
=Format(DateSerial(2001, MonthNum,1), "mmmm")

If you need it as a field, you can create a query and type the expression
into a fresh column in the Field row.

That answered the question you asked, but you might like to consider whether
there could be a better design yet. What happens after the 12th month? Do
you have to create a new table? And then create all the queries, forms, and
reports as well? Even if you did have an extra field for the year, the
design does not easily let you do things like comparing one 9-month or
24-month period against the next.

A better design might be to create a related table to store the values over
time. This table would have fields:
ClientID Number relates to the client (or whatever) in your
main table.
TheMonth Date/Time first day of the month of the year.
TheValue Currency the actual value you are wanting to store.
Since this table is related to your original one (one to many), you can go
on storing a monthly value here for ever, and sum or report on any period at
all.

 

[Guest]

Great! Ofer.
After testing all everything in the DB, it works out wonderful...that I
don't have to change other fields of my table, form, reports, etc....

Thanks for the BIG HELP!
Karen

The field that is saved in the table depends on the what you define in bound
column property of the combo

So if the month is the second column
Select MonthNum, Month From TableName

Then set the BoundColumn Property of the combo to 2, that will be in the
same field
==============================
If after selection you want to display the Month in another field in the
form, there are few ways you can accomplish that
1. On the after update event of the combo, you can write
Me.[Month field name in the form] = Me.[Combo Name].Column(1)

the column number start from 0

2. In the control source of the text field, you can write
=[ComboName].column(1)
================================
I hope that I'm in the right direction ths time.

Thanks for the prompt response, Ofer. Again thanks for the tip about the
reserved name Month, I should make a habit not to use this kind of field name.
I don't seem to get your answer, sorry.
Sorting is not a problem for me if I have 2 fields done.
I want to have MonthNum entered but the Month will automatically
shown/entered in the table.
thanks
karen

Hi Karen
You can display the two fields and sort on the value of the month number
Select [MonthNum], [Month] From [TableName] Order By Val([MonthNum])

That way the sort will be in a numeric order of the month number
One more thing, if you have a field named Month, consider changing it
because it a reserved name in Access, it will be OK as long that you keep it
in squeare brackets

Good luck

:

I checked through the posted questions but I couldn'd find any related
answers though I might have missed some questions and answers. sorry.

I have a table with various fields, 2 of them are "MonthNum" and "Month"
I use the lookup value list for the -
(here, lets name as 1st field) MonthNum (i.e. 01; 02; ...etc. till 12)
(here, lets name as 2nd field) Month (i.e. Jan; Feb; ...etc. till Dec)

I created a form from this table, these 2 fields are on the form for users
to select from the drop-down button. Yet the user complains: if they've
selected the 1st field (MonthNum), the 2nd field (Month) should automatically
display the correct month, why they have to select the 2nd field (Month)
again on the same FORM. It's true, at times they've made mistake, e.g. 1st
field, selected 09, but by mistake, clicked the Oct in the 2nd field.
The reason I have created both fields for MonthNum and Month, I need the
numeric for the sorting purposes of the reports and the word MONTH is also
needed for printing on the REPORT for accounting use.

I think there may be such a way to do in Access, but I don't know how to do
it.
Do I have to do something with the Table or on the Form ?
I want the 2nd field (Month) entered autmatically as alpha-month after the
numeric month is entered in the 1st field (MonthNum).
If there is a way to do so, I would appreciate if you could help to
elaborate the method to me.

thanks
Karen

 

[Guest]

Thanks a lot!
This also helps!!!
karen

Hi Karen

You definately don't want both the MonthNum and Month fields in your
table--for exactly the reason you experienced. Whenever you store 2 copies
of the same thing in a database, you open up a maintenance nightmare: what
happens when they don't match.

Remove the Month field from the table. Access can give you that name based
on the number. All you need to is to put this into the ControlSource of the
text box on your report:
=MonthName([Month])

That might not work properly in Access 2000 or earlier, so if it gives you a
problem, try:
=Format(DateSerial(2001, MonthNum,1), "mmmm")

If you need it as a field, you can create a query and type the expression
into a fresh column in the Field row.

That answered the question you asked, but you might like to consider whether
there could be a better design yet. What happens after the 12th month? Do
you have to create a new table? And then create all the queries, forms, and
reports as well? Even if you did have an extra field for the year, the
design does not easily let you do things like comparing one 9-month or
24-month period against the next.

A better design might be to create a related table to store the values over
time. This table would have fields:
ClientID Number relates to the client (or whatever) in your
main table.
TheMonth Date/Time first day of the month of the year.
TheValue Currency the actual value you are wanting to store.
Since this table is related to your original one (one to many), you can go
on storing a monthly value here for ever, and sum or report on any period at
all.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I checked through the posted questions but I couldn'd find any related
answers though I might have missed some questions and answers. sorry.

I have a table with various fields, 2 of them are "MonthNum" and "Month"
I use the lookup value list for the -
(here, lets name as 1st field) MonthNum (i.e. 01; 02; ...etc. till 12)
(here, lets name as 2nd field) Month (i.e. Jan; Feb; ...etc. till Dec)

I created a form from this table, these 2 fields are on the form for users
to select from the drop-down button. Yet the user complains: if they've
selected the 1st field (MonthNum), the 2nd field (Month) should
automatically
display the correct month, why they have to select the 2nd field (Month)
again on the same FORM. It's true, at times they've made mistake, e.g.
1st
field, selected 09, but by mistake, clicked the Oct in the 2nd field.
The reason I have created both fields for MonthNum and Month, I need the
numeric for the sorting purposes of the reports and the word MONTH is also
needed for printing on the REPORT for accounting use.

I think there may be such a way to do in Access, but I don't know how to
do
it.
Do I have to do something with the Table or on the Form ?
I want the 2nd field (Month) entered autmatically as alpha-month after the
numeric month is entered in the 1st field (MonthNum).
If there is a way to do so, I would appreciate if you could help to
elaborate the method to me.

thanks
Karen

 

[Guest]

Glad I could help

Great! Ofer.
After testing all everything in the DB, it works out wonderful...that I
don't have to change other fields of my table, form, reports, etc....

Thanks for the BIG HELP!
Karen

The field that is saved in the table depends on the what you define in bound
column property of the combo

So if the month is the second column
Select MonthNum, Month From TableName

Then set the BoundColumn Property of the combo to 2, that will be in the
same field
==============================
If after selection you want to display the Month in another field in the
form, there are few ways you can accomplish that
1. On the after update event of the combo, you can write
Me.[Month field name in the form] = Me.[Combo Name].Column(1)

the column number start from 0

2. In the control source of the text field, you can write
=[ComboName].column(1)
================================
I hope that I'm in the right direction ths time.

Thanks for the prompt response, Ofer. Again thanks for the tip about the
reserved name Month, I should make a habit not to use this kind of field name.
I don't seem to get your answer, sorry.
Sorting is not a problem for me if I have 2 fields done.
I want to have MonthNum entered but the Month will automatically
shown/entered in the table.
thanks
karen

:

Hi Karen
You can display the two fields and sort on the value of the month number
Select [MonthNum], [Month] From [TableName] Order By Val([MonthNum])

That way the sort will be in a numeric order of the month number
One more thing, if you have a field named Month, consider changing it
because it a reserved name in Access, it will be OK as long that you keep it
in squeare brackets

Good luck

:

I checked through the posted questions but I couldn'd find any related
answers though I might have missed some questions and answers. sorry.

I have a table with various fields, 2 of them are "MonthNum" and "Month"
I use the lookup value list for the -
(here, lets name as 1st field) MonthNum (i.e. 01; 02; ...etc. till 12)
(here, lets name as 2nd field) Month (i.e. Jan; Feb; ...etc. till Dec)

I created a form from this table, these 2 fields are on the form for users
to select from the drop-down button. Yet the user complains: if they've
selected the 1st field (MonthNum), the 2nd field (Month) should automatically
display the correct month, why they have to select the 2nd field (Month)
again on the same FORM. It's true, at times they've made mistake, e.g. 1st
field, selected 09, but by mistake, clicked the Oct in the 2nd field.
The reason I have created both fields for MonthNum and Month, I need the
numeric for the sorting purposes of the reports and the word MONTH is also
needed for printing on the REPORT for accounting use.

I think there may be such a way to do in Access, but I don't know how to do
it.
Do I have to do something with the Table or on the Form ?
I want the 2nd field (Month) entered autmatically as alpha-month after the
numeric month is entered in the 1st field (MonthNum).
If there is a way to do so, I would appreciate if you could help to
elaborate the method to me.

thanks
Karen