Access 2003 – Setting Form’s Recordsource

50 pts.
Tags:
Access
Access 2003
Access 2003 forms
Hi I have a SQL query which I know works.  I tested it by creating a pass-thru query in Access and running the query below:
SELECT PcSd.PRICE_SCHEDULE_ID, CASE WHEN PcSd.PRICE_LEVEL_CD IN ('BGA', 'BNG') THEN 'Brand' ELSE 'Generic' End As Price_Level, PcSd.PRICE_TYPE_CD, PcSd.DISPENSE_FEE_AMT, PcSd.MARKUP_PCT*-100 AS MARKUP_PT, PcSd.SOURCE_TYPE_CD FROM GAHPP0BD.RPT_PRICE_SCHEDULE AS PcSd Group BY PcSd.PRICE_SCHEDULE_ID, CASE WHEN PcSd.PRICE_LEVEL_CD IN ('BGA', 'BNG') THEN 'Brand' ELSE 'Generic' End, PcSd.PRICE_TYPE_CD, PcSd.DISPENSE_FEE_AMT, PcSd.MARKUP_PCT*-100, PcSd.SOURCE_TYPE_CD ORDER BY PcSd.PRICE_SCHEDULE_ID; 
When i try to run this query using VBA, I'm not able to.  Basically what I would like to do is to set my form's recordsource to this query.  I'm not sure what I'm doing wrong.  Below is my code:
Public Sub diffSearch()
    'Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    Dim strWhere As String  'The criteria string.
    Dim strPriceTypeCd As String
    Dim rs As adodb.Recordset
    Dim strQt As String
    strQt = """"
    
    Set rs = New adodb.Recordset
    
    ConnectToDB   
    
    strSQL = "SELECT PcSd.PRICE_SCHEDULE_ID, CASE WHEN PcSd.PRICE_LEVEL_CD IN ('BGA', 'BNG') THEN 'Brand' ELSE 'Generic' End As Price_Level, PcSd.PRICE_TYPE_CD, PcSd.DISPENSE_FEE_AMT, PcSd.MARKUP_PCT*-100 AS MARKUP_PT, PcSd.SOURCE_TYPE_CD FROM GAHPP0BD.RPT_PRICE_SCHEDULE AS PcSd Group BY PcSd.PRICE_SCHEDULE_ID, CASE WHEN PcSd.PRICE_LEVEL_CD IN ('BGA', 'BNG') THEN 'Brand' ELSE 'Generic' End, PcSd.PRICE_TYPE_CD, PcSd.DISPENSE_FEE_AMT, PcSd.MARKUP_PCT*-100, PcSd.SOURCE_TYPE_CD ORDER BY PcSd.PRICE_SCHEDULE_ID; "
    
    rs.Open strSQL, cn
    Form_Form1.RecordSource = strSQL
    
    
    rs.Close
    cn.Close
           
End Sub

Public Sub ConnectToDB()
    Dim strConnect As String
    Set cn = New adodb.Connection
    strConnect = "Driver={IBM DB2 ODBC DRIVER};ODBC;DSN=DB8G_R;UID=myID;PWD=myPW;DBALIAS=DB8G_R;"
    
    With cn
        .ConnectionString = strConnect
        .Open
    End With
End Sub
After running the code, I get runtime error 3075, telling me that my SQL is incorrect.  
Run-time error '3075: 
Syntax error(missing operator) in query expresseion 'Case WHEN PcSd.PRICE_LEVEL_CD IN ('BGA', 'BNG') Then 'Brand' Else 'Generic' End'.
I also tried to substute double quotes where the single quotes are being used, but same error...Not sure why it's stating this when I tested the SQL and it's working.  Any ideas??
ASKED: April 7, 2010  8:18 PM
UPDATED: April 8, 2010  1:55 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

AFAIK, the Access Jet engine doesn’t support CASE expressions. It worked in the Pass-through query because when using that type of query the command is sent directly to the external database (without being parsed by the Jet engine).

Access <a href=”http://www.techonthenet.com/access/functions/advanced/iif.php”>IIF function</a> provides similar functionality.

-CarlosDL

————–

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following