Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Olympia, WA, Washington, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    tThree fields for one date (Access 2000 sr3)

    I think I saw something about this months ago. I have conveted a Works database to Access (that was a job in itself). It is a very simple database with 37 fields and over 13000 records. The problem is the designer used three fields for dates. One field for Month one for day and one for year. They use the standard numerical equivalent for the months and all year fields contain 4 digits. This is a cemetery record. My question is can I combine the three fields into one and make it a standard date field. the Month and day field may contain 1 or 2 digits (no leading zero). I would like to wind up with MM/DD/YYYY. I have used an update query to combine them but wind up with some fields with as few as 6 digits and some with 7 and some with 8. I think I would have to add a leading 0 to the fields with only one digit but don't know how. 13000 records is too much to do manually. BTW there are three separate date fields combos. One for date intered one for date of death and one for date of birth. All three use the same three field format.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: tThree fields for one date (Access 2000 sr3)

    Access has a DateSerial function which will combine the three fields into one very nicely. As I recall, it looks something like:
    <font color=blue> DateSerial(YearField, MonthField, DayField)</font color=blue>
    Check the Help file, or use the builder to be sure, but it creates a standard Date/Time formatted number and should do exactly what you want with either an update query or a make table query.
    Wendell

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tThree fields for one date (Access 2000 sr3)

    Wendell has posted how you can combine the three into one field. To view/display you can set the formatting of the field on your entry form to display dates (in a variety of standard ways). You can set the format in the table and it will use this as the default when you create a new form/report control based on that field - it doesnt actually alter how the data is stored in the table.

    Another point. Your post implies everything is stored in one table. That may well be inefficient, whether it's worth your effort to optimise it is another question.

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Olympia, WA, Washington, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tThree fields for one date (Access 2000 sr3)

    Thanks for the responses. Displaying dates is something I was aware of. It was combining the fields that was the problem. Yes, everything is in a single table. Only three dates but hey she has this set up with two fields one for male and one for female and just put an x in the appropriate field then lost the male field completely (possibly deleted accidently). I have not tried the solution above yet but have run into another problem. These fields were all text fields and in converting to numeric I ran into a stopper in three of them. It said it could convert all but one record. I did notice a couple year dates that looked like '2002 or 2002' they had an apostrophe added (typo). Is there some way to search and find the one record in each of these fields for a non numeric character? Okay, I will leave you alone now [img]/forums/images/smilies/smile.gif[/img] and Thanks.

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Olympia, WA, Washington, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tThree fields for one date (Access 2000 sr3)

    Eureka! DateSerial was just the ticket. I converted all three sets of fields into one date field each. However, I may have to leave it in the other format. She didn't use the normal date format because we don't have full info on all people. On some just a year of birth, on some the month and year of interment but not the day. This cemetery dates back to 1852 so some info is sparse. The cemetery manager wants to post these records on the internet in a searchable form.

Posting Permissions

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