Dates in subform
in [Form]
|
Prev: Display a message while a form loads
Next: Combo ?
From: Daryl S on 5 Mar 2010 11:28 Jubiiab - Is the date showing up this way in the subform only? Is the date in the table correct? If so, check the formatting of the control on the subform - make sure you choose the date format you want. -- Daryl S "jubiiab via AccessMonster.com" wrote: > 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 > > . >
From: John W. Vinson on 5 Mar 2010 12:53 On Fri, 05 Mar 2010 09:30:17 GMT, "jubiiab via AccessMonster.com" <u51549(a)uwe> wrote: >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 mm/dd/yyyy is REQUIRED - OBLIGATORY - NOT OPTIONAL in internal SQL statements. The problem is that your code has a line Me.SampleID & ", " & (iCounter) & ", #" & dtDue & "#)" in building the SQL string. The dtDue value is being interpreted in accordance with your system regional settings, which I'm sure are d/m/yyyy. It's then constructing a SQL string like 123, 1, #5/3/2010# Since SQL date literals *completely ignore* your regional settings, this will be stored as May 3, not as March 5. The solution is to explicitly recast the date in the Access-acceptable format: 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 will store the value as a date/time (a double float number with no formatting included!), which can then be displayed in any format you like. -- John W. Vinson [MVP]
From: jubiiab via AccessMonster.com on 5 Mar 2010 13:04 Hi Daryl, No, its also showing up this way in the table. It's very strange. I think the code in the cmdInserteDate button are the cause of this wrong date format. And yes the data type in the tables are 100% correct. I have checked it many times now. Very strange. in the cmd button code we are using dim dtDue As Date. I also tried to change it to dim dtDue As String but that didnt work either. I am totally lost. :( Daryl S wrote: >Jubiiab - > >Is the date showing up this way in the subform only? Is the date in the >table correct? If so, check the formatting of the control on the subform - >make sure you choose the date format you want. > >> Hi guys – thx for your responses. >> >[quoted text clipped - 124 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
From: jubiiab via AccessMonster.com on 5 Mar 2010 13:32 JOHN IT WORKED!!!!!!!! Thank you so much Daryl and John. You guys are genious. Once again...thank you guys. To people who want to make somethin simelar. Here is the final code made by Daryl and John: Private Sub cmdInsertDate_Click() Dim dtDue As Date Dim iCounter As Integer dtDue = Me.StartDate 'the first due date iCounter = 0 Do Until dtDue > Me.EndDate DoCmd.SetWarnings (False) DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate]) Values (" & _ Me.SampleID & ", " & (iCounter) & ", #" & Format(dtDue, "mm/dd/yyyy") & "#)" dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date iCounter = iCounter + Me.CheckEvery 'increment iCounter DoCmd.SetWarnings (True) Loop Me.subDueDate.Requery End Sub This case is finally closed. :) John W. Vinson wrote: >>Hi guys thx for your responses. >> >[quoted text clipped - 7 lines] >>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 > >mm/dd/yyyy is REQUIRED - OBLIGATORY - NOT OPTIONAL in internal SQL statements. > >The problem is that your code has a line > >Me.SampleID & ", " & (iCounter) & ", #" & dtDue & "#)" > >in building the SQL string. The dtDue value is being interpreted in accordance >with your system regional settings, which I'm sure are d/m/yyyy. It's then >constructing a SQL string like > >123, 1, #5/3/2010# > >Since SQL date literals *completely ignore* your regional settings, this will >be stored as May 3, not as March 5. > >The solution is to explicitly recast the date in the Access-acceptable format: > >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 will store the value as a date/time (a double float number with no >formatting included!), which can then be displayed in any format you like. -- Message posted via http://www.accessmonster.com
From: Daryl S on 5 Mar 2010 14:16
Thanks John for the SQL Server tip. I would never have figured that one out! -- Daryl S "John W. Vinson" wrote: > On Fri, 05 Mar 2010 09:30:17 GMT, "jubiiab via AccessMonster.com" <u51549(a)uwe> > wrote: > > >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 > > mm/dd/yyyy is REQUIRED - OBLIGATORY - NOT OPTIONAL in internal SQL statements. > > The problem is that your code has a line > > Me.SampleID & ", " & (iCounter) & ", #" & dtDue & "#)" > > in building the SQL string. The dtDue value is being interpreted in accordance > with your system regional settings, which I'm sure are d/m/yyyy. It's then > constructing a SQL string like > > 123, 1, #5/3/2010# > > Since SQL date literals *completely ignore* your regional settings, this will > be stored as May 3, not as March 5. > > The solution is to explicitly recast the date in the Access-acceptable format: > > 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 will store the value as a date/time (a double float number with no > formatting included!), which can then be displayed in any format you like. > -- > > John W. Vinson [MVP] > . > |