D
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
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 said: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 said: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!
[quoted text clipped - 20 lines]I just realized that if I have these values:format….PLEASE HELP. You can see the cmdInsertDate button code in the
previous post.
--
.