Solved: CHOOSE Function assistance (Access '97)

[phendrena]

Hi There,

I have setup the following, i'd like to amend this to include a choose function in the WHERE line. Instead of restricting this to one brand, i'd like to user to be able to pick a brand themselves.

How best can I do this?

TRANSFORM Count([Inbound_Call_Log].[CallReason]) AS TotalCalls
SELECT [Inbound_Call_Log].[CallReason]
FROM Inbound_Call_Log
WHERE ([Inbound_Call_Log].[Scheme] = "Ford")
GROUP BY [Inbound_Call_Log].[CallReason]
PIVOT Format([Inbound_Call_Log].[DateOfCall],"mmm") IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Many Thanks,

[OBP]

I would suggest an Unbound Combo box on your Form where the user can select a "Scheme" and use that as the Criteria row of the Query which is your "Where" statement.
You would replace "Ford" with
me.Combo
where Combo is the actual name of your Combo box.
I am not certain of the exact Syntax as I am not familiar with your overall statement.

[phendrena]

Hi OBP,

Thanks for the reply.

The above code is setup as a crosstab query and not as part of a form.
I was hoping that the end user would be able to launch the query (or an attached report) and Access would prompt them for brand and produce the query/report accordingly.

[OBP]

I always use a Form to enter the Criteria (Filter) in and then use that on a Pre-Query which I then Crosstab. That way you can get the "Criteria" working in a normal select query and then Crosstab it.
I am not sure if Access 97 is the same as Access 2000-2003, but if it is you will have to Declare the Criteria as Parameters.
I use the Forms![Formname]![Fieldname] in both the Criteria row and parameters, where Formname is the actual name of your form and Fieldname is the actual name of your field.

[phendrena]

I always use a Form to enter the Criteria (Filter) in and then use that on a Pre-Query which I then Crosstab. That way you can get the "Criteria" working in a normal select query and then Crosstab it.
I am not sure if Access 97 is the same as Access 2000-2003, but if it is you will have to Declare the Criteria as Parameters.
I use the Forms![Formname]![Fieldname] in both the Criteria row and parameters, where Formname is the actual name of your form and Fieldname is the actual name of your field.

Hi OBP,

Thanks for the reply, For now my knowledge of Access & SQL isn't great as such, your suggestion is something for me to do further down the line as I currently won't know where to begin to setup a form and use the criteria entered into the form as a basis for producing queries & reports.

Would you therefore be able to advise how I can peform what I have asked in my Original Post without constructing a form and/or provide some pointers on how I would build a 'report wizard' form?

Thanks,

[OBP]

I just added an "enter month" parameter to a Crosstab in Acccess 2000 and it produced this SQL statement

PARAMETERS [enter month] Text ( 255 );
TRANSFORM Count([8D Summary for Graphs].[Ref NO]) AS [CountOfRef NO]
SELECT Format([Open Date],"yyyy") AS [Year], [8D Summary for Graphs].DEPARTMENT, Count([8D Summary for Graphs].[Ref NO]) AS [Total Of Ref NO]
FROM [8D Summary for Graphs]
WHERE (((Format([Open Date],"mmm"))=[enter month]))
GROUP BY Format([Open Date],"yyyy"), [8D Summary for Graphs].DEPARTMENT
PIVOT Format([Open Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec" );


Note the Parameter at the start of the statement.

[phendrena]

Hi OBP,

Adding the PARAMETERS line and then changing the WHERE from ="Ford" to =[Enter Brand] works nicely.

Thank you.

Would you be able to provide me with some suggestions on setting up a form to be used as the basis for a query/report? I did track down a post of yours on another forum (techguy?) that did include an example database but it was unfortunetly in a later version of Access so I am unable to view it.