Help using dates as in birthdates

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • emandel
    New Member
    • Dec 2006
    • 65

    Help using dates as in birthdates

    I am an armature access programmer trying my best to design my own database so your patience and detailed instructions is much appreciated.

    I have birthdates in my "participan ts" table

    1) How do I set the criteria so that I only see people that are older than x
    2) in a form, how do I add a calculated field so that it shows me how old the person is as of today?
  • emandel
    New Member
    • Dec 2006
    • 65

    #2
    Anyone out there to help?

    Comment

    • MMcCarthy
      Recognized Expert MVP
      • Aug 2006
      • 14387

      #3
      Originally posted by emandel
      I am an armature access programmer trying my best to design my own database so your patience and detailed instructions is much appreciated.

      I have birthdates in my "participan ts" table

      1) How do I set the criteria so that I only see people that are older than x
      This would be done using a query. Assuming birthdate is the field name and age restriction is 35.

      Code:
       
      SELECT * FROM Tablename
      WHERE DateDiff('yyyy', Now(), [birthdate]) > 35;
      Originally posted by emandel
      2) in a form, how do I add a calculated field so that it shows me how old the person is as of today?
      Make the control source of the textbox

      Code:
      =DateDiff ('yyyy',  Now(), [birthdate])

      Comment

      • emandel
        New Member
        • Dec 2006
        • 65

        #4
        Make the control source of the textbox

        Code:
        =DateDiff ('yyyy', Now(), [birthdate])

        ----------------------

        Thank you
        2 problems,

        1) why is there a minus sign before the age?
        2) it only calculates it based on the birthyear which means it isn't always accurate (it could be up to 364 days off)

        Comment

        • emandel
          New Member
          • Dec 2006
          • 65

          #5
          This would be done using a query. Assuming birthdate is the field name and age restriction is 35.

          Code:

          SELECT * FROM Tablename
          WHERE DateDiff('yyyy' , Now(), [birthdate]) > 35;
          can this be done as a filter within the table under criterea?
          How can it be done?

          Comment

          • MMcCarthy
            Recognized Expert MVP
            • Aug 2006
            • 14387

            #6
            Originally posted by emandel
            Make the control source of the textbox

            Code:
            =DateDiff ('yyyy', Now(), [birthdate])

            ----------------------

            Thank you
            2 problems,

            1) why is there a minus sign before the age?
            Try reversing the code as follows:

            =DateDiff ('yyyy', [birthdate], Now())

            Originally posted by emandel
            2) it only calculates it based on the birthyear which means it isn't always accurate (it could be up to 364 days off)
            No it will look at the dates and calculate the age based on all complete years.

            e.g. based on dd/mm/yyyy formats

            Code:
            DateDiff ('yyyy', #12/10/2000#, #12/09/2006#) = 5
            but

            Code:
            DateDiff ('yyyy', #12/10/2000#, #12/11/2006#) = 6
            Mary

            Comment

            • MMcCarthy
              Recognized Expert MVP
              • Aug 2006
              • 14387

              #7
              Originally posted by emandel
              can this be done as a filter within the table under criterea?
              How can it be done?
              You cannot filter the table directly. Why can't you use a query to do it? Is there some requirement that isn't satisfied?

              Mary

              Comment

              • emandel
                New Member
                • Dec 2006
                • 65

                #8
                Try reversing the code as follows:

                =DateDiff ('yyyy', [birthdate], Now())

                Quote:
                Originally Posted by emandel
                2) it only calculates it based on the birthyear which means it isn't always accurate (it could be up to 364 days off)


                No it will look at the dates and calculate the age based on all complete years.

                e.g. based on dd/mm/yyyy formats

                Code:
                DateDiff ('yyyy', #12/10/2000#, #12/09/2006#) = 5


                but

                Code:
                DateDiff ('yyyy', #12/10/2000#, #12/11/2006#) = 6
                OK the minus sign is gone, but it is still giving me the wrong calculation.

                I have my bithday as July 15 1973 which means that I am not 34 until July 15 2007, yet in the field that i just created it shows it as 34 as of now already. Any suggestons?

                Comment

                • MMcCarthy
                  Recognized Expert MVP
                  • Aug 2006
                  • 14387

                  #9
                  Originally posted by emandel
                  OK the minus sign is gone, but it is still giving me the wrong calculation.

                  I have my bithday as July 15 1973 which means that I am not 34 until July 15 2007, yet in the field that i just created it shows it as 34 as of now already. Any suggestons?
                  This shouldn't be happening.

                  Try replacing Now() with Date().

                  What format are you using in birthdate field for the date?

                  Mary

                  Comment

                  • emandel
                    New Member
                    • Dec 2006
                    • 65

                    #10
                    Try replacing Now() with Date().

                    What format are you using in birthdate field for the date?
                    That didn't work either,

                    The format for birthdate is general date

                    is there something else I should be looking at?

                    Comment

                    • MMcCarthy
                      Recognized Expert MVP
                      • Aug 2006
                      • 14387

                      #11
                      General Date should have worked with Now()

                      Try the following ...

                      Code:
                       
                      SELECT * FROM Tablename
                      WHERE DateDiff('yyyy', Format([birthdate], 'Short Date'), Format(Now(), 'Short Date')) > 35;
                      and

                      Code:
                      =DateDiff ("yyyy", Format([birthdate], "Short Date"),  Format(Now(), "Short Date"))

                      Comment

                      • emandel
                        New Member
                        • Dec 2006
                        • 65

                        #12
                        Try the following ...

                        Code:

                        SELECT * FROM Tablename
                        WHERE DateDiff('yyyy' , Format([birthdate], 'Short Date'), Format(Now(), 'Short Date')) > 35;


                        and

                        Code:
                        =DateDiff ("yyyy", Format([birthdate], "Short Date"), Format(Now(), "Short Date"))
                        Sorry do I put both of these codes in the control source of the "age" text box?


                        --------------------------------------------------------------------------------

                        Comment

                        • MMcCarthy
                          Recognized Expert MVP
                          • Aug 2006
                          • 14387

                          #13
                          Just this one ...

                          Code:
                          =DateDiff ("yyyy", Format([birthdate], "Short Date"),  Format(Now(), "Short Date"))

                          Comment

                          • MMcCarthy
                            Recognized Expert MVP
                            • Aug 2006
                            • 14387

                            #14
                            Just a thought

                            Have you checked that your computers system date is correct?

                            Mary

                            Comment

                            • emandel
                              New Member
                              • Dec 2006
                              • 65

                              #15
                              My computer's system date is fine,

                              I am giving up for the night, it doesn't make any sense to me.

                              Thank you so much for your help over the last hour an d a half

                              Comment

                              Working...