Dates in subform

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
jubiiab
2/25/2010 12:29:08 PM
access.forms 6864 articles. 1 followers. Follow

24 Replies
728 Views

Similar Articles

[PageSpeed] 39

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
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
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
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
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
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
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
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
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
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
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
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
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
jubiiab
3/3/2010 8:54:09 AM
*bump

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

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

Similar Artilces:

filter by date and action
I have a table with three columns: Name Date Action Fred Today this Fred Today this Fred Today that Wilma Today this I want to sort by name and by date, but only if 'this' was done more than once on the same day, and not if 'that' was done on the same day,They have to perform this more than once, and they cannot have performed that on the same day. If not in excel, mssql maybe? Thanks, Harry On Aug 18, 1:53=A0pm, Harry <hamac...@gmail.com> wrote: > I have a table with three co...

Calculate Years/Months Between Dates and then Average
We are using the formula =DATEDIF(A1,NOW(),"y") & "." & DATEDIF(A1,NOW(),"ym") to calculate the years and months from an employee hire date. Is there an easy way to average the years and months for a group of employees with the results of that formula? You could use: =AVERAGE(A1:A10*1) Array-entered, meaning press ctrl-shift-enter. But you could also just change your current formula to: =1*(DATEDIF(A1,NOW(),"y") & "." & DATEDIF(A1,NOW(),"ym")) Then just use: =AVERAGE(A1:A10) which is *not* array-entered. ...

Transaction Date VS Business Date
Our accounting department needs/wants to have all RMS SO reporting by business date opposed to transaction date. Usually these are the same unless you have sales and batches that close after midnight. For our store I would like to set 3 a.m. as the business day closing time. So when I run any type of reports I would like to get the information for a business date (range) starting at 3 a.m. till 3 a.m. the next day. I guess I could accomplish this by subtracting 3 hours from all the transaction (date and) times but I would have to do this for every single report which would be a lot of ...

Post Dated Checks
I had to prepare some post dated checks and we did not have fiscal periods set up yet to match the dates of the checks. Currently the check batches are waiting to be posted. What is the best way to handle post dated checks? If I create new fiscal years to match these dates, I will end up having 4 Open years. Is this the only way to do it? You can manage post dated checks in Microsoft Dynamics GP with the Payment Document Management module available on the Feature Pack 1 DVD image. For more info, take a look at MVP Mohammad Daoud's post on the subject at: https://communit...

Remove "Date: Yesterday" or "Date: Monday" from Outlook Inbox?
Outlook mail inbox now has headers that tell you what day the the email was sent. I find this visually annoying. Each emailalready has the time and day it was sent. I don't need the daily titles in the midst of the list of emails. How do I make them go away? I think you are talking about OL 2003 & the Arrange in Groups setting. Change it in View, Arrange by, uncheck Show in Groups. "silky" wrote: > Outlook mail inbox now has headers that tell you what day the the email was > sent. I find this visually annoying. Each emailalready has the time and day >...

strange date format problem
Hi, I downloaded some data on excel using Bloomberg. One column has dates and the other has numbers. Dates appear as 01/01/1980 format which I want in Jan-80 format. I have changed the format cell properties but it still appears as 01/01/1980. I have to goto individual cell and press F2 <enter> so see the effect. Dont know why? I cant even copy paste format after that .... TIA/amag I am not sure, but is your workbook set to Auto-Recalculate? Try pushing <F9> to do a manual re-calculation, see if that changes the formats. If it does, go to Tools (on the menu bar), Options, Ca...

How to sort by name then date?
I would like to look through all the emails to a particular person. If I click on the "To" column I can see all the emails to a particular person (actually there is a problem with that which I will mention next) but they seem to be in random order. Is there a way to have them sorted by date? (i.e. by name first, then by date). Actually, sorting on the "To" column does not get all the emails sent to a given email address. Because Outlook uses the "display as" field. It will often give different "display as" names to the same email address. C...

Concatenate including a date so that the date appears as a date
When I try to join the contents of several cells, one of which is a date and time obtained from =now(), the date and time part appears as a number. How can I make it appear as a date and time? I have tried changing the cell formats - probably there is a combination that works but I haven't found it. I also tried copying the original date cell to another cell and changing the latter cell's format and concatenating using that latter cell, but still no success. Use the TEXT function. For example, =TEXT(NOW(),"mmmm d, yyyy") would give January 6, 2010 "dd...

if statement with dates #2
I would like to have a statement like: IF(a1>0,1/1/2004) with 1/1/2004 beint a date. EXCEL is interpreting 1/1/2004 as an arithmetic statement and giving me the result of the arithemtic. I could put in the numeric offste for the date and write IF(a1>0,37987), formatting the cell as a date, but this seems awkward. What else can I do? TIA, Sanford ...

Textbox Information to Subform
I have a subform that I need to contain information from a main form. The sub form is linked by studentID, but I also need the tardy ID, TardyID, to populate the subDhall field txtTardyID. I tried the AfterUpdate of TardyID with this: Me!subDhall.txtTardyID=Me!frmTardyAdd.TardyID and Me!subDhall.frmTardyAdd.txtTardyID=Me!frmTardyAdd.TardyID to no avail. Anyone have any tips? -- Thanks As Always Rip "Ripper" <Ripper@discussions.microsoft.com> wrote in message news:753FE132-F478-4952-ABEE-EA884BB75A19@microsoft.com... >I have a subform that I need to contain informat...

Count Unique Cells, within a date range (dates stored in separate column).
Hello All: I am attempting to create a formula that will count the number of unique cells (alpha-numeric) in an entire column (no set length, so ex: "$B:$B"). This count will be based on a date range (Between 2 dates). The dates are stored in another column (Ex: "$A:$A"). The following is an example of the cells. Dates PO_Num 1/2/2010 A123456 1/3/2010 B453453 2/2/2010 A123456 3/4/2010 C2342F3K 4/3/2010 123456 I would like to know a formula that can count the number of different PO's based on a given date range. Please help me if you...

Counting Dates?
=COUNTIF(A1:A100,"<" & NOW()) This works good for specific cells. I have a similar question. How would I do the same thing given that: 1) You only have a starting date 2) Your only wanting to count work days (in this case 4 day/wk) up to NOW() Thank you for all your help. Aviator On Thu, 6 Jan 2005 12:53:03 -0800, "Aviator" <Aviator@discussions.microsoft.com> wrote: >=COUNTIF(A1:A100,"<" & NOW()) > >This works good for specific cells. I have a similar question. > >How would I do the same thing given that: >1) You only ha...

Subform Visiblity Access 2003
Hello all, I'm currently using 4 subforms on a main form. The first form (FM_Staff) shows a a list of staff names and review dates. What I want to do is when you click on the name it make visible the 4th subform (FM_rdc) and it displays the correct record (of the staff member clicked on). I can do it when I have FM_RDC as a stand alone form but can not seem to call from one Sub form to an other. To hide the subform from the mainform, set the subform control visibility to false. So if you have a subform named Sub1 that is contained by a control named Sub1Contro...

An expression for dates from and to
I am trying to create an expression to select items from a date field falling from a certain date to a certain date. I am obviously missing something in my expression. The field name is DateComplete. How can I set up this expression to pull date from a certain date to a certain date? -- Ronnie Hi Ronnie, An example of the SQL would be: SELECT * FROM [TABLE NAME] WHERE DateComplete Between #1/1/2006# And #1/1/2007# ; On May 23, 3:52 pm, Ronnie <Ron...@discussions.microsoft.com> wrote: > I am trying to create an expression to select items from a date field falling > from a...

Refering to fields on master from from subform
I have a text box on a subform, that I am using an IIf statement in, so that if the field ([IUSA]) on the parent form Is Null, returns "No data for this record", else returns "IUSA Data" as a caption. The code: =IIf([Form].[ProspectForm].[IUSA] Is Null,"RefUSA record not connected","RefUSA Data") Am I referring to the field on the parent form (ProspectForm) incorrectly, because it isn't working? It returns #Name? thanks in advance! magmike Where you have form, it should be forms: =IIf([Forms].[ProspectForm].[IUSA] Is Null,"RefUSA record no...

Returning only MAX(date) not all dates
I want to be able to list the most current date an item is checked out. As I was working through the query I used max as an aggregate function but I still can't get the simple result I want. I'd rather list the fields I have chosen and somehow select just the most current checkout date. Any help would be greatly appreciated. Here is my query: SELECT tblTerritory.TerritoryID, [TerritoryName] & " " & [TerritoryDescription] AS Description, tblTerritory.TerritoryTypeID, tblTerritoryType.TerritoryType, Max(tblTerritoryCheckout.DateCheckedOut) AS Checked...

System Date
Can anyone help me on how to force the system date to a member field of "Date" type? I could not find any help on msdn that comes with visual c++. Any hint or help will be appreciated. Thanks, Behrouzk Are you just trying to get the current date. You can use CTime::GetCurrentTime() or COleDateTime::GetCurrentTime() Tom "Behrouzk" <Behrouzk@discussions.microsoft.com> wrote in message news:D8FA4B6B-73CE-4058-B50C-C9BEBAB67E68@microsoft.com... > Can anyone help me on how to force the system date to a member field of > "Date" type? I could not fi...

Conditional Formatting
Hi, I have a spreadsheet that is for tracking work. I would like to have conditional formatting so that if the date in column E is between todays date and 4 weeks away then make it blue, if it is a date that is after 4 weeks away then make it yellow but if it is in the past make it red. Can this be done with conditional formatting or does it need vba? Also it would be nice if the whole row changes colour even if just the condition is on the date which is in column E - but if that can't be done then just the cell is fine. Thanks Assume the dates start in E2, select all dates w...

missing subform data
Can someone please point me in the right direction? My main client form has several tab controls with subforms. Upon Access launch some of those subforms on the first record do not display data. The data is in the table - not displayed. UNTIL -- I select another client, where the data is fine, and return to the first record client. The data displays properly now. How do I get it to display out of the gate? It was working fine until I added code to the main form's on current event. I've scoured and find nothing I know of that could impact. So...I must have done something ...

Time and date problem
Hello I have spreadsheet cell (B1) that contains a date and time: 05/11/2003 13:45:50 I have another series of cells (A1 to A100) that contain an increment of 30 seconds: 30.00 I would like to add the seconds to the date and time putting the result in column C, I have tried to do: =B1+(A1/100000) but the answer is wrong by 4 seconds and then gets worse the further down the column I go. Can somebody show me how to add the seconds to the date please. Thanks Tom Tom, Right method, wrong divider! 86,400 secs in a day.. change your 100000 to 86400 and you're there. Nikos Y. (nyannaco ...

Date subtraction -How to not show negative when 2nd date not entered
Hi, I have a section of a worksheet that just needs to show working days between two dates. For example, an order was entered on 9/2/07 and complete on 9/15/07 The formula =NETWORKDAYS(A1, A9) works perfectly except that until the "completed date" ise filled in, a negative number appears. Is there a way to have that remain blank until both dates have been entered? Thanks! Hi Edward, change your formula as =IF(A9="","",NETWORKDAYS(A1, A9)) -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER...

PivotTable question
Thanks Debra, For some reason when on that Date field button in the Pivot table & choose the Group & Show Detail > Group I get a "Cannot group that selection" pop up message. I'll give the other tips a shot, I'm sure those will work. thanks again -- -= cj = ----------------------------------------------------------------------- -= cj =-'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1467 View this thread: http://www.excelforum.com/showthread.php?threadid=26305 If the date column contains text, or blank cells, you'll see th...

Incorrect date in creation date field
I am having an issue when creating a template I am trying to use the date field called creation date but when it is inerted into the document it shows an incorrect date? For example the file was created and saved on May 26, 2006 but the creation date field show November 26, 2002. Has anyone else seen this problem? ...

Last Balanced Date
Does anyone know a quick way to get a list of accounts and the last date they were balanced? I'm finding myself having to select each account and manually check to see if it is up to date. I would think there would be a report of some kind that would display this somewhere, but I can't seem to find one. Any suggestions? Thanks in advance...Skip Skip, I've been thinking of the same exact question ever since I made the upgrade from Money 2004 to 2005. The ability to see the last balanced date across all accounts did exist in the 2004 version, and it was very useful. It...

Convert Text Entry to Date Format
Hello We were sent a large spreadsheet with hundreds of dates entered as text in this format: 04151985. Each entry has the green triangle in the upper left corner of the cells designating the data as text. We need to change the format to 04/15/1985. I have tried DateValue with no luck. I get a "#value" error I have also tried converting the text to a number first and then formating as a date using "format/cells" but I only get a string of pound signs ####. Please help. Thanks Art Sorry, I just read some of the archived postings and figured out the solution. ...