CODEWITHSUNDEEP
sqlms-access-2010pivotcrosstab
user2907249
user2907249

Reputation: 879

Using a Form to Filter a Crosstab Query MS Access 2010

I am using Access 2010. I have a crosstab query that I am trying to filter based on the value of a Text box on a Form. The full SQL is as below:

PARAMETERS Forms!SelectForm!Text27 DateTime;

TRANSFORM nz(Sum([Raw Contract Data].[Action Obligation]), 0) AS [SumOfAction Obligation]

SELECT [Raw Contract Data].[NAICS Code], [Raw Contract Data].[NAICS Description], Sum([Raw
Contract Data].[Action Obligation]) AS [Total Of Action Obligation], NZ(round(avg([SumOfAction 
Obligation]),0),) AS [Average Obligation], 
nz(round(stdev([SumOfAction Obligation]),0),0) AS [StDev of CTRs], 
nz(round(var([SumOfAction Obligation]),0),0) AS [Variance of Obligations]

FROM [Raw Contract Data]

WHERE [Raw Contract Data].[Date Signed]  <= Forms!SelectForm!Text27

GROUP BY [Raw Contract Data].[NAICS Code], [Raw Contract Data].[NAICS Description]

PIVOT format([Raw Contract Data].[Date Signed], "yyyy-mm");

The problem is that the query will run but a box pops up asking for the value of Text 27. It won't recognize it from the value that is in it on the form. The WHERE clause will work when I manually type in a date (without the preceding PARAMETERS declaration) but when I want it to reference the value of the text field I get nothing.

Upvotes: 1

Views: 2443

Answers (2)

Access Guru
Access Guru

Reputation: 193

Give a try to this method and let me know if this helps you

PARAMETERS EnterDate DateTime;
TRANSFORM nz(Sum([Raw Contract Data].[Action Obligation]), 0) AS [SumOfAction Obligation]
SELECT [Raw Contract Data].[NAICS Code], [Raw Contract Data].[NAICS Description],
Sum([RawContract Data].[Action Obligation]) AS [Total Of Action Obligation],
NZ(round(avg([SumOfAction Obligation]),0),) AS [Average Obligation],
nz(round(stdev([SumOfAction Obligation]),0),0) AS [StDev of CTRs],
nz(round(var([SumOfAction Obligation]),0),0) AS [Variance of Obligations]
FROM [Raw Contract Data]
WHERE [Raw Contract Data].[Date Signed]  <= EnterDate
GROUP BY [Raw Contract Data].[NAICS Code], [Raw Contract Data].[NAICS Description]
PIVOT format([Raw Contract Data].[Date Signed], "yyyy-mm");

Save this query say name Query1

On Filter action say on clicking filter button define the following code

Dim qdf As DAO.QueryDef
Dim rst as DAO.Recordset
Set qdf = CurrentDb.QueryDefs("Query1")
qdf!EnterDate = Forms!SelectForm!Text27
Set rst = qdf.OpenRecordset
Set rst = Nothing
Set qdf = Nothing

Upvotes: 1

Access Guru
Access Guru

Reputation: 193

Remember you must have form in open state from which you are referring the Text27

Otherwise whenever you execute query, query doesn't get the parameter value because the form is not opened and it will ask you for the value of Text27.

Upvotes: 0

Related Questions