Dates in subform
in [Form]
|
Prev: Display a message while a form loads
Next: Combo ?
From: jubiiab via AccessMonster.com on 4 Mar 2010 08:56 I just realized that if I have these values: StartDate: 30-01-2011 EndDate: 30-01-2013 CheckEvery: 3 I get the correct date format results (dd/mm/yyyy): [SampleID] [EveryMonth] [EveryMonthDate] [Done] 6 6 30-01-2011 (checkbox) 6 6 30-04-2012 (checkbox) 6 6 30-07-2012 (checkbox) 6 6 30-10-2012 (checkbox) 6 6 30-01-2012 (checkbox) 6 6 30-04-2013 (checkbox) 6 6 30-07-2013 (checkbox) 6 6 30-10-2013 (checkbox) 6 6 30-01-2013 (checkbox) But If the day is lower then 12 I get the wrong date format mm/dd/yyyy. I always want it two show this format dd/mm/yyyy. I think the code in the cmdInsertDate button makes the wrong date format. Must be the add function dtDue = DateAdd("m", Me.CheckEvery, dtDue. How can I get the correct date format….PLEASE HELP. You can see the cmdInsertDate button code in the previous post. -- Message posted via http://www.accessmonster.com
From: John W. Vinson on 4 Mar 2010 11:46 On Thu, 04 Mar 2010 13:56:27 GMT, "jubiiab via AccessMonster.com" <u51549(a)uwe> wrote: >I just realized that if I have these values: > >StartDate: 30-01-2011 >EndDate: 30-01-2013 >CheckEvery: 3 > >I get the correct date format results (dd/mm/yyyy): >[SampleID] [EveryMonth] [EveryMonthDate] [Done] > 6 6 30-01-2011 (checkbox) > 6 6 30-04-2012 (checkbox) > 6 6 30-07-2012 (checkbox) > 6 6 30-10-2012 (checkbox) > 6 6 30-01-2012 (checkbox) > 6 6 30-04-2013 (checkbox) > 6 6 30-07-2013 (checkbox) > 6 6 30-10-2013 (checkbox) > 6 6 30-01-2013 (checkbox) > >But If the day is lower then 12 I get the wrong date format mm/dd/yyyy. I >always want it two show this format dd/mm/yyyy. I think the code in the >cmdInsertDate button makes the wrong date format. Must be the add function >dtDue = DateAdd("m", Me.CheckEvery, dtDue. How can I get the correct date >format�.PLEASE HELP. You can see the cmdInsertDate button code in the >previous post. I think you're running into a builtin feature of Access. It was programmed by Americans, who use the mm/dd/yyyy format; as a result, a date literal MUST be either in that format, or an unambiguous format such as yyyy-mm-dd. Don't confuse data DISPLAY with data STORAGE. Your code needs to use the mm/dd/yyyy format if you're converting text strings to dates, but you can set the Format property of a Date/Time field to dd-mm-yyyy with no difficulties. Your original message has scrolled off my list; if you could repost the code in your cmdInsertDate button perhaps someone can show you what needs to be tweaked. -- John W. Vinson [MVP]
From: John W. Vinson on 4 Mar 2010 11:52 On Thu, 04 Mar 2010 13:56:27 GMT, "jubiiab via AccessMonster.com" <u51549(a)uwe> wrote: >But If the day is lower then 12 I get the wrong date format mm/dd/yyyy. I >always want it two show this format dd/mm/yyyy. I think the code in the >cmdInsertDate button makes the wrong date format. Must be the add function >dtDue = DateAdd("m", Me.CheckEvery, dtDue. How can I get the correct date >format�.PLEASE HELP. You can see the cmdInsertDate button code in the >previous post. OK... I went back to Google Groups and found your code. A one-line fix should solve the problem: Private Sub cmdInsertDate_Click() Dim dtDue As Date Dim iCounter As Integer dtDue = Me.StartDate 'the first due date iCounter = Me.CheckEvery.Value Do Until dtDue > Me.EndDate DoCmd.SetWarnings (False) DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate]) Values (" & _ Me.SampleID & ", " & (Me.CheckEvery + iCounter) & ", #" & _ Format(dtDue, "mm/dd/yyyy") & "#)" dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date DoCmd.SetWarnings (True) Loop Me.subDueDate.Requery End Sub This just formats your date into the Access mm/dd/yyyy form, rather than using your computer's Regional setting. -- John W. Vinson [MVP]
From: Daryl S on 4 Mar 2010 12:19 Jubiiab - This will fix the [EveryMonth] issue - start iCounter at zero; insert iCounter into the record; and increment it by Me.CheckEvery in the loop (replace these lines in your code): iCounter = 0 Do Until dtDue > Me.EndDate DoCmd.SetWarnings (False) DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate]) Values (" & _ Me.SampleID & ", " & (iCounter) & ", #" & dtDue & "#)" dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date iCounter = iCounter + Me.CheckEvery 'increment iCounter As for the date format - is it only this table that is formatted as dd-mm-yyyy? Or is it all date fields? If it is only this table, then check the format of that field in the table. You may be right in that it is based on the StartDate and EndDate controls. Are these controls dates or text? If they are text, switch them to date. Let us know! -- Daryl S "jubiiab via AccessMonster.com" wrote: > I just realized that if I have these values: > > StartDate: 30-01-2011 > EndDate: 30-01-2013 > CheckEvery: 3 > > I get the correct date format results (dd/mm/yyyy): > [SampleID] [EveryMonth] [EveryMonthDate] [Done] > 6 6 30-01-2011 (checkbox) > 6 6 30-04-2012 (checkbox) > 6 6 30-07-2012 (checkbox) > 6 6 30-10-2012 (checkbox) > 6 6 30-01-2012 (checkbox) > 6 6 30-04-2013 (checkbox) > 6 6 30-07-2013 (checkbox) > 6 6 30-10-2013 (checkbox) > 6 6 30-01-2013 (checkbox) > > But If the day is lower then 12 I get the wrong date format mm/dd/yyyy. I > always want it two show this format dd/mm/yyyy. I think the code in the > cmdInsertDate button makes the wrong date format. Must be the add function > dtDue = DateAdd("m", Me.CheckEvery, dtDue. How can I get the correct date > format….PLEASE HELP. You can see the cmdInsertDate button code in the > previous post. > > -- > Message posted via http://www.accessmonster.com > > . >
From: jubiiab via AccessMonster.com on 5 Mar 2010 04:30
Hi guys – thx for your responses. @Daryl The iCounter works perfect now but I have the main problem – date formation. If we can't fix this, I will not be able to use this so please help. :0( Let me try to explain again what the problem is right now with the date formation. If the dd > 12 I get the correct date format as I want: dd/mm/yyyy If the dd < 12 I get the wrong date format : mm/dd/yyyy I don't understand why its keep changing the date format based on the value of dd?? In my tables all [date] fields has the data type “date/time”. Also [startDate] and [EndDate] This is what I get in the subform when I enter these values: StartDate: [12-10-2011] EndDate: [12-10-2013] CheckEvery: [3] SampleID] [EveryMonth] [EveryMonthDate] [Done] 6 0 10-12-2011 (checkbox) 6 3 01-12-2012 (checkbox) 6 6 04-12-2012 (checkbox) 6 9 07-12-2012 (checkbox) 6 12 10-12-2012 (checkbox) 6 15 01-12-2013 (checkbox) 6 18 04-12-2013 (checkbox) 6 21 07-12-2013 (checkbox) 6 24 10-12-2013 (checkbox) StartDate: [13-10-2011] EndDate: [13-10-2013 CheckEvery: [3] [SampleID] [EveryMonth] [EveryMonthDate] [Done] 6 0 13-10-2011 (checkbox) 6 3 13-01-2012 (checkbox) 6 6 13-04-2012 (checkbox) 6 9 13-07-2012 (checkbox) 6 12 13-10-2012 (checkbox) 6 15 13-01-2013 (checkbox) 6 18 13-04-2013 (checkbox) 6 21 13-07-2013 (checkbox) 6 24 13-10-2013 (checkbox) Notice the change of the date formation based on the value of dd?? (The last example is the correct date formation: dd/mm/yyyy.) @John Hi John. You can follow this thread here: http://www.accessmonster.com/Uwe/Forum.aspx/access-forms/62237/Dates-in-subform#a47f5f23cf49duwe I used Format(dtDue, "dd/mm/yyyy") in the cmdInserteDate button but it didn't work. I also did the other way around Format(dtDue, "mm/dd/yyyy") but that didn't work either. :( I also did this: Private Sub cmdInsertDate_Click() Dim dtDue As Date Dim iCounter As Integer dtDue = Format(Me.StartDate, "dd/mm/yyyy") 'the first due date iCounter = 0 Do Until dtDue > Format(Me.EndDate, "dd/mm/yyyy") DoCmd.SetWarnings (False) DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate]) Values (" & _ Me.SampleID & ", " & (iCounter) & ", #" & Format(dtDue, "dd/mm/yyyy") & "#)" dtDue = DateAdd("m", Me.CheckEvery, Format(dtDue, "dd/mm/yyyy")) 'increment the due date iCounter = iCounter + Me.CheckEvery 'increment iCounter DoCmd.SetWarnings (True) Loop Me.subDueDate.Requery End Sub ….Still not working. Can't understand why this date format making such a huge problem?? I have all the information of my database and tables in this thread. It's just a test example so it is not that big. Maybe you guys could make the database and the form on your computer and see if you get the same problem? It will only take 5 min. to create it or I can also email my version to you if you like? Daryl S wrote: >Jubiiab - > >This will fix the [EveryMonth] issue - start iCounter at zero; insert >iCounter into the record; and increment it by Me.CheckEvery in the loop >(replace these lines in your code): > >iCounter = 0 > >Do Until dtDue > Me.EndDate > >DoCmd.SetWarnings (False) > >DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate]) >Values (" & _ >Me.SampleID & ", " & (iCounter) & ", #" & dtDue & "#)" >dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date >iCounter = iCounter + Me.CheckEvery 'increment iCounter > >As for the date format - is it only this table that is formatted as >dd-mm-yyyy? Or is it all date fields? If it is only this table, then check >the format of that field in the table. You may be right in that it is based >on the StartDate and EndDate controls. Are these controls dates or text? If >they are text, switch them to date. > >Let us know! > >> I just realized that if I have these values: >> >[quoted text clipped - 20 lines] >> format….PLEASE HELP. You can see the cmdInsertDate button code in the >> previous post. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201003/1 |