Passing criteria in excel queries

  • Thread starter Thread starter info
  • Start date Start date
I

info

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
 
Have you tried just coding in an Excel cell, something like this to replace
your question marks...

Range("A1").value

Vaya con Dios,
Chuck, CABGx3
 
Back
Top