Date Format with Pass Through and form fields

[David]

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')

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


Any assistance you can provide is very much appreciated.

 

[MGFoster]

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')

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Like this:

Format(Forms!frm_Switchboard!txt_Start_Date, "dd-mmm-yyyy")

You can tell the Format() function the "format" of the date by using dd
(day number), mm (month number) or mmm (month 3-letter name) or mmmm
(complete month name), and yyyy (4-digit year) or yy (2-digit year).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSMC/iYechKqOuFEgEQJTJwCeIqErz4uP1Gpgi+iwc393Iapfv7sAnjvz
kWbB7Q0DsU5wx2eB6ZWkz2QS
=XhAq
-----END PGP SIGNATURE-----

 

[David]

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 & "')"