1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Extrapolate Month from an Access date field

Discussion in 'Business Applications' started by pgrobinson, Sep 27, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. pgrobinson

    pgrobinson Thread Starter

    Joined:
    Sep 27, 2005
    Messages:
    19
    from email address removed by Dreamboat

    I have a database with a date field; 'Birthday'.

    I want to take the month info from the date field and have access put it into another (adjacent) field. Ie;

    Name:
    Addr:
    Birthday:
    Month of B'day (New Field)

    Also I want to be able to query the birthday field from previous data base tables which do not have the month field and make a table with the month information extrapolated to a new 'Month of B'day' field.

    (Month([Birthday])) does not work.

    I have printed a report with the 'Month of B'day" info extrapolated to the report but it is not useful because the report does not sort by the new information.

    I know these must be possible but . . . . . AAAaaagh I'm going nuts. :eek:
     
  2. Sponsor

  3. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,744
    You don't need to do that. Don't make a new field. Access isn't like Excel. Just put the SAME field, and format it as MMMM
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    63,423
    i would edit your post and remove your email address to avoid getting loads of spam
     
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,744
    Thanks, etaf! I meant to do that and forgot. :) I've removed your email address, pgrobinson.
     
  6. pgrobinson

    pgrobinson Thread Starter

    Joined:
    Sep 27, 2005
    Messages:
    19
    OK Question.

    So I insert another Date field in the database;
    format that field mmmm

    How do I get the second Date field to read the info from the Birthday field
    or
    Do I have to enter the date information twice. (sounds silly, I'm sure that's not so?)
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,744
    No. You don't NEED another date field. But on your form or report, you REPEAT that field (copy/paste is fine), then change the format of the control to MMMM.

    See the attached sample FORM (you don't do this in a table, otherwise you're unnecessarily storing additional data).
     

    Attached Files:

  8. pgrobinson

    pgrobinson Thread Starter

    Joined:
    Sep 27, 2005
    Messages:
    19
    Thanks again

    but I want to be able to put the info into a table, maybe via a query, so I can sort by month.
     
  9. pgrobinson

    pgrobinson Thread Starter

    Joined:
    Sep 27, 2005
    Messages:
    19
    Oh! I made my donation to the site. Thank you again
     
  10. pgrobinson

    pgrobinson Thread Starter

    Joined:
    Sep 27, 2005
    Messages:
    19
    Aaaaaah, I think I get it. (I am not at my computer until tomorrow)

    Do I just pull up the table,
    go to design mode
    select the field
    and change formatting from the ... drop down menu to mmmm
    and go back to view mode to see the months.

    Then when I want to review the full dates do the reverse.

    THIS IS ALL A QUESTION

    pgr
     
  11. pgrobinson

    pgrobinson Thread Starter

    Joined:
    Sep 27, 2005
    Messages:
    19
    Can I make the field from my data entr form; enter the date into 2 table fields??

    I'm getting excited here.
     
  12. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,744
    PG: That is just BAD data design. What you are doing is asking Access to store the SAME value two times instead of one.

    Instead, learn how to sort properly. What I want to know is why it won't sort properly for you now? Why would the date sort differently whether it's a date or a month that you're looking at? How is it sorting now that's wrong?
     
  13. pgrobinson

    pgrobinson Thread Starter

    Joined:
    Sep 27, 2005
    Messages:
    19
    Currently my table sorts by year so

    all the 1900 dates
    then all the 1901
    1902 etc

    what I want is All the April then all the December etc

    or PREFERABLYb all the Month 1 (Jan) then all the month 2 (Feb) etc
    using number identifiers for the months instead of 3 character names.

    I just uploaded my mdb file and now i see that it is not listed as a supported file extension. I am going to go ahead and submit it anyhow

    pg
     
  14. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Just a note, you'll have to zip your .mdb file using WinZip or some other compression utility.

    I've been following this thread and I still can't get what you're after...if you need more help concerning zipping your database file, let us know. Before you do anything, you should make a copy of your DB and delete any confidential or revealing records, then zip that file and post it.

    chris.
     
  15. pgrobinson

    pgrobinson Thread Starter

    Joined:
    Sep 27, 2005
    Messages:
    19
    In the table; The date sorts chronologically 1900, 1901, 1902 etc whether I have the short date format or the mmmm format.
     
  16. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Oi, I think I might understand what you're getting at. Do you want to sort your table by month, so that at the top of the recordset are all the records that have a January birthday, then all the records with a February birthday, etc.?

    chris.
     
  17. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/402593