Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1304
  • Last Modified:

Matching VB date to SQL convert date style

Easy points for someone here, but this is driving me nuts.  The SQL in the VBA code below is not returning any records becuase the date format in the table (even after converting) does not match the value in the datepicker control. Basically the VB control ends up being "11/3/2008" and the SQL query returns "11/03/2008". Any ideas how to get rid of the zero placeholder in the day portion of the SQL date or add one to VB? Any help is appreciated

Private Sub cmdGoDate_Click()
Dim rst As New ADODB.Recordset
Dim ssql As String
Dim Sdate As String
 
Sdate = DTPicker1.Value
 
ssql = "Select sum(trx_qty),count(distinct item_no) from invt_trx WHERE Convert(varchar,trx_date,101)like'" & Sdate & "'"
rst.Open ssql, conn, adOpenStatic, adLockOptimistic
 
If rst.Fields.Item(0) <> "" Then
txtUrcv.Text = rst.Fields.Item(0)
txtPrcv.Text = rst.Fields.Item(1)
Else: MsgBox "No items received on this date", vbOKOnly + vbExclamation, "No Data Found..."
End If
 
End Sub

Open in new window

0
dxhahn
Asked:
dxhahn
3 Solutions
 
exx1976Commented:
dt = split(myDate,"/")
dt = dt(0) & "/" & CInt(dt(1)) & "/" & dt(2)



Now you have a string with the leading zero removed from the day field..
0
 
RobSampsonCommented:
You can try this to convert the date picker date format to mm/dd/yyyy

Sdate = DTPicker1.Value
Sdate = Right("0" & Month(Sdate), 2) & "/" & Right("0" & Day(Sdate), 2) & "/" & Year(Sdate)

Regards,

Rob.
0
 
Shanmuga SundaramDirector of Software EngineeringCommented:
This should help

Sdate =format(DTPicker1.Value,"dd/MM/yyyy")
 
ssql = "Select sum(trx_qty),count(distinct item_no) from invt_trx WHERE Convert(varchar,trx_date,101)like'" & Sdate & "'"
rst.Open ssql, conn, adOpenStatic, adLockOptimistic
0
 
Shanmuga SundaramDirector of Software EngineeringCommented:
sorry this should be as
Sdate =format(DTPicker1.Value,"MM/dd/yyyy")
instead of
Sdate =format(DTPicker1.Value,"dd/MM/yyyy")

0
 
dxhahnAuthor Commented:
Shasunder's solution looked the the simplest so I tried it and it worked perfectly. Thank you all for your help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now