Convert Text To Valid Date
in [General]
|
Prev: How do I change the action of button in Access 2007 contact list?
Next: How do I view table descriptions for the whole list of tables?
From: zyus on 20 Apr 2010 06:17 I have text field of [Yr] and [mth] and sample records as follow [Yr] [mth] 2010 01 2010 02 How to convert the two fields as a valid year & month date Thanks
From: Stefan Hoffmann on 20 Apr 2010 07:15 hi, On 20.04.2010 12:17, zyus wrote: > I have text field of [Yr] and [mth] and sample records as follow > > [Yr] [mth] > 2010 01 > 2010 02 > > How to convert the two fields as a valid year& month date SELECT DateSerial([Yr], [mth], 1) As SampleDate, * FROM yourTable mfG --> stefan <--
From: John W. Vinson on 20 Apr 2010 11:41 On Tue, 20 Apr 2010 03:17:01 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: >I have text field of [Yr] and [mth] and sample records as follow > >[Yr] [mth] >2010 01 >2010 02 > >How to convert the two fields as a valid year & month date > >Thanks Note that a Date/Time field *always* contains a complete date, including a day: there's no such thing as a "year and month date". The Date/Time value is actually stored as a number, a count of days and fractions of a day (times) since midnight, December 30, 1899. As such it corresponds to a precise instant of time. January 2010 was not a precise instant of time, it was a whole bunch of them (some pleasant instants, some irritating....). Stefan's DateSerial expression will give you a date, arbitrarily picking midnight at the beginning of the first day of the month; but if you only display the year and month of the date, you should be OK. -- John W. Vinson [MVP]
From: zyus on 20 Apr 2010 20:54 Hi John, Can i use Stefan's date serial expression with below expression that you have suggested before on special tagging. I want to include a valid date (month & Year) TblTag: IIf(Val([month])-Val(DMax("[MONTH]","Qunionall"))=0,"Current Month","Previous Month") "John W. Vinson" wrote: > On Tue, 20 Apr 2010 03:17:01 -0700, zyus <zyus(a)discussions.microsoft.com> > wrote: > > >I have text field of [Yr] and [mth] and sample records as follow > > > >[Yr] [mth] > >2010 01 > >2010 02 > > > >How to convert the two fields as a valid year & month date > > > >Thanks > > Note that a Date/Time field *always* contains a complete date, including a > day: there's no such thing as a "year and month date". The Date/Time value is > actually stored as a number, a count of days and fractions of a day (times) > since midnight, December 30, 1899. As such it corresponds to a precise instant > of time. January 2010 was not a precise instant of time, it was a whole bunch > of them (some pleasant instants, some irritating....). > > Stefan's DateSerial expression will give you a date, arbitrarily picking > midnight at the beginning of the first day of the month; but if you only > display the year and month of the date, you should be OK. > -- > > John W. Vinson [MVP] > . >
From: John W. Vinson on 21 Apr 2010 00:04
On Tue, 20 Apr 2010 17:54:02 -0700, zyus <zyus(a)discussions.microsoft.com> wrote: >Hi John, > >Can i use Stefan's date serial expression with below expression that you >have suggested before on special tagging. I want to include a valid date >(month & Year) > >TblTag: IIf(Val([month])-Val(DMax("[MONTH]","Qunionall"))=0,"Current >Month","Previous Month") Stefan's expression will return a valid date/time value. You can use it wherever you would use a date/time value. If [month] in the above is in fact a date/time, then yes, you can replace [month] with the expression. -- John W. Vinson [MVP] |