I am encountering an error message on my select statement below.

mySQL = "SELECT TOP 78 FROM dbo.Work_History_1 WHERE Member_id = " & rsMember_Custom!Member_ID & " AND Pay_Date <= " & txtEndDate.Text & " ORDER BY Work_01 DESC,Pay_Date;"

The error message I am getting is

[Microsoft][ODBC driver for Oracle][ORA-00923; FROM keyword not found where expected.

What am I missing in my select statement

Thanks

The fields are missing.

SELECT TOP 78 ?????? FROM etc....

unless * it's not mandatory in oracle..

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Hi gbianchi,

After researching some more. I found out that in Oracle, you do not use "TOP", instead use "ROWNUM".

Here is the select statement:

mySQL = "SELECT * FROM dbo.Work_History_1 WHERE Member_id = " & rsMember_Custom!Member_ID & " AND Pay_Date <= '" & txtEndDate.Text & "' AND ROWNUM <= 78 ORDER BY Work_01 DESC,Pay_Date"

After running it. I found out that there is a date converssion error when comparing two dates.

How do I compare two dates in a Select statement?

Thanks

I think that in oracle Dates should be between ''. Anyway, conversion and functions should be better.

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

How does one code the select statement with the proper syntax for date comparrison.

If function or converssion is better to handle the select statement, then how do I code it?

Thanks

I kept playing with the code and I was able to make it work. I had to convert txtEndDate.Text.

txtEndDate = Format(txtEnddate, "dd-mmm-yyyy")

Oracle has a conversion function for converting strings to dates.
It takes at least two argument, the string, and a string of characters showing where in the 1st argument the month part is, the year part, etc.

Though your solution clearly is working for you, it is weak (meaning it could easily be broken, and is not as explicit as it could be).

If you use Oracles conversion function, and you pass an impossible date, it will generate an error (which is the behavior you want).

I understand that there is a better way to handle the conversion but I kept searching for it and I am not having any luck.

I am aware that it can easily be broken which is why I had the user click on a date in the calendar and not let the user key in the date into the text box.

If you have any suggestions on how to code the conversion I will appreciate it. So far I have seen the TO-DATE.

Thanks