Date Formats
in [General]
|
Prev: ysc8910@msn.com.tw
Next: shadmehr.ws
From: Linda on 7 Sep 2005 16:38 Hi All, I have a date field on an access 2000 form used by multiple users, formated as a "short date" and is automatically populated with todays date...when a new record is created. For some reason it also includes the time with the date. The help or book I have indicates that "short date" should not include time. This is not a problem except when droping the data into excel and doing "and" functions for this date and other dates in the database for comparision purposes. For example if a date also includes a time and is compared to another field with the same date...but without time, it returns a false match. Rightfully so....but not what I am looking for. Same with formatting the results...the displayed results look good...but the time is still part of the cell contents. I would like the access field to only load the todays date without a time, is that possible? OR...a way to quickly eliminate the time portion of the result, once the data is in excel. This has me totally stumped and any suggestions would be greatly appreciated. -- Linda
From: Joseph Meehan on 7 Sep 2005 16:44 Linda wrote: > Hi All, > > I have a date field on an access 2000 form used by multiple users, > formated as a "short date" and is automatically populated with todays > date...when a new record is created. For some reason it also > includes the time with the date. The help or book I have indicates > that "short date" should not include time. > > This is not a problem except when droping the data into excel and > doing "and" functions for this date and other dates in the database > for comparision purposes. > > For example if a date also includes a time and is compared to another > field with the same date...but without time, it returns a false > match. Rightfully so....but not what I am looking for. Same with > formatting the results...the displayed results look good...but the > time is still part of the cell contents. > > I would like the access field to only load the todays date without a > time, is that possible? OR...a way to quickly eliminate the time > portion of the result, once the data is in excel. This has me > totally stumped and any suggestions would be greatly appreciated. I can't help you with the Excel part of it (I am sure someone will, I would guess they will show you have to convert the "date" to a text field). I can tell you that Access holds dates and time in the same field, after all they are all time. You only control what shows. You book says short date does not show the time, but it is still there. -- Joseph Meehan Dia duit
From: Graham Mandeno on 7 Sep 2005 17:13 Hi Linda You say the field is "automatically populated with todays date". There are three functions that return the current system clock in different forms: Time() returns only the time of day (the date is zero) Date() returns just the date (midnight on the current day) Now() returns the current date *and* time I suspect you are using Now() for your field's DefaultValue. If you change it to Date() then the problem should go away. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Linda" <Linda(a)discussions.microsoft.com> wrote in message news:5A94E09D-E059-415D-8C78-825782E9F20D(a)microsoft.com... > Hi All, > > I have a date field on an access 2000 form used by multiple users, > formated > as a "short date" and is automatically populated with todays date...when a > new record is created. For some reason it also includes the time with the > date. The help or book I have indicates that "short date" should not > include > time. > > This is not a problem except when droping the data into excel and doing > "and" functions for this date and other dates in the database for > comparision > purposes. > > For example if a date also includes a time and is compared to another > field > with the same date...but without time, it returns a false match. > Rightfully > so....but not what I am looking for. Same with formatting the > results...the > displayed results look good...but the time is still part of the cell > contents. > > I would like the access field to only load the todays date without a time, > is that possible? OR...a way to quickly eliminate the time portion of the > result, once the data is in excel. This has me totally stumped and any > suggestions would be greatly appreciated. > > -- > Linda >
From: Linda on 7 Sep 2005 17:34 Thank you Graham...Yes, I was using now() and will change to date() first thing tomorrow morning. Thanks again for all your help! -- Linda "Graham Mandeno" wrote: > Hi Linda > > You say the field is "automatically populated with todays date". There are > three functions that return the current system clock in different forms: > Time() returns only the time of day (the date is zero) > Date() returns just the date (midnight on the current day) > Now() returns the current date *and* time > > I suspect you are using Now() for your field's DefaultValue. If you change > it to Date() then the problem should go away. > -- > Good Luck! > > Graham Mandeno [Access MVP] > Auckland, New Zealand > > "Linda" <Linda(a)discussions.microsoft.com> wrote in message > news:5A94E09D-E059-415D-8C78-825782E9F20D(a)microsoft.com... > > Hi All, > > > > I have a date field on an access 2000 form used by multiple users, > > formated > > as a "short date" and is automatically populated with todays date...when a > > new record is created. For some reason it also includes the time with the > > date. The help or book I have indicates that "short date" should not > > include > > time. > > > > This is not a problem except when droping the data into excel and doing > > "and" functions for this date and other dates in the database for > > comparision > > purposes. > > > > For example if a date also includes a time and is compared to another > > field > > with the same date...but without time, it returns a false match. > > Rightfully > > so....but not what I am looking for. Same with formatting the > > results...the > > displayed results look good...but the time is still part of the cell > > contents. > > > > I would like the access field to only load the todays date without a time, > > is that possible? OR...a way to quickly eliminate the time portion of the > > result, once the data is in excel. This has me totally stumped and any > > suggestions would be greatly appreciated. > > > > -- > > Linda > > > > >
From: hughess7 on 8 Sep 2005 05:54
Thanks for your response Allen. The control is used to store the reporting month against records. The user selects the records in a continuous form via a tickbox then enters the ReportingDate in the footer of the form and presses a button which runs an update query to write the ReportingDate to a field CBReportDate for each selected record. I originally just had it as a text field with JAN05, FEB05 etc and had everything working ok. The user now wants me to change the YTD report though, currently it includes all records in a particular year but the user wants it to report on all records up to the month entered. ie if AUG05 is entered the YTD report should show JAN-AUG05 records only. I could not think of an easy way to do this without converting the CBReportDate field to a date field so I could use the comparison operators. Sue "Allen Browne" wrote: > Where are you going with this control? > > If you are using it to filter a date/time field for a report, then you want > to field to contain a real date so that your filter works reliably and > efficiently. Using the Format() function forces it to a string/text type, > and will not give reliable results. Similary, trying to force it with an > input mask just gets Access to treat the numbers as month and day of the > current year, so that won't work either. > > It's easy enough to use the AfterUpdate event of the control to change the > date if Day([ReportingDate] <> 1. > > You could set the Format property of the text box to: > mmmyyyy > so that Access shows the date in that format, but if the control is unbound > there is still a chance that Access may not understand the data type > correctly. > > The only way you could display one thing and yet treat it as something > different would be to use a combo (or list box), where the bound column is > hidden, and the displayed values are mmmyy whereas the actual value is a > true date. This would mean creating a table of dates for the first of each > month, so the combo's RowSource would be: > SELECT TheDate, Format(TheDate, "mmmyy") As ShowDate > FROM tblDate; > > -- > Allen Browne - Microsoft MVP. Perth, Western Australia. > Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org. > > "hughess7" <hughess7(a)discussions.microsoft.com> wrote in message > news:AAA44E3A-51E0-4A72-AB96-5593B07091AF(a)microsoft.com... > > > > I have an unbound control on a form called ReportingDate. It defaults to > > the > > month previous to current month by the following in the default value > > =Format(DateAdd("m",-1,Date()),"mmmyy") > > If the user types over this I want them to enter month+year only, with the > > date defaulting to 01 of month eg Sep05 entered by user and system stores > > 01/09/05 in field (the date is entered into a different field via an > > update > > query run from a button on the form). > > > > I also have an input mask of >L<LL\-00;0;_ which lets the user enter > > Month-Year (modification of the medium date mask). > > > > I thought this was working but its not quite right.... if I enter Sep-05 > > it > > stores 05/09/05 and if I enter Sep-06 it stores 06/09/05. > > > > I can get it to work by changing it to a medium date type with no input > > mask > > and default to =DateSerial(Year(Date()),Month(Date())-1,1). I shrink the > > field so the user can only see month+Year but this is a bit fiddly. > > > > Is there a better way? Brain no longer functioning today... ;-) > > > > Sue > > > |