Problem: SQL string to return date to VB6 App 1

[Sprowler]

Hello everyone.

I have VB6 app connected to a MySQL 4.1 database.

I am using ADO and myODBC to connect to the database on a webhost, and successfully return data to my forms.

My problem is that I need an SQL string to call records where the date is equal to today.

The string I've used is like "SELECT * FROM tblCalls WHERE CallDate='" & Format(Date,"yy-mm-dd")'". When I populate my ADO control, with the SQL as its recordsource, I get all records back, i.e. it won't filter by today's date.

Can anyone tell me how I need to construct my SQL string to apply the filter I want?

Apologies if there's already a post about this, but I've tried to search this forum, and the MySQL forum with no luck.

Thanks in advance for your help.

 

[ericbrunson]

Try "where calldate = curdate()"

If that doesn't do what you need look at the manual page for date functions:

http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

Try to do it all in SQL and not convert VB constructs into strings.

If you've illadvisedly created the column CallDate as a string instead of a date, consider changing it into a date, or else use the mysql functions to convert curdate() into a string of the same form.

 

[Sprowler]

Hi eric!

Thanks for your prompt reply. The field in the database is a "datetime" field. It pulls out the date into my VB form in my VB format, i.e. "dd/mm/yyyy", but I couldn't find out how to filter for records for a certain date. When I formatted it, it just gave me back all dates.

The reason I put the date in single quotes in my SQL string was because I used the MySQL Control Center to run a SQL string, and that seemed to be how it liked it doing it in the Control Center.

Obviously I was chsing the wrong stick, so I'll try your method, and the link you suggest.

Thanks again for your help, and I'll post a message to let you know how I got on. There's a star on offer for this, because it's a very important part of my app.

 

[Sprowler]

Hi again eric! Just tried your suggestion and it looks fine.

However, I have to issue an Exit Sub after I refresh the ADO recordset for it to work, otherwise, it brings back all records in the table.

Thanks for your help, and I've given you the star I promised.

 

[ericbrunson]

That's nice you posted the solution back, not enough people do that. I don't understand what you're talking about with "ExitSub", ADO sucks and I don't use it.

 

[Sprowler]

Hi eric. Thanks for the reply.

I use ADO, because I've come from using MS Access, and am very new to MySQL so it seemed the quickest and easiest way to get connected. I have created a VB Subroutine to do the work, and normally an "Exit Sub" (which terminates any processing in the subroutine) isn't needed if the code runs to the last line of the routine. In this case it appears not the case. That's why I posted the info, in case others have had a simolar problem.

I agree that ADO sucks to a extent, but it "does the job" for me at the moment. If you have any tips on how to connect from VB without ADO and MyODBC, I'd be very grateful to know.

Thanks again for your help, and Merry Xmas to you.