RE: getting values from a form

Tech-Archive recommends: Fix windows errors by optimizing your registry

Insert this line before TRANSFORM --
PARAMETERS [Forms]![Main Menu]![RSM] Text ( 255 ), [Forms]![Main
Menu]![Year] Text ( 255 ), [Forms]![Main Menu]![Week] Text ( 255 );

"Audrey1980" wrote:

Hi

I have a query with 3 tables - RSM Store listing (holding employee data),
MSR Hierarchy (department listings) , validdatabody. Validdatabody includes
margin values against week and year for the store and sales person (employee
aka RSM).

I am trying to create a crosstab query to get the margin(value) for a
specific department and subdepartment (row headings) for each store (column
heading). I am taking the values specified in a form to show data only for a
specific employee, week and year. Currently I am doing this in design view -
I am using the expression builder in the where clause to try to pull data
from the form where the user specifies the RSM, Week and Year. However, it
isnt recognising the form field names. I am getting an error stating that
Access does not recognise ‘[Forms]![Main Menu]![RSM]’ as a valid field name
or expression. Please help as I've drilled down to get this using the
Expression Builder.

Here is the corresponding SQL statement. Any ideas??
Thanks

TRANSFORM
IIf(Sum([VALUEAMOUNTEXCVAT])=0,0,Sum([MARGIN])/Sum([VALUEAMOUNTEXCVAT])) AS
[margin %]
SELECT [MSR HIERARCHY].ValueType, VALIDDATABODY.MSRDepartment,
VALIDDATABODY.MSRDepartmentName, VALIDDATABODY.MSRSubDepartment,
VALIDDATABODY.MSRSubDepartmentName
FROM (VALIDDATABODY LEFT JOIN [RSM STORE LISTING] ON
VALIDDATABODY.storeNumber = [RSM STORE LISTING].Store) LEFT JOIN [MSR
HIERARCHY] ON (VALIDDATABODY.MSRDepartment = [MSR HIERARCHY].MSRDepartment)
AND (VALIDDATABODY.MSRSubDepartment = [MSR HIERARCHY].MSRSubDepartment)
WHERE ((([RSM STORE LISTING].RSM)=[Forms]![Main Menu]![RSM]) AND
((VALIDDATABODY.week)=[Forms]![Main Menu]![Week]) AND
((VALIDDATABODY.year)=[Forms]![Main Menu]![Year]))
GROUP BY [MSR HIERARCHY].ValueType, VALIDDATABODY.MSRDepartment,
VALIDDATABODY.MSRDepartmentName, VALIDDATABODY.MSRSubDepartment,
VALIDDATABODY.MSRSubDepartmentName
ORDER BY [MSR HIERARCHY].ValueType, VALIDDATABODY.MSRDepartment
PIVOT [RSM STORE LISTING].[Store Name]



.

Relevant Pages

  • RE: getting values from a form
    ... MSR Hierarchy (department listings), validdatabody. ... margin values against week and year for the store and sales person (employee ...
    (microsoft.public.access.queries)
  • getting values from a form
    ... MSR Hierarchy, validdatabody. ... margin values against week and year for the store and sales person (employee ... from the form where the user specifies the RSM, ...
    (microsoft.public.access.queries)