Year & Month as Intergers

[Sgwapt]

Say my table as Date, Year, Month fields
and a user form has a Date Textbox with Cs set to Date in table

Is there a way for the table to add the year and month value automatic?
Can this be done in the Default Values property within the table?

Thanks for your assistance

 

[Pat Hartman\(MVP\)]

1. Using Date, Year, and Month for column names only leads to problems since
they are also the names of functions. As well as following accepted
formatting methods for user created names, you need to avoid the names of
functions and properties. Search for the reserved names lists in the KB.
There are two of them. Once for Access and one for SQL.
2. It is not necessary to store year and month separately since they are
already part of the date field. Whenever you need the month or year
separately, use the appropriate function --- Month(YourDate) or
Year(YourDate) in a query or set the format property for a control to mm or
yyyy.

 

[Tim Ferguson]

Delete these fields from your table, then build a query based on your
table and add these as calculated fields in the query. Then just use
the query instead of the table any place you want those values.

I think they are going to be constituents in the primary key...


Tim F

 

[Pat Hartman\(MVP\)]

They do not belong as part of a primary key. Primary keys should not be
constructed from pieces of other attributes. They should be complete in
themselves or be autonumbers that have no meaning.

 

[Tim Ferguson]

They do not belong as part of a primary key. Primary keys should not
be constructed from pieces of other attributes. They should be
complete in themselves or be autonumbers that have no meaning.

Huh? Show me this in Codd or Date or Pascal... The requirements for a PK
are that it should be knowable, unique and stable. As far as I
understood the OP's previous thread, these requirements are met for the
purpose he or she has in mind.

It may not be the way you or I would want to organise the office, but we
can't always arrange other people the way we would like.

As for the second part("autonumbers that have no meaning"), that is
hardly a universally accepted argument, and I think you should accompany
such a statement with a caveat. And no, I am not about to take part in
another Surrogate Key Holy War..!

Best wishes


Tim F

 

[Pat Hartman\(MVP\)]

"Huh? Show me this in Codd or Date or Pascal... "
The "mushed" key violates 1st, 2nd, AND 3rd normal forms!
1. It assigns multiple attributes to the same column.
2. It is redundant since each mushed value occurs as some other attribute or
part of some other attribute in the same record.
3. The pieces of the PK are not dependent on the PK, they are dependent on
the value of some other attribute in the record and if it changes there
becomes a data anomaly since people who "know" how the key is calculated
will not understand why the PK components no longer match the data fields of
origin.
Is that enough?