Problem in mySQL query with VB 6.0

Hi Experts,

Can anyone figure out whats wrong with my query below? Im using mySQL database in vb 6.0

SELECT schedule_info1.*, Format([SCH_startdate],"mm/dd/yyyy") AS Expr1
FROM schedule_info1
WHERE (((Format([SCH_startdate],"mm/dd/yyyy"))=#08/12/2004#));

The compiler is telling that there is an error in SQL syntax.

Thanks in advance
LVL 3
PhoenixricAsked:
Who is Participating?
 
GhostModConnect With a Mentor Commented:
PAQed with points refunded (300)

GhostMod
Community Support Moderator
0
 
JR2003Commented:
I'm not at all familiar with mySQL I've only used Oracle and Access but I'd try the following:

Do you need that semi-colon at the end of the SQL?
Does the Format statement work with mySQL as it's also a VB function?
Instead of
WHERE (((Format([SCH_startdate],"mm/dd/yyyy"))=#08/12/2004#));
shouldnt' it read
WHERE Format([SCH_startdate],"mm/dd/yyyy") = "08/12/2004"
or
WHERE SCH_startdate = "#08/12/2004#"
?
0
 
PhoenixricAuthor Commented:
Hi,

Thanks for the quick reply, Im also beginning to learn mySQL and its really a hard time for us using ACESS , Oracle finding this stuff with big difference, its slightly differs to SQL Server also. . :-(

Researching for the solution, I.ve found this page :
http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

Realizing that if I'd stick with my code above, I will never ever find the solution for its quite very far.

I ended up with this query and its working fine now and be able to combine another criteria for time too.
cause the [SCH_startdate] field  format is mm/dd/yyyy hh:mm:ss AM/PM

 cSQL = "SELECT schedule_info.* From schedule_info " & _
             "WHERE DATE_FORMAT(SCH_startdate,'%m/%d/%Y') ='" & Date & "' " & _
             "AND DATE_FORMAT(SCH_startdate,'%r') ='" & Time & "' "

Thanks a lot

PhoenixRic ;-)
0
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.

All Courses

From novice to tech pro — start learning today.