INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to correctly quote in a Where clause?

How to correctly quote in a Where clause?

(OP)
Hi,
My question is similar to the post: Filtering in VB report.
I tested a Excel macro that tries to run a MS Query to imort data from MS Access. It worked fine until I wanted to put a parameter in the Sub. The error that I have is 'Runtime error 13: type mismatch'. When debugging, it highlights ".CommandText...)" part of the code. It must be a quoting problem.
Thanks in advance!

Sub rawquery(mkt As String)
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
  "ODBC;DSN=MS Access Database;DBQ=C:\jqz\cadi\aetna\aetna_state_May08.mdb;DefaultDir=C:\jqz\cadi\aetna;DriverId=25;FIL=MS Acce" _
  ), Array("ss;MaxBufferSize=4096;PageTimeout=5;")), Destination:=Range("A1"))
  .CommandText = Array( _
 "SELECT rebt_period, state, formularydesign, PLAN_NAME, mkt, product, mail_retail_cd, med_ind, " & _
 "mb_flag, rx_count, total_prod_units, wac_sales, rebates, mkt_rx, mkt_total_prod_units, mkt_shr, unit_shr " & _
 "FROM `C:\jqz\cadi\aetna\aetna_state_May08`.aetna_state " & _
 "where aetna_state.mkt=" & mkt & """")
        .Name = "Query from MS Access Database"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Sub runit()
rawquery "ARB"
End Sub

RE: How to correctly quote in a Where clause?



Hi,

CODE

     "where aetna_state.mkt='" & mkt & "'")
Is there a reason that you are adding the quertable to you sheet via code?

As a matter of course, I'll manually add a QT, and THEN use code to change the CommandText and/or Connection parameter(s) and just Refresh.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: How to correctly quote in a Where clause?







As a matter of course, I'll manually add a QT, and THEN use code to change the CommandText and/or Connection parameter(s) and just Refresh, like this...

CODE

Sub rawquery(mkt As String)
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    
    sPath = "C:\jqz\cadi\aetna"
    
    sDB = "aetna_state_May08"
    
    sConn = "ODBC;DSN=MS Access Database;" & _
            "DBQ=" & sPath & "\" & sDB & ".mdb;" & _
            "DefaultDir=" & sPath & ";" & _
            "DriverId=25;FIL=MS Access;MaxBufferSize=4096;PageTimeout=5;"
    
    sSQL = "SELECT rebt_period, state, formularydesign, PLAN_NAME, mkt, product" & _
            ", mail_retail_cd, med_ind, mb_flag, rx_count, total_prod_units, wac_sales" & _
            ", rebates, mkt_rx, mkt_total_prod_units, mkt_shr, unit_shr " & _
            "FROM `" & sPath & "\" & sDB & "`.aetna_state " & _
            "where aetna_state.mkt='" & mkt & "'"
    
    With ActiveSheet.QueryTables
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub
It also wonders me why you have a database named like DATA?  Seems like there might be Jan08, Feb08, Mar08 etc database.  Yes?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: How to correctly quote in a Where clause?

(OP)
Skip,
Thanks for the quick return and I like your code better.
But after I cut & paste your code and run it. I got run-time error '438' (object doesn't support this property or method). Debug shows the problem is with 'sConn'.

I guess I have to reset the Reference Library. BTW, which library is the right one, ADO or DAO or both?

This is only a test and there is no need to give a name like 'May08' because the report can be re-freshable based on the same database, not monthly data.

I was planning to put the data (from a query table) on a Worksheet and then use Advanced filter and graphs to show the performance of some markets and other economic parameters. It will have about 160 columns.

Thanks again.

RE: How to correctly quote in a Where clause?




I am so sorry...

CODE

    With ActiveSheet.QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: How to correctly quote in a Where clause?

(OP)
It worked like a charm.
Thanks a lot! Skip, you're the man.
John Z.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close