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

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

.

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

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

*bump

--
Message posted via http://www.accessmonster.com

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

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]

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]

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

.

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