Dates in subform

[Daryl S]

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

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?

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.

--



.

 

[John W. Vinson]

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.

 

[jubiiab via AccessMonster.com]

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.

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]

 

[jubiiab via AccessMonster.com]

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.

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.

 

[Daryl S]

Thanks John for the SQL Server tip. I would never have figured that one out!

--
Daryl S

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]

Thanks John for the SQL Server tip. I would never have figured that one out!

It's not actually SQL/Server, Daryl - this is a purely Access JET/ACE issue,
just with the way that Access parses date literals.