Data type mismatch in crosstab 1

[wvandenberg]

Hi all,

I have a crosstab query

TRANSFORM Max(IIf([P_GroupID]=7,IIf([Symbol]=">",Replace([ResultCALC],"-",[Symbol],1,1),Val([ResultCALC])),Val([ResultCALC]))) AS Result
SELECT qselWizExpResults2Sort.P_Analyte AS Analyte, qselWizExpResults2Sort.P_Group AS [Group], qselWizExpResults2Sort.Unit
FROM qselWizExpResults2Sort
GROUP BY qselWizExpResults2Sort.P_Analyte, qselWizExpResults2Sort.P_Group, qselWizExpResults2Sort.Unit, qselWizExpResults2Sort.GroupOrder, qselWizExpResults2Sort.AnalyteOrder
ORDER BY qselWizExpResults2Sort.GroupOrder, qselWizExpResults2Sort.AnalyteOrder, [SiteName] & "  " & Format([StartDate],"dd-mmm-yyyy")
PIVOT [SiteName] & "  " & Format([StartDate],"dd-mmm-yyyy");

When I try and open it, sometimes I get the error "Data type mismatch in criteria expression". I'm pretty sure the error occurs when the highlighted expressions evaluate to TRUE and the Val([ResultCALC]) function has to run.

IIf([highlight][P_GroupID]=7[/highlight],IIf(([highlight][Symbol]=">"[/highlight],(Replace([ResultCALC],"-",[Symbol],1,1),Val([ResultCALC])),Val([ResultCALC])))

So, I changed the expression to

IIf[P_GroupID]=7,IIf(([Symbol]=">",(Replace([ResultCALC],"-",[Symbol],1,1),CStr([ResultCALC])),Val([ResultCALC])))

However, then the Max() function doesn't work properly, I think because it's taking the max of text rather than numbers. Could anyone shed some light on this dilemma. Should I make a custom Max() function? If so, any suggestions?

Thanks in advance,
Wendy

 

[lameid]

I guess I have three questions...

What does your data look like?

What are you trying to accomplish? - What are you wanting Max() to do?

What is the SQL behind qselWizExpResults2Sort? (I am assuming it is a query, if not what are the data types of the field used)?

 

[wvandenberg]

1. The data in [ResultCALC] are all data type double and can be either positive or negative. When the value is negative, it allows for a format to be applied in XL after the data are exported. Negative numbers are used to indicate "<" For example -0.0005 would be formatted in XL to display as <0.0005. The crux of the problem is that for some of the data ([P_GroupID]=7) , the "-" can indicate either "<" or ">". Here is a small subset of the data in the crosstab:
[tt]
Analyte Group Unit WQ9 27-Sep-2005
Biochemical Oxygen Demand Biological Oxygen Demand mg/L -2
Naphthenic acids General Organics mg/L -1
Total Phenolics General Organics mg/L 0.009
Total Recoverable Hydrocarbons General Organics mg/L -0.5
Algal Growth Inhibition Test (72 h) - NOEC Toxicity % 100
Ceriodaphnia 7 d Mortality Test - LC25 Toxicity % -100
Ceriodaphnia 7 d Mortality Test - LOEC Toxicity % -100
Ceriodaphnia 7 d Mortality Test - NOEC Toxicity % 100
Ceriodaphnia 7 d Reproduction Test - IC25 Toxicity % 39[/tt]

2. For a couple analytes, there can be two records (different analysis methods) and I need to output the Max value.

3. The sql for qselWizExpResults2Sort

SELECT qselWizExpResults1Select.*, IIf([UnitsID]<>[fkReportingUnitsID],ConvertUnits([ResultValue],[UnitsID]),[ResultValue]) AS ResultCALC, IIf([P_GroupID]=7,IIf([Symbol]=">",Replace([ResultCALC],"-",[Symbol],1,1),([ResultCALC]))) AS ResultTOX, IIf(IsNull([ResultTOX]),[ResultCALC],[ResultTOX]) AS ResultFINAL, IIf([UnitsID]<>[fkReportingUnitsID] And [ResultCALC]=[ResultValue],[Units],[ReportingUnitsDescription]) AS Unit, tblGroupedAnalytes.GroupOrder, tblGroupedAnalytes.AnalyteOrder, tblGroupedAnalytes.Package, qselWizExpResults4Rank.Rank
FROM qselWizExpResults4Rank INNER JOIN (tblWizExpAnalyteData INNER JOIN (qselWizExpResults1Select INNER JOIN (tblGroupedAnalytes INNER JOIN tlkpReportingUnits ON tblGroupedAnalytes.fkReportingUnitsID = tlkpReportingUnits.pkReportingUnitsID) ON (qselWizExpResults1Select.P_GroupID = tblGroupedAnalytes.fkGroupID) AND (qselWizExpResults1Select.P_AnalyteID = tblGroupedAnalytes.fkAnalyteID)) ON (tblWizExpAnalyteData.Analyte = qselWizExpResults1Select.P_AnalyteID) AND (tblWizExpAnalyteData.Group = qselWizExpResults1Select.P_GroupID)) ON qselWizExpResults4Rank.Heading = qselWizExpResults1Select.Heading
WHERE (((tblGroupedAnalytes.Package)=1));

and the sql for qselWizExpResults1Select:

SELECT [SiteName] & "  " & Format([StartDate],"dd-mmm-yyyy") AS Heading, Format([StartDate],"dd-mmm-yyyy") AS SmpDate, tblSites.fkWaterUnitID, tlkpProjects.pkProjectNumberID, tlkpProjects.fkClientID, tblSites.pkSiteID, tblSites.SiteName, tblSites.Easting, tblSites.Northing, tblSamples.pkSampleID, tblSamples.SampleName, tblSamples.StartDate, tblSamples.fkSampleTypeID, IIf(tblAnalytes.StandardGALAnalyte,tblAnalytes.pkAnalyteID,tblAnalytes_1.pkAnalyteID) AS P_AnalyteID, IIf(tblAnalytes.StandardGALAnalyte,tblAnalytes.AnalyteName,tblAnalytes_1.AnalyteName) AS P_Analyte, IIf(tblGroups.StandardGALGroup,tblGroups.pkGroupID,tblGroups_1.pkGroupID) AS P_GroupID, IIf(tblGroups.StandardGALGroup,tblGroups.GroupName,tblGroups_1.GroupName) AS P_Group, tblGroups.GroupName, tblResults.pkResultID, tblResults.DetectionLimit, tblResults.ResultValue, tblResults.Symbol, IIf([tlkpReportingUnits_1].[pkReportingUnitsID],[tlkpReportingUnits_1].[pkReportingUnitsID],[tlkpReportingUnits].[pkReportingUnitsID]) AS UnitsID, IIf([tlkpReportingUnits_1].[ReportingUnitsDescription],[tlkpReportingUnits_1].[ReportingUnitsDescription],[tlkpReportingUnits].[ReportingUnitsDescription]) AS Units
FROM (tlkpReportingUnits LEFT JOIN tlkpReportingUnits AS tlkpReportingUnits_1 ON tlkpReportingUnits.ParentReportingUnitID = tlkpReportingUnits_1.pkReportingUnitsID) INNER JOIN (tlkpProjects INNER JOIN ((tblWizExpSiteData INNER JOIN (tblWizExpWaterUnitData INNER JOIN tblSites ON tblWizExpWaterUnitData.Data = tblSites.fkWaterUnitID) ON tblWizExpSiteData.Data = tblSites.pkSiteID) INNER JOIN ((tblWizExpSampleData INNER JOIN tblSamples ON tblWizExpSampleData.Data = tblSamples.pkSampleID) INNER JOIN ((tblGroups LEFT JOIN tblGroups AS tblGroups_1 ON tblGroups.ParentGroupID = tblGroups_1.pkGroupID) INNER JOIN ((tblAnalytes LEFT JOIN tblAnalytes AS tblAnalytes_1 ON tblAnalytes.ParentAnalyteID = tblAnalytes_1.pkAnalyteID) INNER JOIN tblResults ON tblAnalytes.pkAnalyteID = tblResults.fkAnalyteID) ON tblGroups.pkGroupID = tblResults.fkGroupID) ON tblSamples.pkSampleID = tblResults.fkSampleID) ON tblSites.pkSiteID = tblSamples.fkSiteID) ON tlkpProjects.pkProjectNumberID = tblSamples.fkProjectNumberID) ON tlkpReportingUnits.pkReportingUnitsID = tblResults.fkReportingUnitsID
WHERE (((tlkpProjects.pkProjectNumberID) Not In (61,62,63,64,65,66,67,68)));

The data type for the results field is double for all queries and the underlying table. It's just when the dataset has P_Group=7 (Group=Toxicity) and the iif statement evaluates to TRUE that the data seems to be converted to text.

 

[lameid]

Now I am following what you are doing. Yes < and > are not numeric so you end up with text and cannot take the Max of those fields if you want the number max.

Nothing is jumping out at me. The catch is you want the maximum value of the number outside of the grouping needed to evaluate it.

Anyways, I'm running to lunch now... Maybe if this stews awhile a solution will come to me.

 

[lameid]

I didn't see the grouping on qselWizExpResults2Sort.P_Group in the crosstab. Still not grouping on Symbol is a problem. If you were able to then moving the max function would work...

TRANSFORM (IIf([P_GroupID]=7,IIf([Symbol]=">",Replace(Max([ResultCALC]),"-",[Symbol],1,1),Val(Max([ResultCALC]))),Val(Max([ResultCALC])))) AS Result

I don't know if that helps at all or not. I think this is a back to the drawing board kind of problem.

The next step if you can't group as I mentioned above (obviously for logical reasons only) then the next logical step is to look at the function ConvertUnits and see if there is anyway to rearrange or reorder the logic from end to end (ConvertUnits and queries) so that this is not an issue.

 

[wvandenberg]

Sorry for the late reply.

I thought for sure it was going to work but alas, sadly it did not :-( I'm pretty sure there is no way to have both double and text data formats in the same column yet treated as double and text where applicable. It just resulted in the same problem as my original post.

Anyway, thanks again.

Wendy

 

[lameid]

The only way I see to maybe hedge this would be to use format to format the number with enough digits that all the data would have the same number of digits and then put the text at the end of the number.

Then when you took the max it would be the largest number. You could then reorder the text and number parts with a function. The catch here is it looks like you are using positive versus negatives to determine the order. You'll have to leave the sign on for this purpose.