Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Month in Numeric & Alpha

Discussion in 'Information Technology' started by KarenY, Nov 13, 2005.

  1. KarenY

    KarenY
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    Monthly Fundraiser - Initial Target USD100 Global Announcements Feb 20, 2016
    Welcome to the month of Hell Blogs Oct 17, 2015
    Malaysia Second Malaysian Plane Lost in only 4 months! Breaking News Jul 18, 2014
    UK In eight months the Gurdwara has already made a big impact on Glasgow Breaking News Jan 7, 2014
    India Teenager exposes India's 'one month wives' sex tourism Breaking News Apr 17, 2013

  3. Ofer

    Ofer
    Expand Collapse
    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

    "KarenY" wrote:

    > 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
     
  4. KarenY

    KarenY
    Expand Collapse
    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

    "Ofer" wrote:

    > 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
    >
    > "KarenY" wrote:
    >
    > > 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
     
  5. Ofer

    Ofer
    Expand Collapse
    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.


    "KarenY" wrote:

    > 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
    >
    > "Ofer" wrote:
    >
    > > 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
    > >
    > > "KarenY" wrote:
    > >
    > > > 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
     
  6. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "KarenY" <KarenY@discussions.microsoft.com> wrote in message
    news:75D9164B-8066-444F-AE2A-D6AD658EC2F1@microsoft.com...
    >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
     
  7. KarenY

    KarenY
    Expand Collapse
    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

    "Ofer" wrote:

    > 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.
    >
    >
    > "KarenY" wrote:
    >
    > > 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
    > >
    > > "Ofer" wrote:
    > >
    > > > 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
    > > >
    > > > "KarenY" wrote:
    > > >
    > > > > 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
     
  8. KarenY

    KarenY
    Expand Collapse
    Guest

    Thanks a lot!
    This also helps!!!
    karen


    "Allen Browne" wrote:

    > 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.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "KarenY" <KarenY@discussions.microsoft.com> wrote in message
    > news:75D9164B-8066-444F-AE2A-D6AD658EC2F1@microsoft.com...
    > >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

    >
    >
    >
     
  9. Ofer

    Ofer
    Expand Collapse
    Guest

    Glad I could help


    "KarenY" wrote:

    > 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
    >
    > "Ofer" wrote:
    >
    > > 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.
    > >
    > >
    > > "KarenY" wrote:
    > >
    > > > 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
    > > >
    > > > "Ofer" wrote:
    > > >
    > > > > 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
    > > > >
    > > > > "KarenY" wrote:
    > > > >
    > > > > > 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
     

Share This Page