Cannot filter by criteria; also no sorting

S

smhowell

One of my fields goes to a module which should be returning a string. Below
is the SQL.

SELECT "Calculated Emissions Do Not Match Reported Emissions" AS [QC Issue],
"21b" AS [Query No], 2007 AS [Inv Year], Format(Now(),"m/d/yyyy") AS [Query
Ran], facility.facInventoryAddDate, facility.facInventoryAddStaff,
company.comp_id, company.company, facility.fac_id, facility.facility,
facility.sic,
IIf([TblReportingfacilities_deduped_CERR_TypeA]![ReportingFacId] Is Not
Null,"CERR Type
A",IIf([TblReportingfacilities_deduped_CERR_TypeB]![ReportingFacId] Is Not
Null,"CERR Type B",[airs_codes]![description])) AS [NEI facility?],
[QueryQC_2007-TotalFacilityEmissions_smh].SumOfemsAmountreported AS
TotFacEms, "Tons" AS Tons, tblPoints2007.pntId, tblPoints2007.pntSequence,
tblPoints2007.pntName, "Process ID: " AS [Process ID Text],
tblPointProcesses2007.pprocId, tblPointProcesses2007.pprocSccId,
tblSCC.SCC_L3, tblSCC.SCC_L4, "Annual Process Rate:" AS [Annual Process
Rate], tblPointProcesses2007.pprocAnnualRate,
tblInvCodesUnits_2.UNIT_DESCRIPTION, "Annual Hours of Operation:" AS [Annual
Hours of Operation], tblPointProcesses2007.pprocHoursPerYear, "Hours" AS
[Hours Text], "HP Rating From Point Name:" AS [HP Rating From Point Name],
CDbl(Nz([QueryQC_2007-21a-HP]![HP],0)) AS HP, "HP" AS [HP Text], "Process
Fuel Heat Content:" AS [Process Fuel Heat Content],
tblPointProcesses2007.pprocMMBTU, "MMBTU Per Standard Unit" AS [MMBTU Per
Standard Unit], tblPollutants.plutDesc, "Calculation Method: " AS
[Calculation Method], tblInvCodesMethod.methDesc, "Emission Factor: " AS [Ems
Fac Text], tblEmissions2007.emsFactor, tblInvCodesUnits.UNIT_DESCRIPTION,
IIf([emsfactorUnitsNum]=128 And [emsfactorUnits]=128,"N/A","Per") AS Per,
tblInvCodesUnits_1.UNIT_DESCRIPTION, "Primary Control:" AS [Primary Control],
tblInvControlEquipment.ControlDevice, "Efficiency:" AS Efficiency,
[emsControl1Efficiency]*100 AS [Control1 Percent], "%" AS [Percent],
"Secondary Control:" AS [Secondary Control],
tblInvControlEquipment_1.ControlDevice, "Efficiency:" AS Efficiency2,
[emsControl2Efficiency]*100 AS [Control2 Percent], "%" AS Percent2, "Reported
Emissions:" AS [Reported Ems Text], tblEmissions2007.emsAmountreported AS
Emissions, "Tons" AS Tons2,
Calccheck([emsAmountreported],[HP],[pprocAnnualRate],[pprocMMBTU],[emsFactor],[pprocHoursPerYear],[emsControl1Efficiency],[emsControl2Efficiency],[ThroughputUnitsID],[emsFactorUnitsNum],[emsFactorUnits])
AS [Calculated Result], "Point Notes: " AS PntNotesText,
tblPoints2007.pntNotes, "Facility Notes: " AS FacNotesText,
facility.facMemoToStaff

FROM (((((((((((tblInvCodesUnits AS tblInvCodesUnits_1 INNER JOIN
((((facility LEFT JOIN company ON facility.comp_id = company.comp_id) INNER
JOIN tblPoints2007 ON facility.fac_id = tblPoints2007.pntFacId) INNER JOIN
(tblPointProcesses2007 INNER JOIN tblEmissions2007 ON
tblPointProcesses2007.pprocId = tblEmissions2007.emsProcId) ON
tblPoints2007.pntId = tblPointProcesses2007.pprocPntId) INNER JOIN
tblPollutants ON tblEmissions2007.emsPlutId = tblPollutants.plutId) ON
tblInvCodesUnits_1.UnitId = tblEmissions2007.emsFactorUnits) INNER JOIN
tblSCC ON tblPointProcesses2007.pprocSccId = tblSCC.SCC) LEFT JOIN
TblReportingfacilities_deduped_CERR_TypeB ON facility.fac_id =
TblReportingfacilities_deduped_CERR_TypeB.ReportingfacId) LEFT JOIN
airs_codes ON facility.airs_classification_code = airs_codes.airs_code_id)
LEFT JOIN tblInvCodesMethod ON tblEmissions2007.emsEstimatedMethodId =
tblInvCodesMethod.methId) LEFT JOIN TblReportingfacilities_deduped_CERR_TypeA
ON facility.fac_id =
TblReportingfacilities_deduped_CERR_TypeA.ReportingFacId) INNER JOIN
[QueryQC_2007-TotalFacilityEmissions_smh] ON tblPoints2007.pntId =
[QueryQC_2007-TotalFacilityEmissions_smh].pntId) LEFT JOIN
[QueryQC_2007-21a-HP] ON tblPoints2007.pntId = [QueryQC_2007-21a-HP].pntId)
INNER JOIN tblInvCodesUnits AS tblInvCodesUnits_2 ON
tblPointProcesses2007.ThroughputUnitsID = tblInvCodesUnits_2.UnitId) INNER
JOIN tblInvCodesUnits ON tblEmissions2007.emsFactorUnitsNum =
tblInvCodesUnits.UnitId) INNER JOIN tblInvControlEquipment ON
tblEmissions2007.ControlDevice1 = tblInvControlEquipment.ID) INNER JOIN
tblInvControlEquipment AS tblInvControlEquipment_1 ON
tblEmissions2007.ControlDevice2 = tblInvControlEquipment_1.ID;

Everything works fine until I try to use the criteria on that field either
for filtering or sorting, then I get a data type mismatch error. I'm using
Access 2007. Casting to CStr() has no effect.

Thanks in advance.
Sharon
 
D

Dale Fye

Which field is causing the problem?
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



smhowell said:
One of my fields goes to a module which should be returning a string. Below
is the SQL.

SELECT "Calculated Emissions Do Not Match Reported Emissions" AS [QC Issue],
"21b" AS [Query No], 2007 AS [Inv Year], Format(Now(),"m/d/yyyy") AS [Query
Ran], facility.facInventoryAddDate, facility.facInventoryAddStaff,
company.comp_id, company.company, facility.fac_id, facility.facility,
facility.sic,
IIf([TblReportingfacilities_deduped_CERR_TypeA]![ReportingFacId] Is Not
Null,"CERR Type
A",IIf([TblReportingfacilities_deduped_CERR_TypeB]![ReportingFacId] Is Not
Null,"CERR Type B",[airs_codes]![description])) AS [NEI facility?],
[QueryQC_2007-TotalFacilityEmissions_smh].SumOfemsAmountreported AS
TotFacEms, "Tons" AS Tons, tblPoints2007.pntId, tblPoints2007.pntSequence,
tblPoints2007.pntName, "Process ID: " AS [Process ID Text],
tblPointProcesses2007.pprocId, tblPointProcesses2007.pprocSccId,
tblSCC.SCC_L3, tblSCC.SCC_L4, "Annual Process Rate:" AS [Annual Process
Rate], tblPointProcesses2007.pprocAnnualRate,
tblInvCodesUnits_2.UNIT_DESCRIPTION, "Annual Hours of Operation:" AS [Annual
Hours of Operation], tblPointProcesses2007.pprocHoursPerYear, "Hours" AS
[Hours Text], "HP Rating From Point Name:" AS [HP Rating From Point Name],
CDbl(Nz([QueryQC_2007-21a-HP]![HP],0)) AS HP, "HP" AS [HP Text], "Process
Fuel Heat Content:" AS [Process Fuel Heat Content],
tblPointProcesses2007.pprocMMBTU, "MMBTU Per Standard Unit" AS [MMBTU Per
Standard Unit], tblPollutants.plutDesc, "Calculation Method: " AS
[Calculation Method], tblInvCodesMethod.methDesc, "Emission Factor: " AS [Ems
Fac Text], tblEmissions2007.emsFactor, tblInvCodesUnits.UNIT_DESCRIPTION,
IIf([emsfactorUnitsNum]=128 And [emsfactorUnits]=128,"N/A","Per") AS Per,
tblInvCodesUnits_1.UNIT_DESCRIPTION, "Primary Control:" AS [Primary Control],
tblInvControlEquipment.ControlDevice, "Efficiency:" AS Efficiency,
[emsControl1Efficiency]*100 AS [Control1 Percent], "%" AS [Percent],
"Secondary Control:" AS [Secondary Control],
tblInvControlEquipment_1.ControlDevice, "Efficiency:" AS Efficiency2,
[emsControl2Efficiency]*100 AS [Control2 Percent], "%" AS Percent2, "Reported
Emissions:" AS [Reported Ems Text], tblEmissions2007.emsAmountreported AS
Emissions, "Tons" AS Tons2,
Calccheck([emsAmountreported],[HP],[pprocAnnualRate],[pprocMMBTU],[emsFactor],[pprocHoursPerYear],[emsControl1Efficiency],[emsControl2Efficiency],[ThroughputUnitsID],[emsFactorUnitsNum],[emsFactorUnits])
AS [Calculated Result], "Point Notes: " AS PntNotesText,
tblPoints2007.pntNotes, "Facility Notes: " AS FacNotesText,
facility.facMemoToStaff

FROM (((((((((((tblInvCodesUnits AS tblInvCodesUnits_1 INNER JOIN
((((facility LEFT JOIN company ON facility.comp_id = company.comp_id) INNER
JOIN tblPoints2007 ON facility.fac_id = tblPoints2007.pntFacId) INNER JOIN
(tblPointProcesses2007 INNER JOIN tblEmissions2007 ON
tblPointProcesses2007.pprocId = tblEmissions2007.emsProcId) ON
tblPoints2007.pntId = tblPointProcesses2007.pprocPntId) INNER JOIN
tblPollutants ON tblEmissions2007.emsPlutId = tblPollutants.plutId) ON
tblInvCodesUnits_1.UnitId = tblEmissions2007.emsFactorUnits) INNER JOIN
tblSCC ON tblPointProcesses2007.pprocSccId = tblSCC.SCC) LEFT JOIN
TblReportingfacilities_deduped_CERR_TypeB ON facility.fac_id =
TblReportingfacilities_deduped_CERR_TypeB.ReportingfacId) LEFT JOIN
airs_codes ON facility.airs_classification_code = airs_codes.airs_code_id)
LEFT JOIN tblInvCodesMethod ON tblEmissions2007.emsEstimatedMethodId =
tblInvCodesMethod.methId) LEFT JOIN TblReportingfacilities_deduped_CERR_TypeA
ON facility.fac_id =
TblReportingfacilities_deduped_CERR_TypeA.ReportingFacId) INNER JOIN
[QueryQC_2007-TotalFacilityEmissions_smh] ON tblPoints2007.pntId =
[QueryQC_2007-TotalFacilityEmissions_smh].pntId) LEFT JOIN
[QueryQC_2007-21a-HP] ON tblPoints2007.pntId = [QueryQC_2007-21a-HP].pntId)
INNER JOIN tblInvCodesUnits AS tblInvCodesUnits_2 ON
tblPointProcesses2007.ThroughputUnitsID = tblInvCodesUnits_2.UnitId) INNER
JOIN tblInvCodesUnits ON tblEmissions2007.emsFactorUnitsNum =
tblInvCodesUnits.UnitId) INNER JOIN tblInvControlEquipment ON
tblEmissions2007.ControlDevice1 = tblInvControlEquipment.ID) INNER JOIN
tblInvControlEquipment AS tblInvControlEquipment_1 ON
tblEmissions2007.ControlDevice2 = tblInvControlEquipment_1.ID;

Everything works fine until I try to use the criteria on that field either
for filtering or sorting, then I get a data type mismatch error. I'm using
Access 2007. Casting to CStr() has no effect.

Thanks in advance.
Sharon
Click to expand...
 
J

John Spencer

I would suspect that your CalcCheck routine is erroring on some records.
It may be that the routine cannot handle null values or is doing a
divide by zero someplace or ???.

When you try to Order by or Filter by the results of that function
(especially Filter by) then if any error is "returned" will cause the
order by or filter by in the SQL statement to error out for the ENTIRE
query. If you are just returning the field in the SELECT clause, the
query may not ever see the error since the "bad" record may be filtered
out before the calculation takes place.

By the way, it would be nice to TELL us the field that is causing the
problem instead of relying on us to look at a long SQL statement, parse
out what is probably happening, and then guess which field (or fields)
is causing the problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

One of my fields goes to a module which should be returning a string. Below
is the SQL.

SELECT "Calculated Emissions Do Not Match Reported Emissions" AS [QC Issue],
"21b" AS [Query No], 2007 AS [Inv Year], Format(Now(),"m/d/yyyy") AS [Query
Ran], facility.facInventoryAddDate, facility.facInventoryAddStaff,
company.comp_id, company.company, facility.fac_id, facility.facility,
facility.sic,
IIf([TblReportingfacilities_deduped_CERR_TypeA]![ReportingFacId] Is Not
Null,"CERR Type
A",IIf([TblReportingfacilities_deduped_CERR_TypeB]![ReportingFacId] Is Not
Null,"CERR Type B",[airs_codes]![description])) AS [NEI facility?],
[QueryQC_2007-TotalFacilityEmissions_smh].SumOfemsAmountreported AS
TotFacEms, "Tons" AS Tons, tblPoints2007.pntId, tblPoints2007.pntSequence,
tblPoints2007.pntName, "Process ID: " AS [Process ID Text],
tblPointProcesses2007.pprocId, tblPointProcesses2007.pprocSccId,
tblSCC.SCC_L3, tblSCC.SCC_L4, "Annual Process Rate:" AS [Annual Process
Rate], tblPointProcesses2007.pprocAnnualRate,
tblInvCodesUnits_2.UNIT_DESCRIPTION, "Annual Hours of Operation:" AS [Annual
Hours of Operation], tblPointProcesses2007.pprocHoursPerYear, "Hours" AS
[Hours Text], "HP Rating From Point Name:" AS [HP Rating From Point Name],
CDbl(Nz([QueryQC_2007-21a-HP]![HP],0)) AS HP, "HP" AS [HP Text], "Process
Fuel Heat Content:" AS [Process Fuel Heat Content],
tblPointProcesses2007.pprocMMBTU, "MMBTU Per Standard Unit" AS [MMBTU Per
Standard Unit], tblPollutants.plutDesc, "Calculation Method: " AS
[Calculation Method], tblInvCodesMethod.methDesc, "Emission Factor: " AS [Ems
Fac Text], tblEmissions2007.emsFactor, tblInvCodesUnits.UNIT_DESCRIPTION,
IIf([emsfactorUnitsNum]=128 And [emsfactorUnits]=128,"N/A","Per") AS Per,
tblInvCodesUnits_1.UNIT_DESCRIPTION, "Primary Control:" AS [Primary Control],
tblInvControlEquipment.ControlDevice, "Efficiency:" AS Efficiency,
[emsControl1Efficiency]*100 AS [Control1 Percent], "%" AS [Percent],
"Secondary Control:" AS [Secondary Control],
tblInvControlEquipment_1.ControlDevice, "Efficiency:" AS Efficiency2,
[emsControl2Efficiency]*100 AS [Control2 Percent], "%" AS Percent2, "Reported
Emissions:" AS [Reported Ems Text], tblEmissions2007.emsAmountreported AS
Emissions, "Tons" AS Tons2,
Calccheck([emsAmountreported],[HP],[pprocAnnualRate],[pprocMMBTU],[emsFactor],[pprocHoursPerYear],[emsControl1Efficiency],[emsControl2Efficiency],[ThroughputUnitsID],[emsFactorUnitsNum],[emsFactorUnits])
AS [Calculated Result], "Point Notes: " AS PntNotesText,
tblPoints2007.pntNotes, "Facility Notes: " AS FacNotesText,
facility.facMemoToStaff

FROM (((((((((((tblInvCodesUnits AS tblInvCodesUnits_1 INNER JOIN
((((facility LEFT JOIN company ON facility.comp_id = company.comp_id) INNER
JOIN tblPoints2007 ON facility.fac_id = tblPoints2007.pntFacId) INNER JOIN
(tblPointProcesses2007 INNER JOIN tblEmissions2007 ON
tblPointProcesses2007.pprocId = tblEmissions2007.emsProcId) ON
tblPoints2007.pntId = tblPointProcesses2007.pprocPntId) INNER JOIN
tblPollutants ON tblEmissions2007.emsPlutId = tblPollutants.plutId) ON
tblInvCodesUnits_1.UnitId = tblEmissions2007.emsFactorUnits) INNER JOIN
tblSCC ON tblPointProcesses2007.pprocSccId = tblSCC.SCC) LEFT JOIN
TblReportingfacilities_deduped_CERR_TypeB ON facility.fac_id =
TblReportingfacilities_deduped_CERR_TypeB.ReportingfacId) LEFT JOIN
airs_codes ON facility.airs_classification_code = airs_codes.airs_code_id)
LEFT JOIN tblInvCodesMethod ON tblEmissions2007.emsEstimatedMethodId =
tblInvCodesMethod.methId) LEFT JOIN TblReportingfacilities_deduped_CERR_TypeA
ON facility.fac_id =
TblReportingfacilities_deduped_CERR_TypeA.ReportingFacId) INNER JOIN
[QueryQC_2007-TotalFacilityEmissions_smh] ON tblPoints2007.pntId =
[QueryQC_2007-TotalFacilityEmissions_smh].pntId) LEFT JOIN
[QueryQC_2007-21a-HP] ON tblPoints2007.pntId = [QueryQC_2007-21a-HP].pntId)
INNER JOIN tblInvCodesUnits AS tblInvCodesUnits_2 ON
tblPointProcesses2007.ThroughputUnitsID = tblInvCodesUnits_2.UnitId) INNER
JOIN tblInvCodesUnits ON tblEmissions2007.emsFactorUnitsNum =
tblInvCodesUnits.UnitId) INNER JOIN tblInvControlEquipment ON
tblEmissions2007.ControlDevice1 = tblInvControlEquipment.ID) INNER JOIN
tblInvControlEquipment AS tblInvControlEquipment_1 ON
tblEmissions2007.ControlDevice2 = tblInvControlEquipment_1.ID;

Everything works fine until I try to use the criteria on that field either
for filtering or sorting, then I get a data type mismatch error. I'm using
Access 2007. Casting to CStr() has no effect.

Thanks in advance.
Sharon
Click to expand...
 
S

smhowell

Sorry, you are right, it is the calcheck function that I was referring to.
Will try to be more specific in the future.

I have to post at home, but this problem is at work. I will copy out the
results and check them with Excel if I have enough resources in my work
computer. The module returns 63,000 records when unfiltered.

Sharon

John Spencer said:
I would suspect that your CalcCheck routine is erroring on some records.
It may be that the routine cannot handle null values or is doing a
divide by zero someplace or ???.

When you try to Order by or Filter by the results of that function
(especially Filter by) then if any error is "returned" will cause the
order by or filter by in the SQL statement to error out for the ENTIRE
query. If you are just returning the field in the SELECT clause, the
query may not ever see the error since the "bad" record may be filtered
out before the calculation takes place.

By the way, it would be nice to TELL us the field that is causing the
problem instead of relying on us to look at a long SQL statement, parse
out what is probably happening, and then guess which field (or fields)
is causing the problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

One of my fields goes to a module which should be returning a string. Below
is the SQL.

SELECT "Calculated Emissions Do Not Match Reported Emissions" AS [QC Issue],
"21b" AS [Query No], 2007 AS [Inv Year], Format(Now(),"m/d/yyyy") AS [Query
Ran], facility.facInventoryAddDate, facility.facInventoryAddStaff,
company.comp_id, company.company, facility.fac_id, facility.facility,
facility.sic,
IIf([TblReportingfacilities_deduped_CERR_TypeA]![ReportingFacId] Is Not
Null,"CERR Type
A",IIf([TblReportingfacilities_deduped_CERR_TypeB]![ReportingFacId] Is Not
Null,"CERR Type B",[airs_codes]![description])) AS [NEI facility?],
[QueryQC_2007-TotalFacilityEmissions_smh].SumOfemsAmountreported AS
TotFacEms, "Tons" AS Tons, tblPoints2007.pntId, tblPoints2007.pntSequence,
tblPoints2007.pntName, "Process ID: " AS [Process ID Text],
tblPointProcesses2007.pprocId, tblPointProcesses2007.pprocSccId,
tblSCC.SCC_L3, tblSCC.SCC_L4, "Annual Process Rate:" AS [Annual Process
Rate], tblPointProcesses2007.pprocAnnualRate,
tblInvCodesUnits_2.UNIT_DESCRIPTION, "Annual Hours of Operation:" AS [Annual
Hours of Operation], tblPointProcesses2007.pprocHoursPerYear, "Hours" AS
[Hours Text], "HP Rating From Point Name:" AS [HP Rating From Point Name],
CDbl(Nz([QueryQC_2007-21a-HP]![HP],0)) AS HP, "HP" AS [HP Text], "Process
Fuel Heat Content:" AS [Process Fuel Heat Content],
tblPointProcesses2007.pprocMMBTU, "MMBTU Per Standard Unit" AS [MMBTU Per
Standard Unit], tblPollutants.plutDesc, "Calculation Method: " AS
[Calculation Method], tblInvCodesMethod.methDesc, "Emission Factor: " AS [Ems
Fac Text], tblEmissions2007.emsFactor, tblInvCodesUnits.UNIT_DESCRIPTION,
IIf([emsfactorUnitsNum]=128 And [emsfactorUnits]=128,"N/A","Per") AS Per,
tblInvCodesUnits_1.UNIT_DESCRIPTION, "Primary Control:" AS [Primary Control],
tblInvControlEquipment.ControlDevice, "Efficiency:" AS Efficiency,
[emsControl1Efficiency]*100 AS [Control1 Percent], "%" AS [Percent],
"Secondary Control:" AS [Secondary Control],
tblInvControlEquipment_1.ControlDevice, "Efficiency:" AS Efficiency2,
[emsControl2Efficiency]*100 AS [Control2 Percent], "%" AS Percent2, "Reported
Emissions:" AS [Reported Ems Text], tblEmissions2007.emsAmountreported AS
Emissions, "Tons" AS Tons2,
Calccheck([emsAmountreported],[HP],[pprocAnnualRate],[pprocMMBTU],[emsFactor],[pprocHoursPerYear],[emsControl1Efficiency],[emsControl2Efficiency],[ThroughputUnitsID],[emsFactorUnitsNum],[emsFactorUnits])
AS [Calculated Result], "Point Notes: " AS PntNotesText,
tblPoints2007.pntNotes, "Facility Notes: " AS FacNotesText,
facility.facMemoToStaff

FROM (((((((((((tblInvCodesUnits AS tblInvCodesUnits_1 INNER JOIN
((((facility LEFT JOIN company ON facility.comp_id = company.comp_id) INNER
JOIN tblPoints2007 ON facility.fac_id = tblPoints2007.pntFacId) INNER JOIN
(tblPointProcesses2007 INNER JOIN tblEmissions2007 ON
tblPointProcesses2007.pprocId = tblEmissions2007.emsProcId) ON
tblPoints2007.pntId = tblPointProcesses2007.pprocPntId) INNER JOIN
tblPollutants ON tblEmissions2007.emsPlutId = tblPollutants.plutId) ON
tblInvCodesUnits_1.UnitId = tblEmissions2007.emsFactorUnits) INNER JOIN
tblSCC ON tblPointProcesses2007.pprocSccId = tblSCC.SCC) LEFT JOIN
TblReportingfacilities_deduped_CERR_TypeB ON facility.fac_id =
TblReportingfacilities_deduped_CERR_TypeB.ReportingfacId) LEFT JOIN
airs_codes ON facility.airs_classification_code = airs_codes.airs_code_id)
LEFT JOIN tblInvCodesMethod ON tblEmissions2007.emsEstimatedMethodId =
tblInvCodesMethod.methId) LEFT JOIN TblReportingfacilities_deduped_CERR_TypeA
ON facility.fac_id =
TblReportingfacilities_deduped_CERR_TypeA.ReportingFacId) INNER JOIN
[QueryQC_2007-TotalFacilityEmissions_smh] ON tblPoints2007.pntId =
[QueryQC_2007-TotalFacilityEmissions_smh].pntId) LEFT JOIN
[QueryQC_2007-21a-HP] ON tblPoints2007.pntId = [QueryQC_2007-21a-HP].pntId)
INNER JOIN tblInvCodesUnits AS tblInvCodesUnits_2 ON
tblPointProcesses2007.ThroughputUnitsID = tblInvCodesUnits_2.UnitId) INNER
JOIN tblInvCodesUnits ON tblEmissions2007.emsFactorUnitsNum =
tblInvCodesUnits.UnitId) INNER JOIN tblInvControlEquipment ON
tblEmissions2007.ControlDevice1 = tblInvControlEquipment.ID) INNER JOIN
tblInvControlEquipment AS tblInvControlEquipment_1 ON
tblEmissions2007.ControlDevice2 = tblInvControlEquipment_1.ID;

Everything works fine until I try to use the criteria on that field either
for filtering or sorting, then I get a data type mismatch error. I'm using
Access 2007. Casting to CStr() has no effect.

Thanks in advance.
Sharon
Click to expand...
Click to expand...
 
J

John Spencer

You might consider posting the VBA code of the CalCheck Function.
Someone might be able to point out what could be the cause of the
problem and work out a solution to handle the problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Sorry, you are right, it is the calcheck function that I was referring to.
Will try to be more specific in the future.

I have to post at home, but this problem is at work. I will copy out the
results and check them with Excel if I have enough resources in my work
computer. The module returns 63,000 records when unfiltered.

Sharon

John Spencer said:
I would suspect that your CalcCheck routine is erroring on some records.
It may be that the routine cannot handle null values or is doing a
divide by zero someplace or ???.

When you try to Order by or Filter by the results of that function
(especially Filter by) then if any error is "returned" will cause the
order by or filter by in the SQL statement to error out for the ENTIRE
query. If you are just returning the field in the SELECT clause, the
query may not ever see the error since the "bad" record may be filtered
out before the calculation takes place.

By the way, it would be nice to TELL us the field that is causing the
problem instead of relying on us to look at a long SQL statement, parse
out what is probably happening, and then guess which field (or fields)
is causing the problem.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

One of my fields goes to a module which should be returning a string. Below
is the SQL.

SELECT "Calculated Emissions Do Not Match Reported Emissions" AS [QC Issue],
"21b" AS [Query No], 2007 AS [Inv Year], Format(Now(),"m/d/yyyy") AS [Query
Ran], facility.facInventoryAddDate, facility.facInventoryAddStaff,
company.comp_id, company.company, facility.fac_id, facility.facility,
facility.sic,
IIf([TblReportingfacilities_deduped_CERR_TypeA]![ReportingFacId] Is Not
Null,"CERR Type
A",IIf([TblReportingfacilities_deduped_CERR_TypeB]![ReportingFacId] Is Not
Null,"CERR Type B",[airs_codes]![description])) AS [NEI facility?],
[QueryQC_2007-TotalFacilityEmissions_smh].SumOfemsAmountreported AS
TotFacEms, "Tons" AS Tons, tblPoints2007.pntId, tblPoints2007.pntSequence,
tblPoints2007.pntName, "Process ID: " AS [Process ID Text],
tblPointProcesses2007.pprocId, tblPointProcesses2007.pprocSccId,
tblSCC.SCC_L3, tblSCC.SCC_L4, "Annual Process Rate:" AS [Annual Process
Rate], tblPointProcesses2007.pprocAnnualRate,
tblInvCodesUnits_2.UNIT_DESCRIPTION, "Annual Hours of Operation:" AS [Annual
Hours of Operation], tblPointProcesses2007.pprocHoursPerYear, "Hours" AS
[Hours Text], "HP Rating From Point Name:" AS [HP Rating From Point Name],
CDbl(Nz([QueryQC_2007-21a-HP]![HP],0)) AS HP, "HP" AS [HP Text], "Process
Fuel Heat Content:" AS [Process Fuel Heat Content],
tblPointProcesses2007.pprocMMBTU, "MMBTU Per Standard Unit" AS [MMBTU Per
Standard Unit], tblPollutants.plutDesc, "Calculation Method: " AS
[Calculation Method], tblInvCodesMethod.methDesc, "Emission Factor: " AS [Ems
Fac Text], tblEmissions2007.emsFactor, tblInvCodesUnits.UNIT_DESCRIPTION,
IIf([emsfactorUnitsNum]=128 And [emsfactorUnits]=128,"N/A","Per") AS Per,
tblInvCodesUnits_1.UNIT_DESCRIPTION, "Primary Control:" AS [Primary Control],
tblInvControlEquipment.ControlDevice, "Efficiency:" AS Efficiency,
[emsControl1Efficiency]*100 AS [Control1 Percent], "%" AS [Percent],
"Secondary Control:" AS [Secondary Control],
tblInvControlEquipment_1.ControlDevice, "Efficiency:" AS Efficiency2,
[emsControl2Efficiency]*100 AS [Control2 Percent], "%" AS Percent2, "Reported
Emissions:" AS [Reported Ems Text], tblEmissions2007.emsAmountreported AS
Emissions, "Tons" AS Tons2,
Calccheck([emsAmountreported],[HP],[pprocAnnualRate],[pprocMMBTU],[emsFactor],[pprocHoursPerYear],[emsControl1Efficiency],[emsControl2Efficiency],[ThroughputUnitsID],[emsFactorUnitsNum],[emsFactorUnits])
AS [Calculated Result], "Point Notes: " AS PntNotesText,
tblPoints2007.pntNotes, "Facility Notes: " AS FacNotesText,
facility.facMemoToStaff

FROM (((((((((((tblInvCodesUnits AS tblInvCodesUnits_1 INNER JOIN
((((facility LEFT JOIN company ON facility.comp_id = company.comp_id) INNER
JOIN tblPoints2007 ON facility.fac_id = tblPoints2007.pntFacId) INNER JOIN
(tblPointProcesses2007 INNER JOIN tblEmissions2007 ON
tblPointProcesses2007.pprocId = tblEmissions2007.emsProcId) ON
tblPoints2007.pntId = tblPointProcesses2007.pprocPntId) INNER JOIN
tblPollutants ON tblEmissions2007.emsPlutId = tblPollutants.plutId) ON
tblInvCodesUnits_1.UnitId = tblEmissions2007.emsFactorUnits) INNER JOIN
tblSCC ON tblPointProcesses2007.pprocSccId = tblSCC.SCC) LEFT JOIN
TblReportingfacilities_deduped_CERR_TypeB ON facility.fac_id =
TblReportingfacilities_deduped_CERR_TypeB.ReportingfacId) LEFT JOIN
airs_codes ON facility.airs_classification_code = airs_codes.airs_code_id)
LEFT JOIN tblInvCodesMethod ON tblEmissions2007.emsEstimatedMethodId =
tblInvCodesMethod.methId) LEFT JOIN TblReportingfacilities_deduped_CERR_TypeA
ON facility.fac_id =
TblReportingfacilities_deduped_CERR_TypeA.ReportingFacId) INNER JOIN
[QueryQC_2007-TotalFacilityEmissions_smh] ON tblPoints2007.pntId =
[QueryQC_2007-TotalFacilityEmissions_smh].pntId) LEFT JOIN
[QueryQC_2007-21a-HP] ON tblPoints2007.pntId = [QueryQC_2007-21a-HP].pntId)
INNER JOIN tblInvCodesUnits AS tblInvCodesUnits_2 ON
tblPointProcesses2007.ThroughputUnitsID = tblInvCodesUnits_2.UnitId) INNER
JOIN tblInvCodesUnits ON tblEmissions2007.emsFactorUnitsNum =
tblInvCodesUnits.UnitId) INNER JOIN tblInvControlEquipment ON
tblEmissions2007.ControlDevice1 = tblInvControlEquipment.ID) INNER JOIN
tblInvControlEquipment AS tblInvControlEquipment_1 ON
tblEmissions2007.ControlDevice2 = tblInvControlEquipment_1.ID;

Everything works fine until I try to use the criteria on that field either
for filtering or sorting, then I get a data type mismatch error. I'm using
Access 2007. Casting to CStr() has no effect.

Thanks in advance.
Sharon
Click to expand...
Click to expand...
Click to expand...
 
Top