Wsrtjtyk

I have a report that uses the following SQL query:

SELECT AccountPerformanceAllHistory.AccountNumber, 

AccountMaster.AccountName AS Name, AccountCurrentModel.Model,    

AccountPerformanceAllHistory.MarketValue, 

AccountPerformanceAllHistory.Cash, ModelDetailAllHistory.Risk, 

AccountPerformanceAllHistory.QTD, AccountPerformanceAllHistory.YTD, 

AccountPerformanceAllHistory.[1Yr], AccountPerformanceAllHistory.[3Yr], 

AccountMaster.AccountAdvisor AS Advisor, 

AccountPerformanceAllHistory.PerformanceDate, 

AccountPerformanceAllHistory.Ticker

FROM ModelDetailAllHistory INNER JOIN ((AccountMaster INNER JOIN 

AccountPerformanceAllHistory ON AccountMaster.[AccountNumber] = 

AccountPerformanceAllHistory.[AccountNumber]) INNER JOIN 

AccountCurrentModel ON AccountMaster.[AccountNumber] = AccountCurrentModel. 

[AccountNumber]) ON ModelDetailAllHistory.[ModelName] = 

AccountCurrentModel.Model

GROUP BY AccountPerformanceAllHistory.AccountNumber, 

AccountMaster.AccountName, AccountCurrentModel.Model, 

AccountPerformanceAllHistory.MarketValue, 

AccountPerformanceAllHistory.Cash, ModelDetailAllHistory.Risk, 

AccountPerformanceAllHistory.QTD, AccountPerformanceAllHistory.YTD, 

AccountPerformanceAllHistory.[1Yr], AccountPerformanceAllHistory.[3Yr], 

AccountMaster.AccountAdvisor, AccountPerformanceAllHistory.PerformanceDate, 

AccountPerformanceAllHistory.Ticker

ORDER BY ModelDetailAllHistory.Risk, AccountPerformanceAllHistory.[1Yr] 

DESC;

The report is run from a button-click:

Private Sub Command3_Click()



Dim StrWhichMonth As String

Dim StrWhichClient As String

Dim CYear, CMonth As Variant

Dim StrSearch As String



StrWhichMonth = InputBox("Enter YYMM you want to report:")

CYear = "20" & Mid(StrWhichMonth, 1, 2)

CMonth = Mid(StrWhichMonth, 3, 2)

StrWhichMonth = DateSerial(CYear, CMonth + 1, 0)



StrWhichClient = InputBox("Enter Client name ('*' for all):")



StrSearch = "AccountPerformanceAllHistory.PerformanceDate = #" & StrWhichMonth _

    & "# AND AccountPerformanceAllHistory.Ticker = " & Chr(34) & "1" & Chr(34) & "" _

    & " AND AccountMaster.AccountName Like " & Chr(34) & StrWhichClient & Chr(34)



StrWhichMonth = "AccountPerformanceAllHistory.PerformanceDate = #" & StrWhichMonth _

    & "# AND AccountPerformanceAllHistory.Ticker = " & Chr(34) & "1" & Chr(34) & ""

DoCmd.SetWarnings False

'    DoCmd.OpenReport "RPTAccountPerformanceAllHistorySummary", acViewReport, , StrWhichMonth

DoCmd.OpenReport "RPTAccountPerformanceAllHistorySummary", acViewReport, , StrSearch

DoCmd.SetWarnings True

End Sub

As you might can tell, I added the search for AccountName in the button click code. The old code works fine.

The new code, with the StrSearch string does not work. When the query is run, it prompts for "AccountMaster.AccountName" after the two InputBox prompts. I know this means something is wrong with the StrSearch, but I don't know what is wrong. I've searched around the web, but could not find a solution.

Any help is appreciated.

TIA

The field list in your source query includes this one ...

AccountMaster.AccountName AS Name

Since you aliased the field, the query result set does not include a field named AccountMaster.AccountName, so Access assumes it must be a parameter and asks you to supply a value for it. Use the alias instead instead of the original field name.

Change this ...

& " AND AccountMaster.AccountName Like " & Chr(34) & StrWhichClient & Chr(34)

to this ...

& " AND [Name] Like " & Chr(34) & StrWhichClient & Chr(34)