Advice for newbie on tables and dates

jphenning

New member
Local time
Today, 13:35
Joined
Sep 26, 2011
Messages
3
Hi

This is my first post.

I am creating a DB for the first time, and need some advice please.

I am creating a DB that will be rolling on a month-month basis.
i.e Jan, Feb, Mar etc... and then Year

do I need to create two separate tables one for month and one for Year?

or can I use the a field and just use the date data type and remove the day part? (is this possible)

what would be my ideal way of doing this?
 
A date field should be enough.

If you want to filter it further for reports you can do so in various ways.

For example, if I wanted to use a month as a criteria where the field is a full date I might use:

Code:
Month([DateField])
Which would return an integer containing 1-12.

Alternatively I could just tweak the display format like so:

Code:
Format([DateField],"mmm yyyy")
Which should return the name of the month followed by the 4 digit year from the full date contained in the DateField field.

This means a single field should be enough, no need for additional tables.
 
Hi CBrighton

thank you for the reply, can I confirm:

If I put in Field Name- Date
Data Type - Date/Time

under the general tab under do I change Format to Format([DateField],"mmm yyyy")?

or am I missing something?
 
I'd avoid field names like Date as Date() is a function.

I always try to use 2 words to try to avoid that, like JoinDate, RecordCreationDate, LeaveDate, ChangeDate, etc.

I think the format property will accept "mmm, yyyy" in the properties, but personally I'd want the table to hold the date unaltered and for the output (whether it's a control on a form or on a report) to alter the format as required.

The above Format() code can be used in the query builder or in VBA code.

For example, if you wanteda query to return first the whole date, then a formatted version you could add the first column as normal then manually input the following into the name of the 2nd field:

Code:
DateFormatted: Format([DateField], "dd mmm yyyy")

You can easily test different formats within the query builder by making lots of slighty different fields. Alternatively you can google "Access format()" and you will find plenty of sites which will tell you the different options (e.g. "mmm" is the full month name as apposed to a number)
 
I'd avoid field names like Date as Date() is a function.

I always try to use 2 words to try to avoid that, like JoinDate, RecordCreationDate, LeaveDate, ChangeDate, etc.

I think the format property will accept "mmm, yyyy" in the properties, but personally I'd want the table to hold the date unaltered and for the output (whether it's a control on a form or on a report) to alter the format as required.

The above Format() code can be used in the query builder or in VBA code.

For example, if you wanteda query to return first the whole date, then a formatted version you could add the first column as normal then manually input the following into the name of the 2nd field:

Code:
DateFormatted: Format([DateField], "dd mmm yyyy")

You can easily test different formats within the query builder by making lots of slighty different fields. Alternatively you can google "Access format()" and you will find plenty of sites which will tell you the different options (e.g. "mmm" is the full month name as apposed to a number)

Thanks for the advice

I have changed the filed to MonthOf as the information on this table will be entered at the beginning of them month but will be for the month before so what is entered in October 2011 will be for September 2011.

This is why I do not want a entered date and just a mmm-yyyy.

do you suggest that I have 2 date fields on DreationDate and one monthOf or am I just putting irrelevant information on my table?
 
It's generally agreed that calculated fields should not be stored.

Storing the full date and a month which is calculated off of that date means you are taking up more harddrive space, etc than required. There's no need to hold the same piece of data twice.

You can use the DateAdd function if you need to tweak things (like showing the date minus one month) and when this is combined with the Format function you can output a calculated date in whatever format you want.

You could display the time 7 hours ago, you could display the weekday 34 days ago, you could display the month name 1902 hours ago, etc.

Here's a couple of links to help:

Format function: http://www.techonthenet.com/access/functions/date/format.php
DateAdd function: http://www.techonthenet.com/access/functions/date/dateadd.php

:edit:

I should include an example of putting one function within another:

Code:
DateMinus1Month: Format(DateAdd("m", -1,[DateField]),"mmm yyyy")
 

Users who are viewing this thread

Back
Top Bottom