Welcome to SPN

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

Sign Up Now!

Sorting Birthdate by month-day instead of by month-day-year

Discussion in 'Information Technology' started by John, Nov 7, 2005.

  1. John

    John
    Expand Collapse
    Guest

    I have a table that has a list of employees date of birth. That field is
    named "DOB". I want to create a report that prints out everyones month and
    day of birth and sorts on that instead of sorting by their month, day, and
    year.

    For example say I have the following data:

    Name: DOB:
    employee_1 1/15/1900
    employee_2 1/1/2000
    employee_3 2/1/2000

    I would like the data to be printed like the following:

    employee_2 1/1
    employee_1 1/15
    employee_3 2/1

    Currently sorting on DOB in the report yields the following though because
    employee_1 was born before employee_2

    employee_1 1/15
    employee_2 1/1
    employee_3 2/1

    I have looked and looked and tried many different things without success.
    Can anyone help me out? Thank you in advance to anyone that can help me out.

    Best Regards,
    John
     
  2. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Create a query, and add a computed field to the query: MonthDay:
    Format([DOB], "mmdd"). Sort on that field.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "John" <john[nospam]terminalreality.com> wrote in message
    news:usK73lz4FHA.4076@tk2msftngp13.phx.gbl...
    >I have a table that has a list of employees date of birth. That field is
    >named "DOB". I want to create a report that prints out everyones month and
    >day of birth and sorts on that instead of sorting by their month, day, and
    >year.
    >
    > For example say I have the following data:
    >
    > Name: DOB:
    > employee_1 1/15/1900
    > employee_2 1/1/2000
    > employee_3 2/1/2000
    >
    > I would like the data to be printed like the following:
    >
    > employee_2 1/1
    > employee_1 1/15
    > employee_3 2/1
    >
    > Currently sorting on DOB in the report yields the following though because
    > employee_1 was born before employee_2
    >
    > employee_1 1/15
    > employee_2 1/1
    > employee_3 2/1
    >
    > I have looked and looked and tried many different things without success.
    > Can anyone help me out? Thank you in advance to anyone that can help me
    > out.
    >
    > Best Regards,
    > John
    >
     
  3. JoJa15

    JoJa15
    Expand Collapse
    Guest

    That worked great! I had actually tried the format command before, but
    in my report and not in a query.

    The problem I have now is how to display the data as mm-dd. In the
    report in the format property I entered mm-dd, then I tried ##-##, then
    00"-"00 and the data still displays as mmdd or 1225 for a birthday on
    12/25/2005. I have tried this in the format property for the report and
    for the query.

    Any ideas?

    - John
     
  4. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Sun, 6 Nov 2005 19:20:42 -0600, "John"
    <john[nospam]terminalreality.com> wrote:

    >I have a table that has a list of employees date of birth. That field is
    >named "DOB". I want to create a report that prints out everyones month and
    >day of birth and sorts on that instead of sorting by their month, day, and
    >year.


    An alternative approach is to include a calculated field:

    HappyHappy: DateSerial(Year(Date()), Month([DOB]), Day([DOB]))

    This will contain the current year's birthday anniversary, and can be
    formated, sorted, searched etc. just like any other date/time field.

    John W. Vinson[MVP]
     
  5. Dlimey

    Dlimey
    Expand Collapse
    Guest

    Edit the "mmdd" to "mm"&"-"&"dd" in your Format.
    This should make it read with the 'dash' between month and day.
    The sort will still be correct.

    "JoJa15" wrote:

    > That worked great! I had actually tried the format command before, but
    > in my report and not in a query.
    >
    > The problem I have now is how to display the data as mm-dd. In the
    > report in the format property I entered mm-dd, then I tried ##-##, then
    > 00"-"00 and the data still displays as mmdd or 1225 for a birthday on
    > 12/25/2005. I have tried this in the format property for the report and
    > for the query.
    >
    > Any ideas?
    >
    > - John
    >
    >
     
  6. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Try mm\-dd

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "JoJa15" <joja15@gmail.com> wrote in message
    news:1131329881.001483.4590@g14g2000cwa.googlegroups.com...
    > That worked great! I had actually tried the format command before, but
    > in my report and not in a query.
    >
    > The problem I have now is how to display the data as mm-dd. In the
    > report in the format property I entered mm-dd, then I tried ##-##, then
    > 00"-"00 and the data still displays as mmdd or 1225 for a birthday on
    > 12/25/2005. I have tried this in the format property for the report and
    > for the query.
    >
    > Any ideas?
    >
    > - John
    >
     
  7. JoJa15

    JoJa15
    Expand Collapse
    Guest

    I tried the different formating suggestions above but oddly enough this
    is what worked in the fields format box on the report:

    -&&

    I did not get this by any knowledge but by trial and error after I
    tried the two above formating suggestions.

    - John
     

Share This Page