Date Format with Pass Through and form fields



Hello All,
I am using a bit of code in VBA to edit an existing Pass Through Query. I
am using the dates the user selected on a form and passing it to the Pass
Through Query SQL.
Problem is the Pass Through Query is evidently needing the date in a very
specific format, to_date('01-SEP-2008')

The current code below results with to_date('9/1/2008'). What changes can I
make to produce the SQL in the right format?

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "Select * from table_example tc where tc.creation_time >=
to_date('" & CDate(Forms!frm_Switchboard!txt_Start_Date) & "')"

This produces SQL ending
where tc.creation_time >= to_date('9/1/2008')

where tc.creation_time >= to_date('01-SEP-2008')

Any assistance you can provide is very much appreciated.


No response needed.
I discovered this is an oracle globalization setting that I can not change.
I parsed the pieces of the date into 3 string variables and put the
variables in the command to create the SQL.

worked ok.

"where tc.creation_time >= to_date('" & mydate_day_start & "-" &
mydate_month_start & "-" & mydate_year_start & "')"

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question