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 |
#11
|
|||
|
|||
Dates in subform
The start date and the end date is now included but I still have few more
problems. 1st: The table keep showing the date format like this: mm-dd-yyyy. Can I change it to dd-mm-yyyy? I have tried to change the input mask in the table but it didn’t work?? 2nd the iCounter is not working. I need [EveryMonth] field to start with 0 and then add the [checkEvery] value on the next. Like if the checkEvery value is 3 then it should count like this: 0 3 6 9 12…..and so on. [SampleID] [EveryMonth] [EveryMonthDate] [Done] 6 0 01-01-2011 (checkbox) 6 3 01-04-2011 (checkbox) 6 6 01-07-2011 (checkbox) 6 9 01-10-2012 (checkbox) 6 12 01-01-2012 (checkbox) 6 15 01-04-2012 (checkbox) 6 18 01-07-2012 (checkbox) 6 21 01-10-2012 (checkbox) 6 24 01-01-2013 (checkbox) Below you can see the new code for the mcdInsertDate button 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) & ", #" & dtDue & "#)" dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date DoCmd.SetWarnings (True) Loop Me.subDueDate.Requery End Sub Daryl S wrote: Jujiiab - I see from your sample you want the original date, and the 'every date' to be incremented. Also the start date. I don't know if you want the start date incremented weekly or always the original start date. In this code, I have the start date fixed. If you want, you can set up a variable and increment the same way the end date is incremented. Anyway, here goes (untested, plus check the field names): 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.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate], [EveryStartDate]) Values (" & _ Me.SampleID & ", " & (Me.CheckEvery * iCounter) & ", #" & dtDue & "#, #" & Me.StartDate & "#)" dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date Loop End Sub I didnt got this part... [quoted text clipped - 65 lines] I am sorry about my English. I know it’s not good but I hope you understand the question or please ask. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201003/1 |
#12
|
|||
|
|||
Dates in subform
Jubiiab -
The default date format is controlled by your Windows settings (language, date, and regional options). You can have it display any way you want on forms, queries, etc. by formatting the control that the date will show up in. The results you are showing in your code look like what you are asking for. Or are you showing what you want? If so, please show what you are getting so we can narrow down the problem. Remember if you are testing with the same SampleID to delete the old data from tblDate for that SampleID before adding the new data, as you only want to see what the new code adds. You indicated you were getting both the start and end date now, but it looks like the startdate is removed from the code? Can you please run the code you have and post both what was selected on the form and the results, then tell us what the problem is with the results? -- Daryl S "jubiiab via AccessMonster.com" wrote: The start date and the end date is now included but I still have few more problems. 1st: The table keep showing the date format like this: mm-dd-yyyy. Can I change it to dd-mm-yyyy? I have tried to change the input mask in the table but it didn’t work?? 2nd the iCounter is not working. I need [EveryMonth] field to start with 0 and then add the [checkEvery] value on the next. Like if the checkEvery value is 3 then it should count like this: 0 3 6 9 12…..and so on. [SampleID] [EveryMonth] [EveryMonthDate] [Done] 6 0 01-01-2011 (checkbox) 6 3 01-04-2011 (checkbox) 6 6 01-07-2011 (checkbox) 6 9 01-10-2012 (checkbox) 6 12 01-01-2012 (checkbox) 6 15 01-04-2012 (checkbox) 6 18 01-07-2012 (checkbox) 6 21 01-10-2012 (checkbox) 6 24 01-01-2013 (checkbox) Below you can see the new code for the mcdInsertDate button 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) & ", #" & dtDue & "#)" dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date DoCmd.SetWarnings (True) Loop Me.subDueDate.Requery End Sub Daryl S wrote: Jujiiab - I see from your sample you want the original date, and the 'every date' to be incremented. Also the start date. I don't know if you want the start date incremented weekly or always the original start date. In this code, I have the start date fixed. If you want, you can set up a variable and increment the same way the end date is incremented. Anyway, here goes (untested, plus check the field names): 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.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate], [EveryStartDate]) Values (" & _ Me.SampleID & ", " & (Me.CheckEvery * iCounter) & ", #" & dtDue & "#, #" & Me.StartDate & "#)" dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date Loop End Sub I didnt got this part... [quoted text clipped - 65 lines] I am sorry about my English. I know it’s not good but I hope you understand the question or please ask. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201003/1 . |
#13
|
|||
|
|||
Dates in subform
Hi daryl,
First I want to thank you for your time – you are truly a great person. Below you see the database, tables, forms and cmdButton code I am using right now: Database name: Sampledb Table name: tblSample [SampleID_PK] [StartDate] [EndDate] [CheckEvery] Table name: tblDate [DateID_PK] [SampleID_FK] [EveryMonth] [EveryMonthDate] [Done] Form name: frmSample Subform name: subDueDate I have created a button with caption: “Insert Date” and the code is: 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) & ", #" & dtDue & "#)" dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date DoCmd.SetWarnings (True) Loop Me.subDueDate.Requery End Sub Below you see the frmSample fields and data I inter and what the result I get. SampleID: [6] StartDate: [01-10-2011] EndDate: [01-10-2013] CheckEvery: [3] ‘Results I get right now from the Data above in the subDate form. Notice, I do get the [StartDate] and [EndDate] in the table. Only [EveryMonth] is wrong: SampleID] [EveryMonth] [EveryMonthDate] [Done] 6 3 10-01-2011 (checkbox) 6 3 01-01-2012 (checkbox) 6 3 04-01-2012 (checkbox) 6 3 07-01-2012 (checkbox) 6 3 10-01-2012 (checkbox) 6 3 01-01-2013 (checkbox) 6 3 04-01-2013 (checkbox) 6 3 07-01-2013 (checkbox) 6 3 10-01-2013 (checkbox) I want it to look more like the table below. Notice [EveryMonth]. It starts with 0 and adds with 3 because the value in [CheckEvery] is 3. If the value was 6 it had to add like this 0 6 12 18….: [SampleID] [EveryMonth] [EveryMonthDate] [Done] 6 0 01-10-2011 (checkbox) 6 3 01-01-2012 (checkbox) 6 6 01-04-2012 (checkbox) 6 9 01-07-2012 (checkbox) 6 12 01-10-2012 (checkbox) 6 15 01-01-2013 (checkbox) 6 18 01-04-2013 (checkbox) 6 21 01-07-2013 (checkbox) 6 24 01-10-2013 (checkbox) I have been in the Controlpanel * Regional and language Options. The Date format is set to dd-mm-yyyy but I get in the tblDate mm-dd-yyyy. Daryl S wrote: Jubiiab - The default date format is controlled by your Windows settings (language, date, and regional options). You can have it display any way you want on forms, queries, etc. by formatting the control that the date will show up in. The results you are showing in your code look like what you are asking for. Or are you showing what you want? If so, please show what you are getting so we can narrow down the problem. Remember if you are testing with the same SampleID to delete the old data from tblDate for that SampleID before adding the new data, as you only want to see what the new code adds. You indicated you were getting both the start and end date now, but it looks like the startdate is removed from the code? Can you please run the code you have and post both what was selected on the form and the results, then tell us what the problem is with the results? The start date and the end date is now included but I still have few more problems. [quoted text clipped - 78 lines] I am sorry about my English. I know it’s not good but I hope you understand the question or please ask. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201003/1 |
#14
|
|||
|
|||
Dates in subform
PLEASE NOTICE that in the eksample above I made a mistake in the result table
I get. I wrote in [EveryMonth] 3 3 3 3 3 3 3... but I get 6 6 6 6 6 6..... becasue of this code in the cmdInsert button: (Me.CheckEvery + iCounter) Sorry. jubiiab wrote: Hi daryl, First I want to thank you for your time – you are truly a great person. Below you see the database, tables, forms and cmdButton code I am using right now: Database name: Sampledb Table name: tblSample [SampleID_PK] [StartDate] [EndDate] [CheckEvery] Table name: tblDate [DateID_PK] [SampleID_FK] [EveryMonth] [EveryMonthDate] [Done] Form name: frmSample Subform name: subDueDate I have created a button with caption: “Insert Date” and the code is: 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) & ", #" & dtDue & "#)" dtDue = DateAdd("m", Me.CheckEvery, dtDue) 'increment the due date DoCmd.SetWarnings (True) Loop Me.subDueDate.Requery End Sub Below you see the frmSample fields and data I inter and what the result I get. SampleID: [6] StartDate: [01-10-2011] EndDate: [01-10-2013] CheckEvery: [3] ‘Results I get right now from the Data above in the subDate form. Notice, I do get the [StartDate] and [EndDate] in the table. Only [EveryMonth] is wrong: SampleID] [EveryMonth] [EveryMonthDate] [Done] 6 3 10-01-2011 (checkbox) 6 3 01-01-2012 (checkbox) 6 3 04-01-2012 (checkbox) 6 3 07-01-2012 (checkbox) 6 3 10-01-2012 (checkbox) 6 3 01-01-2013 (checkbox) 6 3 04-01-2013 (checkbox) 6 3 07-01-2013 (checkbox) 6 3 10-01-2013 (checkbox) I want it to look more like the table below. Notice [EveryMonth]. It starts with 0 and adds with 3 because the value in [CheckEvery] is 3. If the value was 6 it had to add like this 0 6 12 18….: [SampleID] [EveryMonth] [EveryMonthDate] [Done] 6 0 01-10-2011 (checkbox) 6 3 01-01-2012 (checkbox) 6 6 01-04-2012 (checkbox) 6 9 01-07-2012 (checkbox) 6 12 01-10-2012 (checkbox) 6 15 01-01-2013 (checkbox) 6 18 01-04-2013 (checkbox) 6 21 01-07-2013 (checkbox) 6 24 01-10-2013 (checkbox) I have been in the Controlpanel * Regional and language Options. The Date format is set to dd-mm-yyyy but I get in the tblDate mm-dd-yyyy. Jubiiab - [quoted text clipped - 19 lines] I am sorry about my English. I know it’s not good but I hope you understand the question or please ask. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201003/1 |
#15
|
|||
|
|||
Dates in subform
|
#16
|
|||
|
|||
Dates in subform
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 |
#17
|
|||
|
|||
Dates in subform
On Thu, 04 Mar 2010 13:56:27 GMT, "jubiiab via AccessMonster.com" u51549@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] |
#18
|
|||
|
|||
Dates in subform
On Thu, 04 Mar 2010 13:56:27 GMT, "jubiiab via AccessMonster.com" u51549@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] |
#19
|
|||
|
|||
Dates in subform
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 . |
#20
|
|||
|
|||
Dates in subform
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 |
Thread Tools | |
Display Modes | |
|
|