Too few parameter – Expected 2 error message 1

[UongSaki]

I’m trying to use Excel Pivot table to analyze data from a Union query. Is there a way to do it? I keep getting "Too few parameter – Expected 2 error message."


Thanks,

UongSaki

 

[PHV]

I guess that your union query has some parameter(s).
You have to define them in a PARAMETERS clause in your CrossTab Query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[UongSaki]

Can you show me how? Here's my SQL.

SELECT TblTimeBalances.Persno AS EmpID, TblTimeBalances.Date AS [Date], TblTimeBalances.[No] AS Hours, "" AS SendCostCenter, "" AS RecCostCenter, "" AS ActType, TblTimeBalances.[Wage type] AS AATypeOrWageType, TblTimeBalances.[Long text] AS AATypeOrWageTypeDescription, "" AS WBSOrOrder
FROM TblTimeBalances

UNION ALL

SELECT TblTimeCharged.Persno AS EmpID, TblTimeCharged.Date AS [Date], TblTimeCharged.Hours AS Hours, TblTimeCharged.SendCCtr AS SendCostCenter, TblTimeCharged.[Rec CCtr] AS RecCostCenter, TblTimeCharged.ActTyp AS ActType, TblTimeCharged.[A/AType] AS AATypeOrWageType, TblTimeCharged.[Att/abs type text] AS AATypeOrWageTypeDescription, IIf([TblTimeCharged]![WBS elem]<>"",[TblTimeCharged]![WBS elem],[TblTimeCharged]![Rec order]) AS WBSOrOrder
FROM TblTimeCharged
WHERE (((TblTimeCharged.[WBS elem]) Is Not Null) AND ((TblTimeCharged.Name)="Approved")) OR (((TblTimeCharged.[Rec order]) Is Not Null) AND ((TblTimeCharged.Name)="Approved"));

Thanks,

UongSaki

 

[PHV]

Does your union query works without asking for parameter value ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

I would also remove the alias names from the second query.

Leslie

 

[sha76]

I had a similar problem & found the Excel wasn't liking me using " changed them all to ' and it worked fine!

"Your rock is eroding wrong." -Dogbert

 

[UongSaki]

Thank you (PHV, lespaul, and sha76) for responding to plea for help. I’ve not had the chance to try any of your suggestions yet ~ still recovering from illness. I’ll let you know.

Again, thank you for responding

UongSaki

 

[UongSaki]

Thank you all - It's working now that I romoved the alias names.
UongSaki

 

[DreamerZ]

I'm having a similiar problem as this one, but it's more to do with a variable in Excel.

I have a simple select query in Access with no parameters. I have recorded the following code to automatically get the desired data from the Access query:

Dim strDate As String

'{ts '2004-12-02 00:00:00'}
strDate = Format(Date - 1, "yyyy-mm-dd hh:mm:ss;@")

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access 97 Database;DBQ=G:\d-mist\MisDB\data\mis.mdb;DefaultDir=G:\d-mist\MisDB\data;DriverId=25;FIL=MS Access;MaxBufferS" _
        ), Array("ize=2048;PageTimeout=5;")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT BST_PAC_Job.FK_SHIFT, BST_PAC_Job.Accounts, BST_PAC_Job.AccountsOK, BST_PAC_Job.FK_JOB_TYP, BST_PAC_Job.FK_SYSTEM, BST_PAC_Job.shiftdate" & Chr(13) & "" & Chr(10) & "FROM `G:...`.BST_PAC_Job BST_PAC_Job" _
        , _
        "" & Chr(13) & "" & Chr(10) & "WHERE (BST_PAC_Job.shiftdate={ts '[b]2004-12-02 00:00:00[/b]'}" & Chr(13) & "" & Chr(10) & "ORDER BY BST_PAC_Job.FK_SHIFT" _
        )
        .Name = "Query from MS Access 97 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

The above code work perfectly. What I want, however, is a variable date (the bold section) using the strDate variable.

I have tried:

.shiftdate={ts '" & strDate & "'}" &...

But I get a SQL Syntax error. Hovering over strDate upon debugging shows the proper date variable and format as in the bold section above with double quotes around the data ("2004-12-02 00:00:00") which I think is the problem.

So, what am I doing wrong? I'm pretty sure it's the formatting of the variable code, but I can't get it to work.

Thoughts? TIA.

DreamerZ

DreamerZ

 

[lespaul]

have you check that strDate is formatted correctly?

when you are trying to add the variable is your code as follows?

Dim strDate As String

'{ts '2004-12-02 00:00:00'}
strDate = Format(Date - 1, "yyyy-mm-dd hh:mm:ss;@")

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access 97 Database;DBQ=G:\d-mist\MisDB\data\mis.mdb;DefaultDir=G:\d-mist\MisDB\data;DriverId=25;FIL=MS Access;MaxBufferS" _
        ), Array("ize=2048;PageTimeout=5;")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT BST_PAC_Job.FK_SHIFT, BST_PAC_Job.Accounts, BST_PAC_Job.AccountsOK, BST_PAC_Job.FK_JOB_TYP, BST_PAC_Job.FK_SYSTEM, BST_PAC_Job.shiftdate" & Chr(13) & "" & Chr(10) & "FROM `G:...`.BST_PAC_Job BST_PAC_Job" _
        , _
        "" & Chr(13) & "" & Chr(10) & "WHERE (BST_PAC_Job.shiftdate=[b]{ts '" & strDate & "'}"[/b] & Chr(13) & "" & Chr(10) & "ORDER BY BST_PAC_Job.FK_SHIFT" _
        )
        .Name = "Query from MS Access 97 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

Can I ask why you are putting all those line breaks and carriage returns in the middle of your SQL statement?

Leslie

 

[DreamerZ]

The code I listed is what Excel created when I recorded the macro. The initial code with the date detailed out works perfectly.

As I mentioned, hovering the cursor over the strDate variable upon debugging the code shows it to be exactly the same formatting except with double quotes around it. I mentioned that after listing the code.


DreamerZ

 

[lespaul]

ok, if you modify your code with the bolded section, does it pass the variable correctly?

Leslie

 

[DreamerZ]

Yes. The variable shows as "2004-12-02 00:00:00"


DreamerZ

 

[DreamerZ]

Alright, well, I'm not sure why this worked and the other ways didn't, but it's fixed now.

I took the whole "WHERE..." line and put it in a string variable. I then creating put the date variable inside that new string variable and then put the string variable in the SQL code.

It works fine.

intDate = Format(Date - 1, "yyyy-mm-dd hh:mm:ss;@")
strDate = "WHERE (BST_PAC_Job.shiftdate={ts '" & intDate & "'})"

Not sure why it worked that way and not the other, but at least it works now.


DreamerZ