Results 1 to 6 of 6
  1. #1
    Papaduok is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2016
    Location
    Oklahoma, USA
    Posts
    2

    Formatting date fields in table

    I am relatively new to Access and trying to create a table to input the interments of local cemeteries for my genealogy club. Setting up 'Date of Birth' and 'Date of Death' fields work well when you have a complete date, i.e. dd mmm yyyy, but if you just have the year only I can't figure out how to format the field to accept yyyy only without reformatting the entire table. I would like to have the option of entering the complete date, month and year, or year only. If this is possible how do I set the format for date fields?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I would probably set up multiple columns to store a single date. In other words, have a column for month, a column for date, and a column for year. Code could take the values from each and, if each column has a value, concatenate them and format the result for display purposes.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to ItsMe's comment, I would advise you to not use a naming convention that allows spaces in names. These will come back to haunt you with syntax issues. Use alphanumeric and "_" characters only.

    BirthDate or DateOfBirth type of name

    Do you have an overview description of things involved in your genealogy related database. Often a good idea to write a description of what the proposed system will do; what info is expected to be available.....

    This free data model may be useful to you.
    The facts concerning the data model are here.

    Good luck with your project.
    Last edited by orange; 08-19-2016 at 07:10 PM. Reason: added facts link

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    It's like the Twilight Zone. queue music...

  5. #5
    Papaduok is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2016
    Location
    Oklahoma, USA
    Posts
    2

    Formatting 'Date' fields in Table

    Thanks for your suggestions. Using the suggestion from ItsMe I spread the BirthDate and DeathDate fields out to 3 fields each. One for Day, one for Month and one for Year. Found out that using DATE/TIME for the Data Type you can't get away with entering just the day or month or year. Believe I have to change these fields to NUMBER for the Data Type. The issue of how to print these dates in a report then comes into play. I need to then learn how to pull information in 3 fields, truncate it, and put together so it looks like a normal date. A lot to learn.

    My database for this project just has the following fields: Section; Block; Lot; Space (all numbers no larger than 4 digits); Name (last, first middle); Date of Birth; Date of Death; Headstone Photo (Y/N); Remarks. There is no reason to associate each entry with another as we are just building a record of who is buried in the cemetery.

    Quote Originally Posted by orange View Post
    Further to ItsMe's comment, I would advise you to not use a naming convention that allows spaces in names. These will come back to haunt you with syntax issues. Use alphanumeric and "_" characters only.

    BirthDate or DateOfBirth type of name

    Do you have an overview description of things involved in your genealogy related database. Often a good idea to write a description of what the proposed system will do; what info is expected to be available.....

    This free data model may be useful to you.
    The facts concerning the data model are here.

    Good luck with your project.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I would advise you to not use a naming convention that allows spaces in names.
    and don't use name, month and year as your field names - they are all reserved words. Using them as field or control names is likely to cause problems down the line. See this link

    https://support.office.com/en-us/art...ad=US&fromAR=1

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Formatting Text fields in table
    By jercook in forum Access
    Replies: 2
    Last Post: 03-28-2015, 09:23 PM
  2. Replies: 3
    Last Post: 12-02-2014, 07:58 PM
  3. Replies: 0
    Last Post: 02-22-2013, 02:13 AM
  4. Replies: 34
    Last Post: 12-01-2011, 08:18 AM
  5. one date entered to hit 3 fields in one table how?
    By techexpressinc in forum Queries
    Replies: 0
    Last Post: 12-10-2008, 10:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums