By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Access 2007 Crosstab query with parameters

P: 3
I have a simple database recording complaints. A crosstab query is based on a query which returns resolved complaints only. The crosstab has the field Complaint Type as a row heading and Outcome as a column heading.

I want to be able to select complaints which were resolved in a particular date range (field is Date resolved). This field is in the original query but not the crosstab.

How do I do it?
Nov 12 '08 #1
Share this Question
Share on Google+
6 Replies


Delerna
Expert 100+
P: 1,134
In the query that the crosstab query is based off.

In the criteria for the field [Date Resolved] put the following
Expand|Select|Wrap|Line Numbers
  1. [Date Resolved]>=[Please Enter From Date] and [Date Resolved]<=[Please Enter To Date] 
  2.  
the criteria is referencing the fields [Please Enter From Date] and [Please Enter To Date]. Since these 2 fields don't exist in your query, (I hope), an input box will popup for each of them asking you to input their value.

PS
You can use the BETWEEN function instead if you want.
Nov 12 '08 #2

P: 3
Thank you for replying. I tried that first off but when I try to run the crosstab query I get an error message:

"The Microsoft Office Access database engine does not recognize '[startdate]' as a valid field or expression."
Nov 12 '08 #3

P: 3
Got it!

SQL is:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [start] DateTime, [end] DateTime;
  2. TRANSFORM Count(qResolved.ComplaintNumber) AS CountOfComplaintNumber
  3. SELECT qResolved.[Complaint type], Count(qResolved.ComplaintNumber) AS [Total Of ComplaintNumber]
  4. FROM qResolved
  5. WHERE (qResolved.[Date Resolved] BETWEEN [start] AND [end])
  6. GROUP BY qResolved.[Complaint type]
  7. PIVOT qResolved.Outcome;
  8.  
Nov 12 '08 #4

P: 68
I am having hell with my Cross tab query. I am trying to arrive at a table that displays a summary of job status by staff over a given period.

Here is the SQL I am using
TRANSFORM Count(SupportLogs.Status) AS CountOfStatus
SELECT SupportLogs.SupportStaff
FROM SupportLogs
WHERE (((SupportLogs.DateReported) Between [Forms]![frmSearch]![BegDate] And [Forms]![frmSearch]![EndDate]))
GROUP BY SupportLogs.SupportStaff
PIVOT SupportLogs.Status;

When I run this it gives me an error stating that "[Forms]![frmSearch]![BegDate]" is not a valid field name or expression.

I get the same thing if i try to use parameters and have it prompt for the date when it is executed.
Help Please!
Dec 18 '08 #5

Delerna
Expert 100+
P: 1,134
I havent used a pivot query in access that looks quite like yours so this is only a guess.
I am assuming that you are using text boxes for the dates
Try putting a .Text on the end of those controls.

[Forms]![frmSearch]![EndDate].Text

This statement
I get the same thing if i try to use parameters and have it prompt for the date when it is executed.
leads me to believe it won't work, but worth a try
Dec 18 '08 #6

P: n/a
I don't know if anyone answered this, but I found the answer today in doing the following:

Click on Parameters in your Query Design and enter [Forms]![NameofForm]![BeginDate] and select Date/Time for format. Do this for the begin date and again separately for end date on separate lines and close. Then run your query.
Oct 3 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.