turning a short date (11/21/07) into a long date (21st Nov 2007)

[benny]

I've got a form for entering information into my database. The first piece of
information entered is the date time group (in the format yymmdd e.g 071101
is the 1st nov 2007). This is the primary key and so is a unique number in
the records. the next fields are year, month and day. im trying to get it to
work so that these 3 fields automatically fill themselves out based on the
information in the date time group field (i.e with the example above, 2007
would automatically go in the year field, nov in the month etc). any help
would be much appreciated, thanks a lot

 

[Bob Quintal]

I've got a form for entering information into my database. The
first piece of information entered is the date time group (in the
format yymmdd e.g 071101 is the 1st nov 2007). This is the primary
key and so is a unique number in the records. the next fields are
year, month and day. im trying to get it to work so that these 3
fields automatically fill themselves out based on the information
in the date time group field (i.e with the example above, 2007
would automatically go in the year field, nov in the month etc).
any help would be much appreciated, thanks a lot

Firstly, are you storing the date in a text field? Your comment
about format implies you are. Dates are displayed using a format,
not stored with a format, because the format is a text string.

Proper database design says you do not store the year, month and day
fields separately. You extract those values when needed from the
date by using the functions year([dateField]), month([dateField]

 

[ruralguy via AccessMonster.com]

Hi Benny,
The month, day and year values can all be calculated from the DateTimeGroup
field and should *not* be stored in your table but calculated each time you
need them. Here's a link that describes how Access stores a date:

Storing, Calculating, and Comparing Date/Time Data
http://support.microsoft.com/default.aspx/kb/q130514/

I would also recommend using a calendar to select dates to eliminate invalid
values. Here's a selection to pick from:

http://www.accessmvp.com/JConrad/accessjunkie/calendars.html

 

[ruralguy via AccessMonster.com]

Darn, hit send too quick!
Look at the Year(), Month() and Day() functions for your calculations.

 

[Bob Quintal]

Cool thanks heaps that helps a lot. only other thing is, i added a
date/time column and the format is yymmdd. what would the syntax
look like if i wanted to make it say yymmdd.x (e.d 071101.1 is the
first entry on 1 nov, 071101.2 is the second entry in 1 nov etc).
i'd want to be able to put the last number in, not for it to be
automatic... thanks again, much appreciated.
benny

For that, I'd put a separate column in the table, to store your
Sequence. Alternately, if you populate the date field with hours and
minutes, you will get them sorted correctly, but that may not be
possible, if importing data as opposed to real-time data-entry.

Q

I've got a form for entering information into my database. The
first piece of information entered is the date time group (in
the format yymmdd e.g 071101 is the 1st nov 2007). This is the
primary key and so is a unique number in the records. the next
fields are year, month and day. im trying to get it to work so
that these 3 fields automatically fill themselves out based on
the information in the date time group field (i.e with the
example above, 2007 would automatically go in the year field,
nov in the month etc). any help would be much appreciated,
thanks a lot

Firstly, are you storing the date in a text field? Your comment
about format implies you are. Dates are displayed using a format,
not stored with a format, because the format is a text string.

Proper database design says you do not store the year, month and
day fields separately. You extract those values when needed from
the date by using the functions year([dateField]),
month([dateField]

 

[benny]

okay thanks heaps i'll have a play round see if i can get it to work...is it
alright if you e-mail me so i can email you directly? have really appreciated
your help so far, cheers

benny

e-mail address is: (e-mail address removed)

Cool thanks heaps that helps a lot. only other thing is, i added a
date/time column and the format is yymmdd. what would the syntax
look like if i wanted to make it say yymmdd.x (e.d 071101.1 is the
first entry on 1 nov, 071101.2 is the second entry in 1 nov etc).
i'd want to be able to put the last number in, not for it to be
automatic... thanks again, much appreciated.
benny

For that, I'd put a separate column in the table, to store your
Sequence. Alternately, if you populate the date field with hours and
minutes, you will get them sorted correctly, but that may not be
possible, if importing data as opposed to real-time data-entry.

Q

I've got a form for entering information into my database. The
first piece of information entered is the date time group (in
the format yymmdd e.g 071101 is the 1st nov 2007). This is the
primary key and so is a unique number in the records. the next
fields are year, month and day. im trying to get it to work so
that these 3 fields automatically fill themselves out based on
the information in the date time group field (i.e with the
example above, 2007 would automatically go in the year field,
nov in the month etc). any help would be much appreciated,
thanks a lot


Firstly, are you storing the date in a text field? Your comment
about format implies you are. Dates are displayed using a format,
not stored with a format, because the format is a text string.

Proper database design says you do not store the year, month and
day fields separately. You extract those values when needed from
the date by using the functions year([dateField]),
month([dateField]

 

[Linq Adams via AccessMonster.com]

This thread seems to be kind of going around and around with the same things
being said multiple times, so to summarize:

Dates are stored in Access as *dates*

Setting the format, ie short, long , whatever has nothing to do with how the
date is stored, only how it's displayed!

In your table, have two fields which are bound to controls on your form:

OriginalDate
EntryNumber

On your form have 4 unbound controls with these Control Sources

Control: Day
Control Source: =Day([OriginalDate])

Control: Month
Control Source: =Month([OriginalDate])

Control: Year
Control Source: =Year([OriginalDate])

Control: DateEntry
Control Source: =Format([OriginalDate],"yymmdd") & "." & [EntryNumber]

If you wanted to store *DateEntry* you could make it a bound field, and use
the same formula in VBA code to set it. You could also use the same formula
in a calculated field in a query.

The point is, if you store OriginalDate and EntryNumber you can always pull
everything else, whenever you need it!

 

[benny]

yeah im struggling with this one..!! i did that and its almost working, just
got a couple more things to fix. could you e-mail me so i can e-mail you back
with the things im still having trouble with..?will stop this getting too
full...

cheers
benny

e-mail addy: (e-mail address removed)

 

[Bob Quintal]

okay thanks heaps i'll have a play round see if i can get it to
work...is it alright if you e-mail me so i can email you directly?
have really appreciated your help so far, cheers

benny

I prefer to keep correspondence in the newsgroup, so that others may
read and benefit from the discussions.

I'm here every day.

Q

Cool thanks heaps that helps a lot. only other thing is, i
added a date/time column and the format is yymmdd. what would
the syntax look like if i wanted to make it say yymmdd.x (e.d
071101.1 is the first entry on 1 nov, 071101.2 is the second
entry in 1 nov etc). i'd want to be able to put the last number
in, not for it to be automatic... thanks again, much
appreciated. benny

For that, I'd put a separate column in the table, to store your
Sequence. Alternately, if you populate the date field with hours
and minutes, you will get them sorted correctly, but that may not
be possible, if importing data as opposed to real-time
data-entry.

Q

:

in
I've got a form for entering information into my database.
The first piece of information entered is the date time
group (in the format yymmdd e.g 071101 is the 1st nov 2007).
This is the primary key and so is a unique number in the
records. the next fields are year, month and day. im trying
to get it to work so that these 3 fields automatically fill
themselves out based on the information in the date time
group field (i.e with the example above, 2007 would
automatically go in the year field, nov in the month etc).
any help would be much appreciated, thanks a lot


Firstly, are you storing the date in a text field? Your
comment about format implies you are. Dates are displayed
using a format, not stored with a format, because the format
is a text string.

Proper database design says you do not store the year, month
and day fields separately. You extract those values when
needed from the date by using the functions year([dateField]),
month([dateField]