Access/Oracle ora-00907 error

[shaikmonster]

Hi all!

I have a Access db with some vb running sql against an oracle db... It works well at some of our sites, but I get an ora-00907 error in other places. I think the problem comes in the following line :

sql9 = "(select sum(decode(subs tr(result_type, 1,1),'C',1,0)) from prospect_contac t pc,prospect_bas e pb, base_pledge bp,segment_grou p_control sg,campus.resul t_codes rc where pb.id_number = pc.id_number And pb.segment_grou p = sg.segment_grou p and sg.result_group = rc.result_group and pb.id_number = bp.id_number (+) and pc.last_result = rc.result_code and pc.last_call_Da te between to_date('" & Format(DLookup( "Month1", "qryFY"), "mm\/dd\/yyyy") & "','mm-dd-yyyy') and to_date('" & Format(DLookup( "Month1End" , "qryFY"), "mm\/dd\/yyyy") & "','mm-dd-yyyy')) Month1Completes "

I am running out of ideas of why it works/doesn't work.... Help me!!!

[MMcCarthy]

You have problems with your quotation marks and spacings, try the following:

sql9 = "(select sum(decode(substr(result_type,1,1),'C',1,0)) " & _
    "FROM prospect_contact pc, prospect_base pb, base_pledge bp, " & _
    "segment_group_control sg, campus.result_codes rc " & _
    "WHERE pb.id_number = pc.id_number " & _
    "AND pb.segment_group = sg.segment_group " & _
    "AND sg.result_group = rc.result_group  " & _
    "AND pb.id_number = bp.id_number (+)  " & _
    "AND pc.last_result = rc.result_code  " & _
    "AND pc.last_call_Date BETWEEN to_date('" & _
    Format(DLookup("[Month1]", "qryFY"), "mm\/dd\/yyyy") & "','mm-dd-yyyy')  " & _
    "AND to_date('" & Format(DLookup("[Month1End]", "qryFY"), "mm\/dd\/yyyy") & "','mm-dd-yyyy')"
 

Make sure the DLookup statements are only returning one value and I also removed the last word from the end as I couldn't figure out what it were doing.