excel vba sql query help

[gracehskuo]

hi ,how to change the sql query date between as below startdate and enddate string??i want to use date between query from excel vba to retrieve data from sql server.please help.thanks.

Dim enddate As String
Dim startdate As String
Dim partno As String

partno = ComboBox1.Text
startdate = Format(Dtpdata.DTPicker1.Value, "dd MMM yyyy")
enddate = Format(Dtpdata.DTPicker1.Value, "dd MMM yyyy")

“SELECT SpcData.PartNo, SpcData.Dimension, SpcData.MachineNo, SpcData.Date FROM SPC.dbo.SpcData SpcData WHERE (SpcData.Date Between {ts '2008-03-24 00:00:00'} And {ts '2008-03-27 00:00:00'}) AND (SpcData.PartNo='" & partno & "') GROUP BY SpcData.PartNo, SpcData.Dimension, SpcData.MachineNo, SpcData.Date

[Hack]

Are the dates in question in a specific cell on a specific worksheet?

[gracehskuo]

Hack:
hi,thanks reply.The date is from the sql database table field.
for example:i want to retrieve the data from 23-03-2008 to 28-03-08.so i use 2 dtpicker to retrieve the data from the sql server to the excel worksheet.
i just want to know how to change the sql query of date use the dtpicker select date.
(SpcData.Date Between {ts '2008-03-24 00:00:00'} And {ts '2008-03-27
00:00:00'})


please help.

[si_the_geek]

Everything you need to know (and based on your previous threads, should know already) is in two articles from our Database Development FAQs/Tutorials (at the top of this forum):

How do I use values (numbers, strings, dates) in SQL statements?
How can I put the value of a variable/control into my SQL statement?

[gracehskuo]

hi,thank ya reply.the previous problem has been solved.but then i face another problem is using "where statement as below statement cannot to sql the query have error message "type mismatch'

this is the current code:

"SELECT SpcData.PartNo, SpcData.Dimension, SpcData.MachineNo, Sum(SpcData.SampleNo/5) AS 'Sum'" & Chr(13) & "" & Chr(10) & "FROM SPC.dbo.SpcData SpcData WHERE (SpcData.PartNo='" & partno & "')and (SpcData.Date BETWEEN '" & startdate & "' and '" & enddate & "')GROUP BY SpcData.PartNo, SpcData.Dimension, SpcData.MachineNo " _

this is the previous work able statement is without date between query

"SELECT SpcData.PartNo, SpcData.Dimension, SpcData.MachineNo, Sum(SpcData.SampleNo/5) AS 'Sum'" & Chr(13) & "" & Chr(10) & "FROM SPC.dbo.SpcData SpcData WHERE (SpcData.PartNo='" & partno & "')GROUP BY SpcData.PartNo, SpcData.Dimension, SpcData.MachineNo " _

please help.thanks.

[si_the_geek]

That should be fine for SQL Server, as long as startdate and enddate are formatted appropriately (what you showed in your first post will fail if the client OS language is not English).