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)

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers

 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...

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
Format that date as "m/dd/yy", returning a string, and then
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.

Problem in date fields

re: Problem in date fields

re: Problem in date fields

re: Problem in date fields

re: Problem in date fields

re: Problem in date fields

re: Problem in date fields

re: Problem in date fields

Similar Visual Basic 4 / 5 / 6 bytes

/bytes/development

/bytes/it

/bytes/about