Access Oracle SQL

[Guest]

I have an app to access Oracle database from Access mdb file.

It looks that Oracle has it date format.

I have folowing SQL code:

"SELECT * FROM MyTable
WHERE MyField = to_date('07/18/2005', 'mm/dd/yyy')"

It works, I wnated to use date() instaed of hardcode date.

I tried the following:

"SELECT * FROM MyTable
WHERE MyField = to_date(format(now(), "mm/dd/yyy"), 'mm/dd/yyy')"

It does not work.

Are there any way to convert a Oracle date format from date() function in
Access?

Any informaiton is great appreciated,

 

[John Vinson]

"SELECT * FROM MyTable
WHERE MyField = to_date('07/18/2005', 'mm/dd/yyy')"

It works, I wnated to use date() instaed of hardcode date.

I tried the following:

"SELECT * FROM MyTable
WHERE MyField = to_date(format(now(), "mm/dd/yyy"), 'mm/dd/yyy')"

It does not work.

Are there any way to convert a Oracle date format from date() function in
Access?

I think you want to use yyyy rather than yyy in your format. Try

SELECT * From MyTable
WHERE MyField = To_Date(Format(Date(), "mm/dd/yyyy"))

or - possibly -

SELECT * From MyTable
WHERE MyField = "'" & Date() & "'"

John W. Vinson[MVP]

 

[Guest]

I got following errors when I tried your suggest.

SELECT * From MyTable
WHERE MyField = To_Date(Format(Date(), "mm/dd/yyyy"))

ORA-00936 ERROR

SELECT * From MyTable
WHERE MyField = "'" & Date() & "'"

ORA-00933 ERROR

 

[John Vinson]

I got following errors when I tried your suggest.

SELECT * From MyTable
WHERE MyField = To_Date(Format(Date(), "mm/dd/yyyy"))

ORA-00936 ERROR

SELECT * From MyTable
WHERE MyField = "'" & Date() & "'"

ORA-00933 ERROR

I'd suggest you repost, Souris - it's been years since I've used an
Access-Oracle link and I'm not coming up with any good ideas. I don't
have Oracle installed so I cannot help with the Oracle error numbers.

John W. Vinson[MVP]

 

[Guest]

Thanks for helping,

I'd suggest you repost, Souris - it's been years since I've used an
Access-Oracle link and I'm not coming up with any good ideas. I don't
have Oracle installed so I cannot help with the Oracle error numbers.

John W. Vinson[MVP]