Date Parsing

[Guest]

I have a table with a field that reflects the current date using Date(). I
want to extract the month to a field, the day to a field and the year to
another field (three fields). These fileds will be used to supply
information in other tables. I also need each field to contain two
characters (e.g., 01 instead of 1). I tried the Datepart function but must
be doing something wrong. Any suggestions?

Thanks,
Bill

 

[Roger Carlson]

Not the question you asked, but it would be a Very Bad Idea to extract and
save the parts of the date into separate fields. This introduces
possibility of data integrity errors. These are essentially calculated
fields and you rarely want to store calculated fields.

The best way would be to leave the date intact and just use the date
functions to get the parts as you need them. Look at the Month, Day, and
Year functions.

--
On my website (www.rogersaccesslibrary.com), there is a small Access
database sample which illustrates this: ""

--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Brendan Reynolds]

It is usually better not to store the results of calculations, but to
calculate them 'on the fly' as needed (that way they are always up-to-date
with the latest changes to the data on which they are based. This can be
done quite easily in a query ...

SELECT tblTest.DateField, Format$(Year([DateField]),'00') AS YearField,
Format$(Month([DateField]),'00') AS MonthField,
Format$(Day([DateField]),'00') AS DayField
FROM tblTest;

If you're convinced you really have a good reason for breaking the rules and
storing the calculated values, you need code something like this in the
AfterUpdate event procedure of the text box into which the user enters the
date ...

Private Sub DateField_AfterUpdate()

Me.YearField = Format$(Year(Me.DateField), "00")
Me.MonthField = Format$(Month(Me.DateField), "00")
Me.DayField = Format$(Day(Me.DateField), "00")

End Sub

The YearField, MonthField, and DayField fields will need to be text fields
if you want to actually store the leading zeros, or you can use numeric
fields which will not store the leading zeros but you can display leading
zeros using Format as above.