Date formats in forms and tables

[simona67_99]

Hello everybody,

if anybody knows the answer to my problem in Access please share:

I want to define a field in a table which contains only the month and year
(something like MM/JJJJ) not the complete date (DD/MM/JJJJ). When I insert
data into this field with a form, I want to take the values from a lookup
list (eg. the list should contain: May 2007, June 2007, July 2007, etc.). The
data should be formatted as a date (so I can work with it in expressions,
sorting, etc)

Please help!

Thanks,

Simona

 

[Rick Brandt]

Hello everybody,

if anybody knows the answer to my problem in Access please share:

I want to define a field in a table which contains only the month and
year (something like MM/JJJJ) not the complete date (DD/MM/JJJJ).
When I insert data into this field with a form, I want to take the
values from a lookup list (eg. the list should contain: May 2007,
June 2007, July 2007, etc.). The data should be formatted as a date
(so I can work with it in expressions, sorting, etc)

Please help!

If you want to work with it as a date then you need to store it as one. Just
use the first of the month for all values and use formatting to suppress display
of the day.

As for your list... You can easily build a table that holds these values or you
can use a table filled with integers. Then build a query based on that table
that uses DateAdd and the Format() function to create the list of values with
another column that holds a date value using the first day of each month. You
display the mm/yyyy value while storing the actual date value.

With such a table you can have a list of dates that (for example) is a list of
months going back a few months and going forward a few months and the list will
automatically "walk" forward each month so you never have to update the
underlying table.

 

[Klatuu]

Fomatting a date without the day part is not possible. Even if you tried to
set the day to 0, you will end up with the last day of the prior month. That
is because of the way dates are stored.

Depending on your exact requirements, you might use the first day of the
month.
You can return the format you are showing with format(date, "mmmm yyyy");
however, you will have to do the sorting prior to the formatting or you will
not get a cronological order.

 

[missinglinq via AccessMonster.com]

As Dave said, a date is a date is a date; a month and a day and a year! But
as he also said, you can do ost of what you need with formatting.

For instance, if your field is DDate and the date entered is 12/01/2007

format(DDate,"MM/YY") will yield 12/07 for display or printing

You can use the same thing in expressions, as here in using the formatted
date with the AddDate function

format((DateAdd("m",1,format(DDate,"MM/YY"))),"MM/YY") will yield 01/08, 1
month later than 12/07

 

[simona67_99]

Thank you so much for the answer! Can I use the function DatePart() in any
way? It's exactly what I need but I don't know if it can be used in forms.

Could you explain to me again, what the exact steps are (of what you wrote in
the message)? I am quite the beginner in Access and I don't always get the
point..)Thanks

Hello everybody,

[quoted text clipped - 8 lines]

Please help!

If you want to work with it as a date then you need to store it as one. Just
use the first of the month for all values and use formatting to suppress display
of the day.

As for your list... You can easily build a table that holds these values or you
can use a table filled with integers. Then build a query based on that table
that uses DateAdd and the Format() function to create the list of values with
another column that holds a date value using the first day of each month. You
display the mm/yyyy value while storing the actual date value.

With such a table you can have a list of dates that (for example) is a list of
months going back a few months and going forward a few months and the list will
automatically "walk" forward each month so you never have to update the
underlying table.

 

[Rick Brandt]

Thank you so much for the answer! Can I use the function DatePart()
in any way? It's exactly what I need but I don't know if it can be
used in forms.

Could you explain to me again, what the exact steps are (of what you
wrote in the message)? I am quite the beginner in Access and I don't
always get the point..)Thanks

Create a table with a single Integer field. In that field store the numbers
negative 10 through positive 10. Now build a new query and use that table as
its input. In the first column of the query grid enter...

ChooseMonth: DateSerial(Year(Date()), Month(Date())+[IntegerFieldName], 1)

The query should give you a list of dates starting from 10 months in the past
and ending 10 months in the future. All dates will be the first of each
respetive month.

Add another column next to that one...

DisplayMonth: Format(DateSerial(Year(Date()), Month(Date())+[IntegerFieldName],
1),"mmmm yyyyy")

That will give you the display format you want. Now you can use that query as
the RowSource for your ComboBox. Use the first column as the hidden bound
column (the one you store) and the second column as the one you show the user.

 

[simona67_99 via AccessMonster.com]

Hi Rick,

thanks so much for your detailed answer. You look like you know a lot about
Access...that's why, I want to ask you another question:

I have a table in my database, in which there is for every month exactly one
record for every employee. Something like:

May 2007...Lidia Devon...Grade A
May 2007...Eric Drake...Grade B
May 2007...Sam Neil...Grade A
June 2007...Lidia Devon...Grade B
June 2007...Eric Drake...Grade C
June 2007...Sam Neil...Grade A

The user of my DB is updating this table every month through a form. I want
to make sure, he doesn't enter 2 records for May for one employee (eg. Lidia
Devon). How do I do this? Make a validation rule on 2 fields?

Please help!

Thank you,

Simona

 

[Douglas J. Steele]

Hopefully your user isn't update the table directly, but is using a form. In
the form's BeforeUpdate event, you check to see whether there's already an
entry in the table for that date and user, pop up a message to the user
informing them of that fact, and set Cancel = True in the code.

 

[Rick Brandt]

Hi Rick,

thanks so much for your detailed answer. You look like you know a lot
about Access...that's why, I want to ask you another question:

I have a table in my database, in which there is for every month
exactly one record for every employee. Something like:

May 2007...Lidia Devon...Grade A
May 2007...Eric Drake...Grade B
May 2007...Sam Neil...Grade A
June 2007...Lidia Devon...Grade B
June 2007...Eric Drake...Grade C
June 2007...Sam Neil...Grade A

The user of my DB is updating this table every month through a form.
I want to make sure, he doesn't enter 2 records for May for one
employee (eg. Lidia Devon). How do I do this? Make a validation rule
on 2 fields?

Please help!

If as discussed, you store all dates as the first of the month and with no
time component (actually a time of midnight), then you can create a unique
index on the combination of the date field and the name fields or employeeID
(whatever identifies the person uniquely). Then the database engine will
dis-allow any two entries for an employee in the same month.