limhodav
asked on
SQL statement to search records based on dates
Dear experts,
I have a simple qn that has dumbfounded me and have not been able to find any solutions.
I create a Date field in my SQL database and have records in it. I use ADODB connection to connect to my VB program.
But this simple SQL statement refuses to work rs.Open "Select * from Testing Where Date = 03/05/2006"
Alternatives that I have tried include:-
1) putting single quotes around the date like '03/05/2006' but to no success.
2) testing other fields to know the connection works cos I could get results from them.
3) Reiterate thro the recordset
Do While Not rs.EOF
MsgBox rs!Date
rs.MoveNext
Loop
i could actually see the date 03/05/2006 appearing
what have I done wrong?
I have a simple qn that has dumbfounded me and have not been able to find any solutions.
I create a Date field in my SQL database and have records in it. I use ADODB connection to connect to my VB program.
But this simple SQL statement refuses to work rs.Open "Select * from Testing Where Date = 03/05/2006"
Alternatives that I have tried include:-
1) putting single quotes around the date like '03/05/2006' but to no success.
2) testing other fields to know the connection works cos I could get results from them.
3) Reiterate thro the recordset
Do While Not rs.EOF
MsgBox rs!Date
rs.MoveNext
Loop
i could actually see the date 03/05/2006 appearing
what have I done wrong?
Last Comment
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
what database do you have ? as I posted, I assumed Microsoft SQL Server (which is not the same as Microsoft Access)
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Dear all,
I have solved this problem by using this statement instead. Yes, I'm using mySQL database.
Actually, wat I wanted to do is to retrieve records based on the selected month n year.
Instead of comparing dates, I have separated the month and year and compared it as below.
sql = "Select * from Pemakaian Where Month(Date) = " & Month1 & " And Year(Tanggal) =" & Year.Text & " Order by Id"
I didn't know u could use a function on an SQL field e.g. Month(Date) like that.
As a form of appreciation for your help, I will split the points.
I have solved this problem by using this statement instead. Yes, I'm using mySQL database.
Actually, wat I wanted to do is to retrieve records based on the selected month n year.
Instead of comparing dates, I have separated the month and year and compared it as below.
sql = "Select * from Pemakaian Where Month(Date) = " & Month1 & " And Year(Tanggal) =" & Year.Text & " Order by Id"
I didn't know u could use a function on an SQL field e.g. Month(Date) like that.
As a form of appreciation for your help, I will split the points.
Visual Basic Classic
Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.
165K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
Using MsgBox Gives the exact string above.
I've also tried Cdate and Format function but didn't work too. I've spent 4 hours on this problem. Sigh ....
Btw, My Sql database only contains a Date field, NOT datetime.