By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,057 Members | 1,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,057 IT Pros & Developers. It's quick & easy.

Problem in date fields

100+
P: 274
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
  1. Private Sub Form_Load()
  2. 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
  3. StartingDate_msk.Text = Format$(Date, "dd/mm/yyyy")
  4. Else
  5.     EndingDate_msk.Text = Format$(DateSerial(Year(Now), Month(Now), 0), "dd/mm/yyyy")
  6.     Dim Yr, Mnth
  7.     If Month(Now) = 1 Then
  8.         Yr = Year(Now) - 1
  9.         Mnth = 12
  10.     Else
  11.         Yr = Year(Now)
  12.         Mnth = Month(Now) - 1
  13.     End If
  14.     StartingDate_msk.Text = Format$(DateSerial(Year(EndingDate_msk.Text), Month(EndingDate_msk.Text), 1), "dd/mm/yyyy")
  15. End If
  16. End Sub

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnView_Click()
  2. On Error GoTo err:
  3. Dim d As String
  4. cfromdate.Text = Format$(StartingDate_msk.Text, "m/dd/yy")
  5. ctodate.Text = Format$(EndingDate_msk.Text, "m/dd/yy")
  6. 'd = StartingDate_msk.Text
  7. d = StartingDate_msk.Text
  8. Dim d1 As String
  9. Dim d4 As String
  10. Dim d5 As String
  11. d1 = (d)
  12. d4 = Mid(d1, 1, 2)
  13. d5 = Mid(d1, 4, 2)
  14. 'validlidate endingdate
  15. Dim dtwo As String
  16. dtwo = EndingDate_msk.Text
  17. 'dtwo = endingdate_mask.Text
  18. Dim d12 As String
  19. Dim d42 As String
  20. Dim d52 As String
  21. d12 = (d)
  22. d42 = Mid(dtwo, 1, 2)
  23. d52 = Mid(dtwo, 4, 2)
  24.  
  25. strTo = Format$(StartingDate_msk.Text, "m/dd/yy")
  26. strFrom = Format$(EndingDate_msk.Text, "m/dd/yy")
  27. ............(sliced)
  28. '*******************************************************
  29. '******           RptAccountStatement         **********
  30. '*******************************************************
  31. ElseIf formcall = 7 Then
  32.  
  33.     If txtAccountNo.Text = "" Then
  34.         MsgBox ("Please select an account no first")
  35.         If txtAccountNo.Enabled = True Then
  36.             txtAccountNo.SetFocus
  37.         End If
  38.         Exit Sub
  39.     End If
  40. strAccountNo = Trim(txtAccountNo.Text)
  41. ''''''''''''''''''''''''''''for single date and a particular account no'''''''''''''''
  42.        If onday_option.Value = True And txtAccountNo.Text <> "" Then
  43.             If (Val(d4) > 31 Or Val(d5) > 12) Or StartingDate_msk.Text = "__/__/____" Then
  44.                     MsgBox ("Please enter a valid date!!!")
  45.                      StartingDate_msk.SetFocus
  46.                      Exit Sub
  47.              Else
  48.                      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"
  49.                      strdates = "                For  " + strTo
  50.                         RptAccountStatement.Show 1
  51.              End If
  52.  ''''''''''''''''''''''''''''for two dates and a specified account number''''''''''''''''''''''
  53.         ElseIf Bydates_option.Value = True And txtAccountNo.Text <> "" Then
  54.             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
  55.                      MsgBox ("Please enter a valid dates for report first")
  56.                      Exit Sub
  57.             Else
  58.                 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"
  59.                         strdates = "       From  " + strTo + "   to   " + strFrom
  60.                         strtitle = "Vendor History"
  61.                         RptAccountStatement.Show 1
  62.  
  63.             End If
  64.  '''''''''''''''''''for no date criteria and no cat selection'''''''''''''
  65.         ElseIf onday_option.Value = False And Bydates_option.Value = False And txtAccountNo.Text = "" Then
  66.                    MsgBox ("Please Select Any Category and enter a valid date to print a report")
  67.                     Exit Sub
  68.         End If
  69.  
  70. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  71.  
  72.   End If
  73. '''''''''''''''''''''''''''''(sliced)
Please help me solve this problem
Nov 7 '07 #1
Share this Question
Share on Google+
7 Replies


QVeen72
Expert 100+
P: 1,445
Hi,

Try to use DatePicker instead of the MaskedTextbox..

Regards
Veena
Nov 7 '07 #2

100+
P: 274
but i m printing report by using variables
Nov 7 '07 #3

QVeen72
Expert 100+
P: 1,445
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
Nov 7 '07 #4

100+
P: 274
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
Nov 7 '07 #5

debasisdas
Expert 5K+
P: 8,127
Include Microsoft Windows Common Controls-2 6.0 (SP4) from components .

Then try to use the value property.
Nov 7 '07 #6

100+
P: 274
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
Nov 8 '07 #7

Expert 5K+
P: 8,434
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.
Nov 8 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.