I have successfully used the following SQL where to retreive dates from an
Oracle database:

WHERE (ALL_MARGINAL_PRICES.LOC_ID=4) AND (ALL_MARGINAL_PRICES.MP_DATE = {ts
'" & st & "'}) AND (ALL_MARGINAL_PRICES.MP_DATE <= {ts '" & en & "'})"

be careful of the times.



"Tim Williams" wrote:

this is not going to work - as it will only get records entered at midnight

where PROPERTY_DATE= to_date('2005-06-30', 'yyyy-mm-dd')

try this instead

where (PROPERTY_DATE to_date('2005-06-30', 'yyyy-mm-dd') and
PROPERTY_DATE < to_date('2005-07-01', 'yyyy-mm-dd')

or even

where (PROPERTY_DATE to_date('2005-06-30', 'yyyy-mm-dd') and
PROPERTY_DATE < (to_date('2005-06-30', 'yyyy-mm-dd')+1)

Get one of your Oracle gurus to run your SQL in SQLPLus and get the real
error.

Tim.
--
Tim Williams
Palo Alto, CA


"BAC" wrote in message
...
You are correct the TimeStamp portion of the date. However, my "ORACLE
guru's" tell me the TO_DATE function will resolve that issue.

I found the "missing space" (it was hiding under the phone) and put it
back
in but no help there.

I know nothing (or a little less) about SQLPLUS. I can run this query in
Access, as long as I run it as a Pass-Through query with the contracts
list
in a separate table and not as an IN() list. The SQL from Access submits
the
date as #06/30/05# = which I have tried but ended up with the same,
rather
non-specific, error "SQL Syntax Error"

Thanx...



"Tim Williams" wrote:

If your PROPERTY_DATE field includes a time component then your current
query will only return records where the timestamp is 00:00 (since by
default the time component will get assigned as 00:00), so you might
think
about modifying that part if that's the case.

You seem to be missing a space he
UNEARNED_RESIDUALFROM


Can you execute a "select *" on your view with no problems?
Have you tried running the sample query directly against the database in
SQLPlus ?


--
Tim Williams
Palo Alto, CA


"BAC" wrote in message
...
pdate is the date selected by the user from a drop down box listing
"AS
OF"
date for the report(s). The date is stored in date formatted cell on
sheets("MISC").cell (17,4)


"PROPERTY_DATE is date value in ORACLE view in Datawarehouse. A
"straight
read" of this value includes TimeStamp
Generated SQL:

debug.Print sql_str
SELECT LEASE_TYPE, CONTRACT_NBR, LL_NET_CBR, LL_REMAINING_PRETAX_INC,
RESIDUAL_NET, CONTRACT_BALANCE, UNEARNED_FINANCE,
UNEARNED_RESIDUALFROM
IL_REPORT_CONTRACTS_FINAL_CURRENT WHERE (PROPERTY_DATE=
to_date('2005-06-30',
'yyyy-mm-dd')) AND (CONTRACT_NBR IN


('200-0000001-001','200-0000002-001','200-0000002-003','200-0000002-004','20

0-0000004-001','200-0000005-001','200-0000005-002','200-0000005-003','200-00

00006-001','200-0000007-002','200-0000007-003','200-0000007-004','200-000000

7-005','200-0000008-002','200-0000008-003','200-0000008-004','200-0000009-00

1','200-0000009-004','200-0000009-005','200-0000009-006','200-0000010-002','

200-0000011-001','200-0000011-002','200-0000011-003','200-0000011-004','200-

0000012-001','200-0000013-001','200-0000014-001','200-0000016-001','200-0000

017-001','200-0000018-001','200-0000020-001','200-0000021-001','200-0000022-

001','200-0000022-002','200-0000023-001','200-0000023-002','200-0000024-001'

,'200-0000031-001','200-0000035-001','200-0000036-001','200-0000037-001','20
1-000000


1-001','201-0000001-002','202-0000001-001','202-0000002-002','203-0000001-00

1','203-0000001-002','204-0000001-001','204-0000001-002','204-0000001-003','

204-0000002-001','204-0000003-001','204-0000003-002','204-0000003-003','204-

0000004-001','204-0000004-002','204-0000005-001','204-0000006-001','204-0000

006-002','204-0000007-001','204-0000007-002','204-0000007-003','205-0000001-

001','206-2001005-001','206-2001006-001','206-2001007-001','207-0990091-001'

,'207-0990091-003','207-0990091-004','215-2001033-001','215-2001033-002','21

5-2001033-003','215-2001033-004','215-2001035-001','216-2001032-001','216-20

01034-001','216-2001034-002','217-0000004-001','217-0000008-002','217-000000
9-001'))ORDER
BY CONTRACT_NBR;

THanx..



"Tim Williams" wrote:

Can you paste an example of the generated SQL ?

What is the value of "pdate", and what is the datatype of
PROPERTY_DATE
?

Tim.

--
Tim Williams
Palo Alto, CA


"BAC" wrote in message
...
We recently converted one of our datawarehouses to ORACLE.

I am now having difficulty converting several Excel Macros that
use
VBA
generated SQL statements to execute. e.g.:

Sub gogetem()
'Fields appropriately dimmed
'Loads Warehouse Data to the DownLoad sheet

curwrkbk = Format(Sheets("MISC").Cells(18, 2), "mmddyy") &
"_this_File.xls"
curwrkbk = "\\crpAtlFnp03\Accounting\" & curwrkbk

K_List = contract_list 'Function builds list of contracts to have
data
returned

'Retrieve data from data warehouse
'Get the detail data
'Initialize period date variables
'User selects report "AS of Date" from drop down and selection
stored
in:

pdate = Sheets("Misc").Cells(17, 4).Value


Sql_Str = "SELECT LEASE_TYPE, CONTRACT_NBR, LL_NET_CBR,
LL_REMAINING_PRETAX_INC, " & _
"RESIDUAL_NET, CONTRACT_BALANCE, UNEARNED_FINANCE,
UNEARNED_RESIDUAL
" & _
"FROM IL_REPORT_CONTRACTS_FINAL_CURRENT " & _
"WHERE (PROPERTY_DATE= to_date('" & pdate & "',
'yyyy-mm-dd'))AND
"
& _
"(CONTRACT_NBR IN (" & K_List & "))" & _
"ORDER BY CONTRACT_NBR;"

Sheets("Download").Select
Range("A2").Select
With Selection.QueryTable
.Connection = _
"ODBC;DRIVER={Microsoft ODBC for Oracle};UID=" & IAM &
";PWD=" &
MyPWD & ";SERVER=CDMP.com;"
.Sql = Sql_Str

.Refresh BackgroundQuery:=False

End With

ActiveWorkbook.SaveAs Filename:=curwrkbk

End Sub



The debugger kicks in at the " .Refresh BackgroundQuery:=False"

With a SQL Syntax error.

pdate format must be "yyyy-mm-dd", and my understanding is that
the
TO_DATE
function is required to override ORACLE default format of :date
Timestamp"

K_List format is:
('123-0000007-000','123-0000008-001',...)

I run the query in ACCESS without difficulty when K_List is in a
separate
table.
Access bombs with "Exceeding 1,024 character limit for query
grid"
error
when I keep IN(list) format in grid layout

When I try to run as an SQL Pass-through I get an ODBC error
indicating
"this operator must be followed by Any or ALL", but nothing to
indicate
what
"this operator" may be.

Any help would be greatly appreciated as we are soon to convert
all
out
data
warehouses to ORACLE and I have tons of similar queries that will
need
to
be
revised as well...

TIA

BAC