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

dxhahnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dxhahnAuthor Commented:
Shasunder's solution looked the the simplest so I tried it and it worked perfectly. Thank you all for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.