Microsoft Query Parameter Problem

[StarhawkGamer]

I'm attempting to build a parameter query in MS Query to pull data from an Access 2007 database into an Excel 2007 spreadsheet. I have attempted every permutation of the between statement that I can figure out and either I get a "Too Few Parameters. 2 Expected." error or I get all of the data without the criteria.
*Condition Field*

Field: TRAN_DTTM
Value: Between [Start Date] and [End Date]

*SQL CODE*

SELECT Adjustments.TRAN_DTTM, Adjustments.`Advocate ID`, Sum(Adjustments.TRAN_AMT) AS 'Sum of TRAN_AMT'
FROM `Y:\TP CL Database 2.0.accdb`.Adjustments Adjustments
GROUP BY Adjustments.TRAN_DTTM, Adjustments.`Advocate ID`
HAVING (Adjustments.TRAN_DTTM Between [Start Date] And [End Date])

 

[SkipVought]

Hi,

Turn on your macro recorder.

Edit the Query, proceeding into the Query Editor and immediately returning data to Excel (File > Return data to Microsoft Excel)

Turn off your macro recorder. Observe your code, part of which will be your SQL as posted above.

Yoju have not stated how you intend to provide the parameter values. Let's assume that they have been place in two cells on SheetA, A1 & B1...

.CommandText = "SELECT Adjustments.TRAN_DTTM, Adjustments.`Advocate ID`, Sum(Adjustments.TRAN_AMT) AS 'Sum of TRAN_AMT'
FROM `Y:\TP CL Database 2.0.accdb`.Adjustments Adjustments
GROUP BY Adjustments.TRAN_DTTM, Adjustments.`Advocate ID`
HAVING (Adjustments.TRAN_DTTM Between [b]" & Sheets("SheetA").[A1] & "[/b] And [b]" & Sheets("SheetA").[B1] & "[/b])"

You can delete the Array( and trailing )

I only posted the snippet that is immediately applicable with the SQL in place.

I would also change one other thing...

[s]
With Selection.QueryTable[/s]
to
With Sheets("SheetContaingQueryTable").QueryTables(1)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

And of course you will have to run this procedure one some sort of event, once the correct values have been placed in A1 & B1.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Took a second look at your SQL an noticed that you have DATES.

Here's what I would do, notice the # delimiters for string to date conversion,

    Dim sSQL As String
    
    sSQL = "SELECT"
    sSQL = sSQL & "  Adjustments.TRAN_DTTM"
    sSQL = sSQL & ", Adjustments.`Advocate ID`"
    sSQL = sSQL & ", Sum(Adjustments.TRAN_AMT) AS 'Sum of TRAN_AMT'"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM `Y:\TP CL Database 2.0.accdb`.Adjustments Adjustments"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "Group BY"
    sSQL = sSQL & "  Adjustments.TRAN_DTTM"
    sSQL = sSQL & ", Adjustments.`Advocate ID`"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "HAVING (Adjustments.TRAN_DTTM Between #" & Sheets("SheetA").[A1] & "# And #" & Sheets("SheetA").[B1] & "#)"

    Debug.Print sSQL

    With Sheets("SheetA).QueryTables(1)
       '...
       .CommandText = sSQL
       '...
    End With

Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[PHV]

Furthermore, in this context a WHERE clause is more efficient than an HAVING clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Ditto on that.

    Dim sSQL As String
    
    sSQL = "SELECT"
    sSQL = sSQL & "  Adjustments.TRAN_DTTM"
    sSQL = sSQL & ", Adjustments.`Advocate ID`"
    sSQL = sSQL & ", Sum(Adjustments.TRAN_AMT) AS 'Sum of TRAN_AMT'"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM `Y:\TP CL Database 2.0.accdb`.Adjustments Adjustments"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE (Adjustments.TRAN_DTTM Between #" & Sheets("SheetA").[A1] & "# And #" & Sheets("SheetA").[B1] & "#)"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "Group BY"
    sSQL = sSQL & "  Adjustments.TRAN_DTTM"
    sSQL = sSQL & ", Adjustments.`Advocate ID`"

    Debug.Print sSQL

    With Sheets("SheetA).QueryTables(1)
       '...
       .CommandText = sSQL
       '...
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]