dynamic oracle sql syntax for date greater than today

I am using frontend traditional visual basic 6 and backend oracle database
I want write an SQL for oracle to return  a recordset with records greater than a defined date
The following static date type is working well
rspaperlist.Open "select ex.exam_code,ex.exam_name, ex.exam_date,ep.paper_code,ep.bank_code,ep.spec_code from qb_m_exams ex,qb_m_exampapers ep where ex.exam_code=ep.exam_code and ep.spec_code=2 and ep.bank_code=1 and ex.exam_date >= DATE '2010-04-16'", cnora, 1, 1

To make the SQL more dynamic, I created a code to return a date what oracle expects in its SQL

Dim tempday, tempmon
tempday = Day(Date)
If Len(tempday) = 1 Then
tempday = "0" & tempday
End If
tempmon = Month(Date)
If Len(tempmon) = 1 Then
tempmon = "0" & tempmon
End If
oradttoday = Year(Date) & "-" & tempmon & "-" & tempday
Me.txttoday = oradttoday

In the form, a textbox named txttoday contains current date as required by oracle.  
The following code is not working
rspaperlist.Open "select ex.exam_code,ex.exam_name, ex.exam_date,ep.paper_code,ep.bank_code,ep.spec_code from qb_m_exams ex,qb_m_exampapers ep where ex.exam_code=ep.exam_code and ep.spec_code=2 and ep.bank_code=1 and ex.exam_date >= DATE & " '" & me.txttoday &"'"", cnora, 1, 1

How should the SQL syntax be modified
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HugoHiaslCommented:
If you now the format in which the text box gives back the date then can use to_date() of oracle

rspaperlist.Open "select ex.exam_code,ex.exam_name, ex.exam_date,ep.paper_code,ep.bank_code,ep.spec_code from qb_m_exams ex,qb_m_exampapers ep where ex.exam_code=ep.exam_code and ep.spec_code=2 and ep.bank_code=1 and ex.exam_date >= TO_DATE('"& me.txttoday &"','dd-mm-yyyy'')", cnora, 1, 1

This would be valid for the format dd-mm-yyyy and needs to be adapted.
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
I recoded to give textbox date output in the format you suggested i.e. dd-mm-yyyy.  For example, today the output is 17-04-2010.  I just copied your SQL and pasted.  
It give me an error with a message
Quoted string not properly terminated. Can it be rectified
Naveen KumarProduction Manager / Application Support ManagerCommented:
can you try the below one. I have removed one extra quote which was appearing there.

rspaperlist.Open "select ex.exam_code,ex.exam_name, ex.exam_date,ep.paper_code,ep.bank_code,ep.spec_code from qb_m_exams ex,qb_m_exampapers ep where ex.exam_code=ep.exam_code and ep.spec_code=2 and ep.bank_code=1 and ex.exam_date >= TO_DATE('" & me.txttoday & "','dd-mm-yyyy')", cnora, 1, 1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
Thanks for the prompt reply
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.