Problems with Date function in ADO recordset

[dhaveedh]

Hi,

I have an ADODB connection to an Oracle table from VBA code and I want to retrive matching recordsets from the tables into another.


Here is my code;

Set db = CurrentDb
Set rs1 = db.OpenRecordset("ImageA")


Set con = New ADODB.Connection

Set rs = New ADODB.Recordset

sConnection = "Provider=MSDAORA;Data Source=Polite;User ID=test;Password=testMe;"

con.Open sConnection

Do Until rs1.EOF

sSQL = SELECT a.CG_DATE as TxnDate, a.AMT as TxnAmt,a.REF
FROM CASH_PAY as a, CM_DATA as b, CG_ACCOUNT as c
WHERE a._REF = b._REF AND c.REF = b._REF
AND c.B_SENT_IND = 0
AND c.LIVE = 1
AND a.CG_DATE >= Date - 70 AND a.CG_DATE <= Date

rs.Open sSQL, con

If rs.RecordCount <> 0 Then
rs1.Edit
rs1.Fields!Txn = rs.Fields("TxnAmt")
rs1.Fields!TDate= rs.Fields("TxnDate")
rs1.Update

Do Until rs.EOF
UpdatePayment rs1.Fields("Ref"), rs.Fields("TxnDate"), rs.Fields("TxnAmt")
rs.MoveNext
Loop

End If
rs.Close
rs1.MoveNext
Loop

I encounter a problem at this point

AND a.CG_DATE >= Date - 70 AND a.CG_DATE <= Date

Where it tells me "Not a Valid month" I have tried using the oracle function To_Date() to convert the date but get no success.

I know the problem is from there because each time I remove it, the Sub runs successfully.

Thanx for any help



KISS - Keep It Simple Sugar!

 

[SkipVought]

Hi,

Is a.CG_DATE a Date type?

Is Date a valid function returning today's date in Oracle or is there another function that returns today's date?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[dhaveedh]

Sysdate returns today's date in oracle. I have tried using that but it does not work.

That line actually reads

AND CG_DATE >= '" & Date-70 & "' AND CG_DATE <= '" & Date & "'

I know Oracle only accepts date in the format 'Date' That's why I have the dates in ''

I also know that Oracle Standard format is dd-Mon-YYYY.

Any ideas?

Thanks


KISS - Keep It Simple Sugar!

 

[SkipVought]

You did not answer my first queston...

"Is a.CG_DATE a Date type?"

Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second, so if your field, CG_DATE is not a REAL ORACLE DATE, your compare will fail.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[dhaveedh]

Yes it is a date type.

KISS - Keep It Simple Sugar!

 

[SkipVought]

I just notices the MISSING QUOTE at the end of your statement...

AND CG_DATE >= '" & Date-70 & "' AND CG_DATE <= '" & Date & "' "

???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[dhaveedh]

Thanks for your responses. I already had that in my query but i have found out the problem to be the Date Format in Oracle.

The satandard format is dd-Mon-YYYY so I changed my syntax to

CG_DATE >= '" & Format(Date-70, "dd-MMM-YYYY") & "' AND CG_DATE <= '" & Format(Date, "dd-MMM-YYYY") & "'

and Bingo!

Thanks anyway


KISS - Keep It Simple Sugar!