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