Passing criteria for queries in macros   

I use Excel Macros to search databases using ODBC.  I have a macro I
wrote that performs queries on 4 different databases with the same
search criteria.  Currently, the macro runs each query separately and
prompts the user 4 times to enter the same start and end date and
salesman number.  How can I save the values and pass them from the
first query to the next 3 without the user having to enter them 4
times?  This is the SQL code from the firat query.  The 3 question
marks are the prompted criteria.

SELECT CMASCP.CPCUS, CMASTR.CU_NME, CMASCP.CPDIV, CMASCP.CP_CID,
CMASCP.CPCDTE, CMASCP.CPCSMN, CMASCP.CPTYP, CMASCP.CPCDET,
CMASCP.CPSEQ
FROM S10BB89B.QS36F.CMASCP CMASCP, S10BB89B.QS36F.CMASTR CMASTR
WHERE CMASCP.CPCUS = CMASTR.CU_CUS AND CMASCP.CPDIV = CMASTR.CU_DIV
AND CMASCP.CPCO = CMASTR.CU_CO AND ((CMASCP.CPCDTE Between ? And ?)
AND (CMASCP.CPDIV=?))


This is the macro code that calls the query.


Workbooks.Add
'insert blank worksheet
       With ActiveSheet.QueryTables.Add(Connection:= _
        "FINDER;C:\Documents and Settings\gvarianides\Application
Data
\Microsoft\Queries\calldetail.dqy" _
        , Destination:=Range("A1"))
        .Name = "calldetail"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
'run call query
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With


I know I can prompt for the criteria in my macro and save them in 3
different variable names.  What I don't know how to do is pass these
variables to the SQL code in the queries.
Thanks

date: Wed, 6 Feb 2008 06:16:36 -0800 (PST)   author:   unknown

Re: Passing criteria for queries in macros   

Hi

You've posted this to the Word newsgroup at 
microsoft.public.word.vba.beginners.

I suggest you re-post to an Excel newsgroup.

Hope this helps.

Shauna Kelly.  Microsoft MVP.
http://www.shaunakelly.com/word



 wrote in message 
news:afd0d33d-2f23-426a-bb0c-3071b6511348@s8g2000prg.googlegroups.com...
>I use Excel Macros to search databases using ODBC.  I have a macro I
> wrote that performs queries on 4 different databases with the same
> search criteria.  Currently, the macro runs each query separately and
> prompts the user 4 times to enter the same start and end date and
> salesman number.  How can I save the values and pass them from the
> first query to the next 3 without the user having to enter them 4
> times?  This is the SQL code from the firat query.  The 3 question
> marks are the prompted criteria.
>
> SELECT CMASCP.CPCUS, CMASTR.CU_NME, CMASCP.CPDIV, CMASCP.CP_CID,
> CMASCP.CPCDTE, CMASCP.CPCSMN, CMASCP.CPTYP, CMASCP.CPCDET,
> CMASCP.CPSEQ
> FROM S10BB89B.QS36F.CMASCP CMASCP, S10BB89B.QS36F.CMASTR CMASTR
> WHERE CMASCP.CPCUS = CMASTR.CU_CUS AND CMASCP.CPDIV = CMASTR.CU_DIV
> AND CMASCP.CPCO = CMASTR.CU_CO AND ((CMASCP.CPCDTE Between ? And ?)
> AND (CMASCP.CPDIV=?))
>
>
> This is the macro code that calls the query.
>
>
> Workbooks.Add
> 'insert blank worksheet
>       With ActiveSheet.QueryTables.Add(Connection:= _
>        "FINDER;C:\Documents and Settings\gvarianides\Application
> Data
> \Microsoft\Queries\calldetail.dqy" _
>        , Destination:=Range("A1"))
>        .Name = "calldetail"
>        .FieldNames = True
>        .RowNumbers = False
>        .FillAdjacentFormulas = False
>        .PreserveFormatting = True
>        .RefreshOnFileOpen = False
>        .BackgroundQuery = True
>        .RefreshStyle = xlInsertDeleteCells
> 'run call query
>        .SavePassword = False
>        .SaveData = True
>        .AdjustColumnWidth = True
>        .RefreshPeriod = 0
>        .PreserveColumnInfo = True
>        .Refresh BackgroundQuery:=False
>    End With
>
>
> I know I can prompt for the criteria in my macro and save them in 3
> different variable names.  What I don't know how to do is pass these
> variables to the SQL code in the queries.
> Thanks
>
>

date: Thu, 7 Feb 2008 01:25:40 +1100   author:   Shauna Kelly