Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Semi-date fields (2003/SP2)

    I'm new to Access but I have been trying to apply what I learned so far to a real situation and am stuck.

    I'm trying to create a database which tracks the articles in various issues of my computer clubs monthly newsletter. It seemed simple
    enough. I have 2 tables:

    ISSUES - which includes fields for the IssueDate, MonthlyEditor, NumberOfPages, etc. and
    ARTICLES - which has Title, Author, Issue, PageNumber, etc. along with the foreign key, IssueDate

    The IssueDate field (a primary key) in the ISSUE table is my problem. The newsletters are published monthly so the values are "Sept 2006",
    "Oct 2006". The day of the month is not used.

    What field type should it be? It could be TEXT or DATE. I think the field type should be DATE so reports will sort properly but how can I
    get it to accept date values which do not include the day of the month?

    Thanks in advance.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Semi-date fields (2003/SP2)

    What you could do is to use the 1st of the month, then you can use the date field.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Semi-date fields (2003/SP2)

    Thanks for the help. Actually, I thought of that and it would be OK as far as storing the data but how do I get a report to print the field as "Jan 2006" instead of "Jan, 1, 2006"? It's the formatting of the field data in reports and forms that is my problem.

    Thanks again.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Semi-date fields (2003/SP2)

    As Patt said, you can store the IssueDate as a full date with the 1st of each month. During data entry, there are 2 ways to enter the IssueDate. You could use a 99/99/99 input mask, and manually enter the a date such as "12/01/06". Or, you can leave the input mask blank and enter dates like this "Dec 2006". Just set your Format property to "mmm yyyy" in both cases.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Semi-date fields (2003/SP2)

    Mark & Patt,
    It took me a while to understand your suggestions but I think I got it. The key was realizing that the way a date field is stored in the data table (i.e. 1/1/2006) can be displayed in a different format (i.e Jan 2006) in a form or report.

    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •