Problem setting up a primary key field (1 Viewer)

BazLondon

Registered User.
Local time
Today, 15:55
Joined
Jan 3, 2005
Messages
15
On the face of it a simple question.

I'm setting up a very simple table with a primary key field consisting of a year and month.

Attached is an image of the Design View.

Also attached is an image of the response when entering data in Datasheet View.

I'd be grateful for a lead as to where I'm going wrong.:banghead:

Thanks

Using 2007 (still, but it's serving me well)
 

Attachments

  • Design View.JPG
    Design View.JPG
    59.1 KB · Views: 88
  • Datasheet View.jpg
    Datasheet View.jpg
    31.1 KB · Views: 82

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:55
Joined
Aug 30, 2003
Messages
36,118
Well, "yyyy-mm" isn't a date, it's a year and month. Like the message indicates, if you want data saved in that format, you'd need to make it a text field. If you want to store a date, it needs a day as well. You can display a date/time value in that format, but not save it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:55
Joined
Oct 29, 2018
Messages
21,358
Hi,

I think the problem involves some confusion about data types and formatting. Access stores date/time data types with both date and time components. Furthermore, dates alway include the year, month, and day components. You can format the data to display them in many different ways, but the underlying data doesn’t change at all.
 

GinaWhipp

AWF VIP
Local time
Today, 11:55
Joined
Jun 21, 2011
Messages
5,901
Hmm, just curious... Are you saying you will only have one record per month-year? Just seems odd.
 

BazLondon

Registered User.
Local time
Today, 15:55
Joined
Jan 3, 2005
Messages
15
Thanks to all who responded.

Pbaldy - you've hit the nail on the head. Pretty obvious really isn't it? yyyy-dd isn't a date.

Baz
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:55
Joined
Aug 30, 2003
Messages
36,118
No problem Baz. When you understand how date/time values are stored, you realize why all 3 components are needed to save one. You can display today's date all kinds of ways, including yours, but it's always saved as 43437, the number of days after the base date (disregarding time, which is saved as the decimal portion). Obviously if Access doesn't know the day, it can't come up with that value thus can't save as a date/time.
 

Users who are viewing this thread

Top Bottom