Problem in date fields

[creative1]

Hello everyone.
I am experiencing a strange problem that I can't fix on my own. I think I need expert's suggestions for this.
The problem is:
I want to print account statement (or any other report) from VB form based on an entered date in the masked fields (dd/mm/yy). I am using one form to display five reports. I send date to display report after formatting it (m/dd/yy). When I enter date range like this in masked textbox (DD/MM/YY)

23/10/2007 - 29/10/2007 pls note I get two digit in date after formatting it
the report works fine.
However, on trying dates like this

09/10/2007 - 12/10/2007 note that first date is now 10/9/2007 not the second
or
the output contains all records till date 12/10/2007
23/10/2007 - 06/11/2007 second date is one digit after formatting

I get output with all dates even previous month.... till date 23/10/2007 nothing from next month.

if I use 8/10/2007 - 9/10/2007 both dates one digit dates
I get no records found

I am really worried because I can't understand the reason; here is code I am using (sliced code of the module because its really long)

Form has two date boxes (and 2 masked fields)

Private Sub Form_Load()
If EndingDate_msk.Visible = False And formcall <> 1 And formcall <> 6 And formcall <> 5 And formcall <> 4 And formcall <> 2 And formcall <> 7 Then ' if closing sheet is calling this form
StartingDate_msk.Text = Format$(Date, "dd/mm/yyyy")
Else
    EndingDate_msk.Text = Format$(DateSerial(Year(Now), Month(Now), 0), "dd/mm/yyyy")
    Dim Yr, Mnth
    If Month(Now) = 1 Then
        Yr = Year(Now) - 1
        Mnth = 12
    Else
        Yr = Year(Now)
        Mnth = Month(Now) - 1
    End If
    StartingDate_msk.Text = Format$(DateSerial(Year(EndingDate_msk.Text), Month(EndingDate_msk.Text), 1), "dd/mm/yyyy")
End If
End Sub

Private Sub btnView_Click()
On Error GoTo err:
Dim d As String
cfromdate.Text = Format$(StartingDate_msk.Text, "m/dd/yy")
ctodate.Text = Format$(EndingDate_msk.Text, "m/dd/yy")
'd = StartingDate_msk.Text
d = StartingDate_msk.Text
Dim d1 As String
Dim d4 As String
Dim d5 As String
d1 = (d)
d4 = Mid(d1, 1, 2)
d5 = Mid(d1, 4, 2)
'validlidate endingdate
Dim dtwo As String
dtwo = EndingDate_msk.Text
'dtwo = endingdate_mask.Text
Dim d12 As String
Dim d42 As String
Dim d52 As String
d12 = (d)
d42 = Mid(dtwo, 1, 2)
d52 = Mid(dtwo, 4, 2)
 
strTo = Format$(StartingDate_msk.Text, "m/dd/yy")
strFrom = Format$(EndingDate_msk.Text, "m/dd/yy")
............(sliced)
'*******************************************************
'******           RptAccountStatement         **********
'*******************************************************
ElseIf formcall = 7 Then
 
    If txtAccountNo.Text = "" Then
        MsgBox ("Please select an account no first")
        If txtAccountNo.Enabled = True Then
            txtAccountNo.SetFocus
        End If
        Exit Sub
    End If
strAccountNo = Trim(txtAccountNo.Text)
''''''''''''''''''''''''''''for single date and a particular account no'''''''''''''''
       If onday_option.Value = True And txtAccountNo.Text <> "" Then
            If (Val(d4) > 31 Or Val(d5) > 12) Or StartingDate_msk.Text = "__/__/____" Then
                    MsgBox ("Please enter a valid date!!!")
                     StartingDate_msk.SetFocus
                     Exit Sub
             Else
                     strSQL = "SHAPE {select distinct vendor.customer,inv_total.account_no,vendor.address,vendor.city ,vendor.province ,vendor.postal_code ,inv_total.date,tran_id,inv_total.po_number,total_amt as t,gst as g,(t+g) as total from inv_total,vendor,product_codes where vendor.account_no=" & strAccountNo & " and inv_total.date = #" & strTo & "# and inv_total.account_no = vendor.account_no}  AS AccountStatement1 APPEND ({select tran_id,product_codes.description,inv_detail.quantity as q1,product_codes.unit_size,inv_detail.price as p1, (p1*q1) as subtotal from inv_detail,product_codes  where inv_detail.item_code=product_codes.item_code }  AS AccountStatement2 RELATE 'tran_id' TO 'tran_id') AS AccountStatement2"
                     strdates = "                For  " + strTo
                        RptAccountStatement.Show 1
             End If
 ''''''''''''''''''''''''''''for two dates and a specified account number''''''''''''''''''''''
        ElseIf Bydates_option.Value = True And txtAccountNo.Text <> "" Then
            If (Val(d4) > 31 Or Val(d5) > 12 Or Val(d42) > 31 Or Val(d52) > 12) Or StartingDate_msk.Text = "__/__/____" Or EndingDate_msk.Text = "__/__/____" Then
                     MsgBox ("Please enter a valid dates for report first")
                     Exit Sub
            Else
                strSQL = "SHAPE {select distinct vendor.customer,inv_total.account_no,vendor.address,vendor.city ,vendor.province ,vendor.postal_code ,inv_total.date,tran_id,inv_total.po_number,total_amt as t,gst as g,(t+g) as total from inv_total,vendor,product_codes where vendor.account_no=" & strAccountNo & " and inv_total.date between #" & strTo & "# and #" & strFrom & "# and inv_total.account_no = vendor.account_no}  AS AccountStatement1 APPEND ({select tran_id,product_codes.description,inv_detail.quantity as q1,product_codes.unit_size,inv_detail.price as p1, (p1*q1) as subtotal from inv_detail,product_codes  where inv_detail.item_code=product_codes.item_code }  AS AccountStatement2 RELATE 'tran_id' TO 'tran_id') AS AccountStatement2"
                        strdates = "       From  " + strTo + "   to   " + strFrom
                        strtitle = "Vendor History"
                        RptAccountStatement.Show 1
 
            End If
 '''''''''''''''''''for no date criteria and no cat selection'''''''''''''
        ElseIf onday_option.Value = False And Bydates_option.Value = False And txtAccountNo.Text = "" Then
                   MsgBox ("Please Select Any Category and enter a valid date to print a report")
                    Exit Sub
        End If
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
  End If
'''''''''''''''''''''''''''''(sliced)

Please help me solve this problem

[QVeen72]

Hi,

Try to use DatePicker instead of the MaskedTextbox..

Regards
Veena

[creative1]

but i m printing report by using variables

[QVeen72]

Hi,

Even if you use Variables, after Formatting in the Textbox, the date Values itself are changing as per your post..
Dates are a bit complicated, It again depends on the Regional setting of the User. if user has got m/d/yy format and you have Formatted with dd/mm/yyyy, "m"and "d" are Interchanged for dates 1 to 9..
Date Picker would avoid such Complications..

Regards
Veena

[creative1]

thanks I'll try date picker. I never use it and don't know how it works. If yo any code sample pls send me.
regards

[debasisdas]

Include Microsoft Windows Common Controls-2 6.0 (SP4) from components .

Then try to use the value property.

[creative1]

when I tried the same code on another machine that had Vista installed on it. I worked fine and not any problem at all.
whats the reason? can someone tell

[Killer42]

I think the important thing to remember is that the masked edit control just returns a string. So when you try to format that as though it were a date, you force VB to first try and convert it to a date value.

Thus, when you say ctodate.Text = Format$(EndingDate_msk.Text, "m/dd/yy") you are saying to do the following...

1. Take the string which is in EndingDate_msk.Text and convert it to a date value using whatever defaults are set up on this machine, then
2. Format that date as "m/dd/yy", returning a string, and then
3. Place that string in ctodate.Text.

You can see that you have already potentially stuffed up your data in step 1, no matter what you do with it afterward.

There are a number of ways around this sort of problem. One is to use the DatePicker control as has been suggested. Another technique is to use an unambiguous date format when interacting with the user. For example, a format such as "DD-MMM-YYYY" (Eg. "01-JUN-2007") will never be misinterpreted.