If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Dates in subform
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 he http://www.accessmonster.com/Uwe/For...7f5f23cf49duwe 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/For...forms/201003/1 . |
#22
|
|||
|
|||
Dates in subform
On Fri, 05 Mar 2010 09:30:17 GMT, "jubiiab via AccessMonster.com" u51549@uwe
wrote: Hi guys thx for your responses. @Daryl The iCounter works perfect now but I have the main problem date formation. If we cant 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] |
#23
|
|||
|
|||
Dates in subform
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/For...forms/201003/1 |
#24
|
|||
|
|||
Dates in subform
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 |
#25
|
|||
|
|||
Dates in subform
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@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] . |
#26
|
|||
|
|||
Dates in subform
On Fri, 5 Mar 2010 11:16:01 -0800, Daryl S
wrote: Thanks John for the SQL Server tip. I would never have figured that one out! It's not actually SQL/Server, Daryl - this is a purely Access JET/ACE issue, just with the way that Access parses date literals. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|