Results 1 to 14 of 14

Thread: Default Year

  1. #1
    Join Date
    Mar 2009
    Posts
    120

    Unanswered: Default Year

    Hello,
    Am hoping someone can help me figure out how to do this. I have a simple DB for tracking of hours for our Contract Labor usage.

    The fields are:

    Period
    Dept #
    Job Code
    Invoice Hours
    Invoice Dollars
    Clock Hours
    Agency Name
    Invoice Number
    Fiscal Year
    Entry Date
    Calendar Year

    I am trying to figure out a way to get Calendar Year to default to the Current year. I've tried using Year([EntryDate]) but it doesn't recognize the field name. Is there something else I can do to get this field to default automatically instead of me having to go in and change the Default Value Every year?

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    In your table you have field name Entry Date , but you are using Year([EntryDate]). Change this to Year([Entry Date]) note the space between Entry and Date.

  3. #3
    Join Date
    Mar 2009
    Posts
    120
    I did do that. I still got the same error. It doesn't recognize the field.

    The error message I get is:

    "The database engine does not recognize either the field 'EntryDate' in a validation expression, or the default value in the table 'Agency Invoice's."

    I changed the field to the way it is above, I typed it with the space in the pevious email for ease of reading.
    Last edited by CHI Brian; 10-14-10 at 09:25.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is this a default value to be overridden if necessary or should it always be the year of Entry Date?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2009
    Posts
    120
    It should always be the year of entry. So it would be the same as the year on the field Entry Date.

    Fiscal Year is different, our Fiscal Year runs July - June and will require being changed manually since it doesn't match the entry year until January.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In that case you have a transitive dependency and are violating third normal form.
    Don't store this data - just derive the year as and when you need it. It is for the same reason you only store date of birth and work out age as and when you need it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2009
    Posts
    120
    Okay. Is that easy to do?
    I don't know VB or anything, I wing this all the time and figure it out usually on my own for the most part.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh very easy.
    Code:
    SELECT this, that, [Entry Date], YEAR([Entry Date]) AS [Year of Entry Date]
    FROM myTable
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Mar 2009
    Posts
    120
    Okay. That is VB right?

    I can do that I think, never did VB Code before. Where would I do this at?

    The SELECT this, that, what is this and that? I don't know VB at all. I do everything in Access using querys and such.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is not VB - this is SQL.
    "this, that" were made up column names - just placeholders, you would use real column names.
    SQL is the code that is "behind the covers" when you create a query. Really, Access is just translating the actions you make in the query designer into (horribly formatted) SQL. Look at your queries in "SQL View" to see what I mean.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2009
    Posts
    120
    Thanks.
    From the way it sounds it would require a query using SQL to update this field in my table.

    I am totally clueless about this. What I know about Access I learned on my own, am not a programmer.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No - you do not have a column in your table for the year of entry. You derive the year of entry from the Entry Date whenever you need it. You do not store the year separate to Entry Date.
    The reason is that there is a functional dependency between Entry Year and Entry Date.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2009
    Posts
    120
    I do have a column in my table for year of entry, it is Calendar Year.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes you do, and I'm saying you shouldn't.

    Instead of me just telling you to drink how about I take you to the water. You say you are not a programmer or database designer - that makes it all the more important you read this, not less.
    The Relational Data Model, Normalisation and effective Database Design
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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