Crosstab Query - Passing textbox as Parameter

[weweber3]

The crosstab (FUNDING_SOURCE_RPT) is based off of (FUNDING_SOURCE_01).

Here is FUNDING_SOURCE_01 -

SELECT tbl_Visit.FundingSourceID, Funding_Source.FundingSourceDescription, tbl_Visit.MedicalID, tbl_Visit.DateOfVisit, IIf(tbl_Visit.MedicalID=1,"Initial",IIf(tbl_Visit.MedicalID=2,'Annual',IIf(tbl_Visit.MedicalID=3,"Follow-up",IIf(tbl_Visit.MedicalID=4,"Pregnancy Test",IIf(tbl_Visit.MedicalID=5,"Other","Supply"))))) AS MedicalDescription, tbl_Visit.VisitTypeID, tbl_Visit.ClinicID, tbl_Clinic.ClinicName, tbl_Clinic.ClinicAgencyID, tbl_Agency.AgencyName
FROM tbl_Agency INNER JOIN ((tbl_Visit INNER JOIN Funding_Source ON tbl_Visit.FundingSourceID = Funding_Source.FundingSource) INNER JOIN tbl_Clinic ON tbl_Visit.ClinicID = tbl_Clinic.ClinicID) ON tbl_Agency.AgencyID = tbl_Clinic.ClinicAgencyID
WHERE (((tbl_Visit.ClinicID) In ([Forms]![frm_PatientVisitReport_02]![txtClinics])) AND ((tbl_Clinic.ClinicAgencyID) In ([Forms]![frm_PatientVisitReport_01]![txtClient]))) OR ((([Forms]![frm_PatientVisitReport_02]![txtClinics].[value]) Is Null) AND (([Forms]![frm_PatientVisitReport_01]![txtClient].[value]) Is Null)) OR ((([Forms]![frm_PatientVisitReport_02]![txtClinics].[value]) Is Null) AND (([Forms]![frm_PatientVisitReport_01]![txtClient].[value]) Is Null));

Here is FUNDING_SOURCE_RPT -

TRANSFORM IIf(Count(FUNDING_SOURCE_1.MedicalID) Is Null,"0",Count(FUNDING_SOURCE_1.MedicalID)) AS CountOfMedicalID
SELECT FUNDING_SOURCE_1.MedicalDescription, Count(FUNDING_SOURCE_1.MedicalID) AS [Total Of MedicalID]
FROM FUNDING_SOURCE_1
GROUP BY FUNDING_SOURCE_1.MedicalDescription
PIVOT FUNDING_SOURCE_1.FundingSourceDescription;

Here is the message I am receiving -
"The Microsoft Jet database engine does not recognize '[Forms]![frm_PatientVisitReport_02]![txtClinics]' as a valid field name or expression'

Thank you for your help.

 

[dhookom]

You must specify the data types of your parameters when the results are displayed in a crosstab. Select Query|Parameters and then enter:
[Forms]![frm_PatientVisitReport_02]![txtClinics] Text
...etc...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[weweber3]

Would I put this in the crosstab query or in the original query?

 

[dhookom]

Either should work. I would probably place the parameters in the query where the parameters are first used.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.