From: MR EDDD on 6 Jun 2010 08:25 Hey all, I have a control box in my form that asks the user to enter a date. It then takes the date and turns it into the default date so that it will show up on the next record. It accepts the date and saves it to the table in the correct format (dd/mm/yyyy) but then when it copies it over to the next form it converts the date into american format (mm/dd/yyyy). I have worked out it must be something to do with the code. This is what I currently have in the relevant section of coding. Private Sub DateFirstSession_AfterUpdate() DateFirstSession.DefaultValue = "#" & DateFirstSession.Value & "#" End Sub Any assistance as to how I should code it so that it accepts the correct date format would be greatly appreciated. I have my regional settings in Windows correctly set to Australia and am using Access 2003. -- cheers
From: Linq Adams via AccessMonster.com on 6 Jun 2010 08:59 "There once was a tall man from Perth..." Sorry! Don't remember the rest of the limerick! But there is, in fact, a tall gentleman from Perth named Allen Browne, considered by many to be the biggest Access Guru in the galaxy! He has a small white papar that discusses the use of non-USA date formats, and might be of interest to you: http://www.allenbrowne.com/ser-36.html For this particular problem, you might try formatting the date in the DefaultValue assignment itself: Private Sub DateFirstSession_AfterUpdate() DateFirstSession.DefaultValue = "#" & Format(DateFirstSession.Value, "dd/mm/yyyy") & "#" End Sub or maybe Private Sub DateFirstSession_AfterUpdate() DateFirstSession.DefaultValue = Format("#" & DateFirstSession.Value & "#", "dd/mm/yyyy") End Sub Format outputs as string or text, but Access is ususally pretty tolerant in this regard, i.e. if it looks like a Date, even if it's s string, Access will accept it as a Date. Strings that look like dates, for example, can be used accurately in the DateAdd() and DateDiff() functions -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201006/1
From: KenSheridan via AccessMonster.com on 6 Jun 2010 09:36 The DefaultValue property is always a string expression regardless of the date type, so should be wrapped in quotes characters: DateFirstSession.DefaultValue = """" & DateFirstSession.Value & """" The pairs of contiguous quotes within the delimiting quotes characters are each interpreted as a single literal quotes character. When you use the # date delimiter in this context it assumes a US or otherwise internationally unambiguous format, so it takes the date as a string expression in the format dd/mm/yyyy and, because the US short date format is mm/dd/yyyy, transposes the months and days provided that the result is a legitimate date. Ken Sheridan Stafford, England MR EDDD wrote: >Hey all, > >I have a control box in my form that asks the user to enter a date. It then >takes the date and turns it into the default date so that it will show up on >the next record. It accepts the date and saves it to the table in the >correct format (dd/mm/yyyy) but then when it copies it over to the next form >it converts the date into american format (mm/dd/yyyy). I have worked out it >must be something to do with the code. This is what I currently have in the >relevant section of coding. > >Private Sub DateFirstSession_AfterUpdate() >DateFirstSession.DefaultValue = "#" & DateFirstSession.Value & "#" >End Sub > >Any assistance as to how I should code it so that it accepts the correct >date format would be greatly appreciated. I have my regional settings in >Windows correctly set to Australia and am using Access 2003. > -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201006/1
From: Marshall Barton on 6 Jun 2010 11:41 MR EDDD wrote: >I have a control box in my form that asks the user to enter a date. It then >takes the date and turns it into the default date so that it will show up on >the next record. It accepts the date and saves it to the table in the >correct format (dd/mm/yyyy) but then when it copies it over to the next form >it converts the date into american format (mm/dd/yyyy). I have worked out it >must be something to do with the code. This is what I currently have in the >relevant section of coding. > >Private Sub DateFirstSession_AfterUpdate() >DateFirstSession.DefaultValue = "#" & DateFirstSession.Value & "#" >End Sub > >Any assistance as to how I should code it so that it accepts the correct >date format would be greatly appreciated. I have my regional settings in >Windows correctly set to Australia and am using Access 2003. When Access evaluates the expression in the DefaultValue string, the date in the # signs must be either in USA style or an unambiguous style (eg. yyyy-mm-dd) I prefer the latter just because it is unambiguous. Your code should be more like: DateFirstSession.DefaultValue=Format(DateFirstSession,"\#yyyy-m-d\#") Note: If you use / as the separator, then Access will translate that to the separator specified in your Windows local settings which may not be a legal separator. In this case the Format would need to be: Format(dt,"\#yyyy\/m\/d\#") Note: The Windows local settings will be used to display a date whenever you do not specify the Format property in whatever displays the date. That means that you can not look at a date in a table/querys/form/report and tell anything about whether it is "correct" or not. Note: Access will also use the Windows local settings whenever it it needs to convert a string that looks like a date to an actual date value. You can use the CDate function to do that explicitly or more implicitly when you specify a date type Format in a text box's Format property, even if the Format property is different style than the Windows setting (this is very confusing to me). The bottom line is your code should always specify a date string in the unambiguous date style enclosed in # signs. -- Marsh MVP [MS Access]
|
Pages: 1 Prev: NumLock Next: Open form to ID and add in the record if not there |