Dates in subform

  • Follow


I need to make a Form where the user type:

SampleID: 1234

StartDate: 01-01-2010

EndDate: 01-01-2015

CheckEvery: 3

(There will be more fields but these are the most important)

On this form there should be a subForm because in the subForm it will show
all the due dates:

          0               3                 6                  9
12                15
01-01-2010   01-03-2010    01-06-2010    01-09-2010     01-12-2010     01-03-
2012    

...and it continues until it reaches the last date 01-01-2015. 

In the “checkEvery” textbox the user should be able to write a number. In
this case I wrote 3 – means that the subform should write the date for every
3rd month. I don’t know how to make this subform to add dates automatically?

Is it also possible to make a checkbox for every date it shows? So you can
mark that the sampleID has been analyzed that date?

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/Forums.aspx/access-forms/201002/1

0
Reply jubiiab 2/25/2010 12:29:08 PM

On this form there should be a subForm because in the subForm it will show
all the due dates:
          0               3                   6                 9
12                  15
01-01-2010   01-03-2010    01-06-2010    01-09-2010     01-12-2010     01-03-
2012

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201002/1

0
Reply jubiiab 2/25/2010 12:31:38 PM

Jujiiab -

Since you didn't give any details, the answer will be general, but you can 
take the idea and use your table/field/control names to do it.  I guessed you 
would have the SampleId and DueDate fields in the table behind your subform, 
but you will need to use whatever is required for your table.

Set up the form like you said, then add a button for the user to click on 
when the data is ready.  In the code behind the button, you want to check to 
make sure the data is valid (e.g. both dates are entered and the end date is 
at least [CheckEvery] months after the start date.

Then append records as needed in a loop, something like this:

Dim dtDue as Date

dtDue = DateAdd("m", Me.CheckEvery, Me.StartDate)	'the first due date
Do Until dtDue > Me.EndDate
   DoCmd.RunSQL "INSERT Into tablename ([SampleId],[DueDate]) Values (" & _
       Me.SampleID & ", #" & dtDue & "#)"
   dtDue = DateAdd("m", Me.CheckEvery, dtDue)	'increment the due date
Loop

Then requery the subform.
Me.subformname.requery

-- 
Daryl S


"jubiiab via AccessMonster.com" wrote:

> I need to make a Form where the user type:
> 
> SampleID: 1234
> 
> StartDate: 01-01-2010
> 
> EndDate: 01-01-2015
> 
> CheckEvery: 3
> 
> (There will be more fields but these are the most important)
> 
> On this form there should be a subForm because in the subForm it will show
> all the due dates:
> 
>           0               3                 6                  9
> 12                15
> 01-01-2010   01-03-2010    01-06-2010    01-09-2010     01-12-2010     01-03-
> 2012    
> 
> ...and it continues until it reaches the last date 01-01-2015. 
> 
> In the “checkEvery” textbox the user should be able to write a number. In
> this case I wrote 3 – means that the subform should write the date for every
> 3rd month. I don’t know how to make this subform to add dates automatically?
> 
> Is it also possible to make a checkbox for every date it shows? So you can
> mark that the sampleID has been analyzed that date?
> 
> 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/Forums.aspx/access-forms/201002/1
> 
> .
> 
0
Reply Utf 2/25/2010 5:17:02 PM

Hi Daryl

What kind of details do u need more? 

I will look at your answer tomorrow at work. thx for your time.


Daryl S wrote:
>Jujiiab -
>
>Since you didn't give any details, the answer will be general, but you can 
>take the idea and use your table/field/control names to do it.  I guessed you 
>would have the SampleId and DueDate fields in the table behind your subform, 
>but you will need to use whatever is required for your table.
>
>Set up the form like you said, then add a button for the user to click on 
>when the data is ready.  In the code behind the button, you want to check to 
>make sure the data is valid (e.g. both dates are entered and the end date is 
>at least [CheckEvery] months after the start date.
>
>Then append records as needed in a loop, something like this:
>
>Dim dtDue as Date
>
>dtDue = DateAdd("m", Me.CheckEvery, Me.StartDate)	'the first due date
>Do Until dtDue > Me.EndDate
>   DoCmd.RunSQL "INSERT Into tablename ([SampleId],[DueDate]) Values (" & _
>       Me.SampleID & ", #" & dtDue & "#)"
>   dtDue = DateAdd("m", Me.CheckEvery, dtDue)	'increment the due date
>Loop
>
>Then requery the subform.
>Me.subformname.requery
>
>> I need to make a Form where the user type:
>> 
>[quoted text clipped - 27 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/Forums.aspx/access-forms/201002/1

0
Reply jubiiab 2/25/2010 7:16:26 PM

What you are asking to create is better done in Excel with its autofill.  
Fill in the date in 3 cells, highlight those three, use cursor to grab the 
lower right corner and then drag to the right until you the number of sells 
filled you need.  A little popup will follow along telling you the date in 
the last cell.

-- 
Build a little, test a little.


"jubiiab via AccessMonster.com" wrote:

> On this form there should be a subForm because in the subForm it will show
> all the due dates:
>           0               3                   6                 9
> 12                  15
> 01-01-2010   01-03-2010    01-06-2010    01-09-2010     01-12-2010     01-03-
> 2012
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201002/1
> 
> .
> 
0
Reply Utf 2/25/2010 10:39:04 PM

@ Karyl I need to make it in Access and not Excel. This is just a small part
of the Form and Database but a very necessary part.

I know it can be done somehow in access I just don’t know how to do yet. 

@ Daryl or anyone,

You asked for more information. Right now I just made a simple test database:

Database name: Sampledb
Table name: tblSample [SampleID] [StartDate] [EndDate] [CheckEvery]
Form name: frmSample

I think I need to make a query and make some kind of crosstable and then add
it as subform? I just can’t make the code for that. Please help.


Daryl S wrote:
>Jujiiab -
>
>Since you didn't give any details, the answer will be general, but you can 
>take the idea and use your table/field/control names to do it.  I guessed you 
>would have the SampleId and DueDate fields in the table behind your subform, 
>but you will need to use whatever is required for your table.
>
>Set up the form like you said, then add a button for the user to click on 
>when the data is ready.  In the code behind the button, you want to check to 
>make sure the data is valid (e.g. both dates are entered and the end date is 
>at least [CheckEvery] months after the start date.
>
>Then append records as needed in a loop, something like this:
>
>Dim dtDue as Date
>
>dtDue = DateAdd("m", Me.CheckEvery, Me.StartDate)	'the first due date
>Do Until dtDue > Me.EndDate
>   DoCmd.RunSQL "INSERT Into tablename ([SampleId],[DueDate]) Values (" & _
>       Me.SampleID & ", #" & dtDue & "#)"
>   dtDue = DateAdd("m", Me.CheckEvery, dtDue)	'increment the due date
>Loop
>
>Then requery the subform.
>Me.subformname.requery
>
>> I need to make a Form where the user type:
>> 
>[quoted text clipped - 27 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/Forums.aspx/access-forms/201002/1

0
Reply jubiiab 2/26/2010 8:40:56 AM

Jujiab -

You need to create a new table to hold the 'due date' records.  Something 
like this:

tblDueDate
SampleDueDateID   (PK - autonumber)
SampleID    (FK)
DueDate   
other fields you may need...


Then on your main form, you can add a button to create the new due date 
records using the code I put in the last posting.

-- 
Daryl S


"jubiiab via AccessMonster.com" wrote:

> @ Karyl I need to make it in Access and not Excel. This is just a small part
> of the Form and Database but a very necessary part.
> 
> I know it can be done somehow in access I just don’t know how to do yet. 
> 
> @ Daryl or anyone,
> 
> You asked for more information. Right now I just made a simple test database:
> 
> Database name: Sampledb
> Table name: tblSample [SampleID] [StartDate] [EndDate] [CheckEvery]
> Form name: frmSample
> 
> I think I need to make a query and make some kind of crosstable and then add
> it as subform? I just can’t make the code for that. Please help.
> 
> 
> Daryl S wrote:
> >Jujiiab -
> >
> >Since you didn't give any details, the answer will be general, but you can 
> >take the idea and use your table/field/control names to do it.  I guessed you 
> >would have the SampleId and DueDate fields in the table behind your subform, 
> >but you will need to use whatever is required for your table.
> >
> >Set up the form like you said, then add a button for the user to click on 
> >when the data is ready.  In the code behind the button, you want to check to 
> >make sure the data is valid (e.g. both dates are entered and the end date is 
> >at least [CheckEvery] months after the start date.
> >
> >Then append records as needed in a loop, something like this:
> >
> >Dim dtDue as Date
> >
> >dtDue = DateAdd("m", Me.CheckEvery, Me.StartDate)	'the first due date
> >Do Until dtDue > Me.EndDate
> >   DoCmd.RunSQL "INSERT Into tablename ([SampleId],[DueDate]) Values (" & _
> >       Me.SampleID & ", #" & dtDue & "#)"
> >   dtDue = DateAdd("m", Me.CheckEvery, dtDue)	'increment the due date
> >Loop
> >
> >Then requery the subform.
> >Me.subformname.requery
> >
> >> I need to make a Form where the user type:
> >> 
> >[quoted text clipped - 27 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/Forums.aspx/access-forms/201002/1
> 
> .
> 
0
Reply Utf 2/26/2010 2:38:01 PM

Hi Daryl

Right now I have:

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

dtDue = DateAdd("m", Me.CheckEvery, Me.StartDate)    'the first due date
Do Until dtDue > Me.EndDate
  DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate])
Values (" & _
  Me.SampleID & ", " & Me.CheckEvery & ", #" & dtDue & "#)"
  dtDue = DateAdd("m", Me.CheckEvery, dtDue)    'increment the due date
Loop

End Sub


When I fill out the the form with this data:

SampleID: 6
StartDate: 01-01-2011
EndDate:  01-01-2013
CheckEvery: 3

…and press the "Instert Date" button I get this data in the subform:


[SampleID] [EveryMonth] [EveryMonthDate] [Done]
       6                    3                04-01-2011     (checkbox)
       6                    3                07-01-2011     (checkbox) 
       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) 


Dates are totally wrong and i need the startDate to be in the subform like
the endDate.

Thx for your help. 
Daryl S wrote:
>Jujiab -
>
>You need to create a new table to hold the 'due date' records.  Something 
>like this:
>
>tblDueDate
>SampleDueDateID   (PK - autonumber)
>SampleID    (FK)
>DueDate   
>other fields you may need...
>
>Then on your main form, you can add a button to create the new due date 
>records using the code I put in the last posting.
>
>> @ Karyl I need to make it in Access and not Excel. This is just a small part
>> of the Form and Database but a very necessary part.
>[quoted text clipped - 43 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/Forums.aspx/access-forms/201003/1

0
Reply jubiiab 3/1/2010 11:18:48 AM

I didnt got this part...

I wanted more like this:

[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) 


jubiiab wrote:
>Hi Daryl
>
>Right now I have:
>
>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
>
>dtDue = DateAdd("m", Me.CheckEvery, Me.StartDate)    'the first due date
>Do Until dtDue > Me.EndDate
>  DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate])
>Values (" & _
>  Me.SampleID & ", " & Me.CheckEvery & ", #" & dtDue & "#)"
>  dtDue = DateAdd("m", Me.CheckEvery, dtDue)    'increment the due date
>Loop
>
>End Sub
>
>When I fill out the the form with this data:
>
>SampleID: 6
>StartDate: 01-01-2011
>EndDate:  01-01-2013
>CheckEvery: 3
>
>…and press the "Instert Date" button I get this data in the subform:
>
>[SampleID] [EveryMonth] [EveryMonthDate] [Done]
>       6                    3                04-01-2011     (checkbox)
>       6                    3                07-01-2011     (checkbox) 
>       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) 
>
>Dates are totally wrong and i need the startDate to be in the subform like
>the endDate.
>
>Thx for your help. 
>>Jujiab -
>>
>[quoted text clipped - 15 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/Forums.aspx/access-forms/201003/1

0
Reply jubiiab 3/1/2010 11:29:09 AM

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

-- 
Daryl S


"jubiiab via AccessMonster.com" wrote:

> I didnt got this part...
> 
> I wanted more like this:
> 
> [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) 
> 
> 
> jubiiab wrote:
> >Hi Daryl
> >
> >Right now I have:
> >
> >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
> >
> >dtDue = DateAdd("m", Me.CheckEvery, Me.StartDate)    'the first due date
> >Do Until dtDue > Me.EndDate
> >  DoCmd.RunSQL "INSERT Into tblDate ([SampleID],[EveryMonth],[EveryMonthDate])
> >Values (" & _
> >  Me.SampleID & ", " & Me.CheckEvery & ", #" & dtDue & "#)"
> >  dtDue = DateAdd("m", Me.CheckEvery, dtDue)    'increment the due date
> >Loop
> >
> >End Sub
> >
> >When I fill out the the form with this data:
> >
> >SampleID: 6
> >StartDate: 01-01-2011
> >EndDate:  01-01-2013
> >CheckEvery: 3
> >
> >…and press the "Instert Date" button I get this data in the subform:
> >
> >[SampleID] [EveryMonth] [EveryMonthDate] [Done]
> >       6                    3                04-01-2011     (checkbox)
> >       6                    3                07-01-2011     (checkbox) 
> >       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) 
> >
> >Dates are totally wrong and i need the startDate to be in the subform like
> >the endDate.
> >
> >Thx for your help. 
> >>Jujiab -
> >>
> >[quoted text clipped - 15 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/Forums.aspx/access-forms/201003/1
> 
> .
> 
0
Reply Utf 3/1/2010 3:15:01 PM

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/Forums.aspx/access-forms/201003/1

0
Reply jubiiab 3/2/2010 8:52:50 AM

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/Forums.aspx/access-forms/201003/1
> 
> .
> 
0
Reply Utf 3/2/2010 3:25:01 PM

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/Forums.aspx/access-forms/201003/1

0
Reply jubiiab 3/3/2010 8:43:19 AM

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/Forums.aspx/access-forms/201003/1

0
Reply jubiiab 3/3/2010 8:54:09 AM

*bump

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

0
Reply jubiiab 3/4/2010 9:19:12 AM

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

0
Reply jubiiab 3/4/2010 1:56:27 PM

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]
0
Reply John 3/4/2010 4:46:36 PM

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]

0
Reply John 3/4/2010 4:52:34 PM

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
> 
> .
> 
0
Reply Utf 3/4/2010 5:19:02 PM

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

0
Reply jubiiab 3/5/2010 9:30:17 AM

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
> 
> .
> 
0
Reply Utf 3/5/2010 4:28:01 PM

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]
0
Reply John 3/5/2010 5:53:51 PM

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

0
Reply jubiiab 3/5/2010 6:04:20 PM

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

0
Reply jubiiab 3/5/2010 6:32:02 PM

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]
> .
> 
0
Reply Utf 3/5/2010 7:16:01 PM

24 Replies
372 Views

(page loaded in 0.325 seconds)


Reply: