[RESOLVED] Error in Date field format and calculation

[matrik02]

I use Microsoft Access and DTPicker, listview, visual basic 6

I have 'Start_date' field and 'End_date' field, 'hari' field and 'peruntukan' field.

the start_date field, End_date field name are set as text in the table and it is define as date .

Both fields 'hari' field and 'peruntukan' field are set as number in the table.

The date in the 'Start date' field are calculate by adding the date selected in the DTPicker with the days value in the 'hari' field.For example

Date 12/05/2007 are selected in the DTPicker

DTPicker1.Format = dtpCustom
DTPicker1.CustomFormat = "dd/MM/yyyy"

The value in the hari field = -10 days
Then the date in the 'Start date' field are calculate and show 02/05/2007.
All value in the 'Start date' field name is ok and no problem.

I am facing a problem at this part.
The date in the 'end date' field are calculate by adding the date in the 'Start date' with the days value in the 'peruntukan' field.For example

Start date field = 02/05/2007

rec("start_date") = Format(newDate, "dd/MM/yyyy")

The value in the 'peruntukan' feld = 6
The the date in the 'End_date field are calculate and show 10/02/2007. It suppose to be 07/05/2007.

newDate1 = DateAdd("d", rec("peruntukan") - 1, rec("start_date"))
 rec("end_date") = Format(newDate1, "dd/MM/yyyy")

.

When the form load, both 'start date' field and 'end date' field are automatically calculate base on the date in dt picker.

Private Sub Form_Load()
DTPicker1.Format = dtpCustom
DTPicker1.CustomFormat = "dd/MM/yyyy"

end sub.

Why I got the error calculation in End_date field and also its date format ?But not all value in the End_date field are error in its calculation and it date format, only certain record in the End_date field.

I attach the source code and have a look and test. I try to solve it on my own but still fail to find a solution.Here is source code in the listview

Public Sub AddLvItem()
On Error Resume Next

    Dim newDate As Date
    Dim newDate1 As Date
    rec.MoveFirst
    Do Until rec.EOF
        If rec("hari") = "" Then
        
   
            Set lvItem = lvJadual.ListItems.Add(, , "")
            lvItem.ListSubItems.Add , , "" & rec("end_date")
            lvItem.ListSubItems.Add , , "" & rec("peruntukan")
            lvItem.ListSubItems.Add , , "" & rec("aktiviti")
            lvItem.ListSubItems.Add , , "" & rec("sasaran")
            lvItem.ListSubItems.Add , , "" & rec("jenisinput")
            lvItem.ListSubItems.Add , , "" & rec("tindakan")
            'lvItem.ListSubItems.Add , , "" & rec("hari")
            
            'Isnull(rec("hari")) ialah test untuk tengo kalau nilai yang dibaca dpd field hari ialah null atau kosong
        ElseIf IsNull(rec("hari")) Then
        
    
        
            Set lvItem = lvJadual.ListItems.Add(, , "")
             lvItem.ListSubItems.Add , , "" & rec("end_date")
            lvItem.ListSubItems.Add , , "" & rec("peruntukan")
            lvItem.ListSubItems.Add , , "" & rec("aktiviti")
            lvItem.ListSubItems.Add , , "" & rec("sasaran")
            lvItem.ListSubItems.Add , , "" & rec("jenisinput")
            lvItem.ListSubItems.Add , , "" & rec("tindakan")
            
        Else
            newDate = DateAdd("d", rec("hari"), DTPicker1)
            
            rec("start_date") = Format(newDate, "dd/MM/yyyy")
            newDate1 = DateAdd("d", rec("peruntukan") - 1, rec("start_date"))
          
          rec("end_date") = Format(newDate1, "dd/MM/yyyy")
         
          
            Set lvItem = lvJadual.ListItems.Add(, , "" & Format(newDate, "dd/MM/yyyy"))
            lvItem.ListSubItems.Add , , "" & rec("end_date")
            lvItem.ListSubItems.Add , , "" & rec("peruntukan")
            lvItem.ListSubItems.Add , , "" & rec("aktiviti")
            lvItem.ListSubItems.Add , , "" & rec("sasaran")
            lvItem.ListSubItems.Add , , "" & rec("jenisinput")
            lvItem.ListSubItems.Add , , "" & rec("tindakan")
        
           
            
            rec.Update
        End If
        rec.MoveNext
    Loop
End Sub

[brucevde]

Is the Date regional setting on your computer set to mm/dd/yyyy?
When you convert strings to dates, VB has to assume that the string is in the format of the regional setting.

If the regional setting is mm/dd/yyyy, then 02/05/2007 = Feb 5 2007. Add 5 days and newdate1 = Feb 10.

Format(newDate1, "dd/MM/yyyy") = 10/02/2007.

Regardless, changing this line

newDate1 = DateAdd("d", rec("peruntukan") - 1, rec("start_date"))
to
newDate1 = DateAdd("d", rec("peruntukan") - 1, newdate)

should solve this problem, this time.

[matrik02]

Thank you so much . This a long time story.I try to find a solution to identify the error that cause this problem untill you come and help me.

Thank you again.