 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Developer Toolkits / December 2004Date in US format only?
Hello I am using a lot of DateDiff and DateAdd in my queries. Do i have to convert all dates to "US format" before using the DateDiff and DateAdd functions? Regional settings=English(British) Thank you Jenny >Hello >I am using a lot of DateDiff and DateAdd in my queries. [quoted text clipped - 3 lines] >Thank you >Jenny If you include any literal typed-in dates they must be either in US mm/dd/yy or mm/dd/yyyy format, or an unambiguous format such as 27-Dec-2004. It is not necessary to make any changes to dates stored in your table or the format properties of form or report controls. A Date/Time is actually stored as a number, a count of days and fractions of a day since midnight, December 30, 1899; no format is stored with the number, and the date functions work with the value just fine. It's only when you're doing something like DateDiff("d", #6/1/2004#, Date()) that you need to use the above as June 1 rather than your reading of January 6. John W. Vinson[MVP] Thanks John I am using DateDiff("d",[PrevDate], Date()) (a) Without the "#"-Is this a hassle?- The [PrevDate] is currently is displayed in DD/MM/YYYY format (b)Another place i am using it is to determine the "Working days" The 'workingdays' function is from mvps.org by Arvin Meyer. When i use this to discount the 'Holidays' also it gives erronious results BUT when i type the dates in US format its ok. What should i do ? Thankkkkkkkks a loooot in advance Jenny > >Hello > >I am using a lot of DateDiff and DateAdd in my queries. [quoted text clipped - 19 lines] > > John W. Vinson[MVP] Thanks John I am using DateDiff("d",[PrevDate], Date()) (a) Without the "#"-Is this a hassle?- The [PrevDate] is currently is displayed in DD/MM/YYYY format (b)Another place i am using it is to determine the "Working days" The 'workingdays' function is from mvps.org by Arvin Meyer. When i use this to discount the 'Holidays' also it gives erronious results BUT when i type the dates in US format its ok. What should i do ? Thankkkkkkkks a loooot in advance Jenny > >Hello > >I am using a lot of DateDiff and DateAdd in my queries. [quoted text clipped - 19 lines] > > John W. Vinson[MVP] >Thanks John > >I am using DateDiff("d",[PrevDate], Date()) >(a) Without the "#"-Is this a hassle?- The [PrevDate] is currently is >displayed in DD/MM/YYYY format That makes ABSOLUTELY NO DIFFERENCE. DateDiff will use the numeric stored date; it neither knows nor cares how the display format is set. The # delimiter is needed only when you are *actually typing a date on the keyboard* into code or into a query criterion or the like; it is not needed for dates stored in table Date/Time fields, since Access already knows these are dates. >(b)Another place i am using it is to determine the "Working days" >The 'workingdays' function is from mvps.org by Arvin Meyer. >When i use this to discount the 'Holidays' also it gives erronious results >BUT when i type the dates in US format its ok. >What should i do ? You'll need to explain in more detail. Where are you typing the date? If you are typing a literal date then yes, you do need to use either US format or an unambiguous format such as dd-mmm-yyyy or yyyy.mm.dd (28-Dec-2004, 2004.12.28). Typing a literal dd/mm/yyyy date *will not work correctly*. John W. Vinson[MVP] Thanks for the quick response john. The function from mvps.org is called WorkingDays2.It calculates the number of working days between 2 dates and also number of holidays from a Holiday table. The Holiday table has a field Type=date/time. When the holidays are within the start date and the end date, there is a problem with the date format. I enter dates in dd/mm/yyyy format and when the function is run it ignores the days that are “within” my 2 dates BUT if I type the holidays in mm/dd/yyyy format in the table the holidays are taken into account. > >Thanks John > > [quoted text clipped - 22 lines] > > John W. Vinson[MVP] >I enter dates in dd/mm/yyyy format and when the function is run it ignores >the days that are within my 2 dates BUT if I type the holidays in >mm/dd/yyyy format in the table the holidays are taken into account. That's your solution, then! John W. Vinson[MVP] yes!that is 'A' solution but it would look silly if i ask my user to type the dates in US format > >I enter dates in dd/mm/yyyy format and when the function is run it ignores > >the days that are “within” my 2 dates BUT if I type the holidays in [quoted text clipped - 3 lines] > > John W. Vinson[MVP] >yes!that is 'A' solution >but it would look silly if i ask my user to type the dates in US format I guess I don't understand why the users would need to ever type the dates AT ALL. You're setting up a holiday table for an entire year, right? Just store it! I guess I'm not understanding the circumstances. I do not have the workdays function on my machine (I know *of* it but don't know its details), and I cannot, of course, see your computer. Could you explain in more detail the steps that you are carrying out and why and where the dates need to be manually entered? John W. Vinson[MVP] Thanks John,you are an MVp and also MPP(Most patient person) <smile> The Holidays table consists of a field where the HolidayDate is entered. The function looks up the holidays table and searches for holidays between the start date and End date and excludes them if they are there. My user will have to enter the holidays-say if there is a State holiday since the PM died or say the company decides on an extra holiday etc. > >yes!that is 'A' solution > >but it would look silly if i ask my user to type the dates in US format [quoted text clipped - 10 lines] > > John W. Vinson[MVP] >Thanks John,you are an MVp and also MPP(Most patient person) <smile> >The Holidays table consists of a field where the HolidayDate is entered. >The function looks up the holidays table and searches for holidays between >the start date and End date and excludes them if they are there. >My user will have to enter the holidays-say if there is a State holiday >since the PM died or say the company decides on an extra holiday etc. Ok... use a Form to enter the data, and enter the date in a textbox; this will use the computer's regional settings to correctly translate the date using that user's settings to parse it. Allen Browne has a good page on issues with international date formats: http://members.iinet.net.au/~allenbrowne/ser-36.html John W. Vinson[MVP] > >Thanks John,you are an MVp and also MPP(Most patient person) <smile> > >The Holidays table consists of a field where the HolidayDate is entered. [quoted text clipped - 11 lines] > > John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.