Data Type Mismatch in Criteria Expression

[Gath]

Hi, I have a field where numbers are stored as text. I created another field where I converted text to numbers using the Val function. However, when I try to perform math functions on this new field (e.g. greater than, equal to, etc.), I get this message "Data Type Mismatch in Criteria Expression". Why?

[JoeM]

Can you post the SQL code of your query?

[Gath]

Sure, it's"

SELECT WellConstrSummary.[Location Desc], WellConstrSummary.[Location ID], [Well-SampleID_CrossWalk].[QA/QC], UnionAll_LDC.ANALYTE, UnionAll_LDC.ANALYTE_VALUE, UnionAll_LDC.FINAL_QUALIFIER, Val([ANALYTE_VALUE]) AS Expr1
FROM ((UnionAll_LDC INNER JOIN [Well-SampleID_CrossWalk] ON UnionAll_LDC.SAMPLE_NAME = [Well-SampleID_CrossWalk].SampleID) INNER JOIN [AnalyteMethod-Group_CrossWalk] ON UnionAll_LDC.ANALYTICAL_METHOD = [AnalyteMethod-Group_CrossWalk].ANALYTICAL_METHOD) INNER JOIN WellConstrSummary ON [Well-SampleID_CrossWalk].[Well ID] = WellConstrSummary.[Well ID]
WHERE (((WellConstrSummary.[Location Desc]) Like "*First Sand*") AND (([Well-SampleID_CrossWalk].[QA/QC])="Duplicate" Or ([Well-SampleID_CrossWalk].[QA/QC])="ms/msd" Or ([Well-SampleID_CrossWalk].[QA/QC]) Is Null) AND ((UnionAll_LDC.ANALYTE)="vinyl chloride") AND ((Val([ANALYTE_VALUE]))>5) AND ((UnionAll_LDC.REPORTABLE_RESULT)="y"));

[lfpm062010]

Did you create the field in a table or in a query?

If it is in a table, what's the data type of the new field? Text or Number???

If it is in a query, can you post the SQL?

[Gath]

I created the field in a query. How should I post the SQL? You mean the SQL code posted above?

[lfpm062010]

Yes, timing is everything.

I don't have the structure to test. But this might be your problem.


SELECT WellConstrSummary.[Location Desc], WellConstrSummary.[Location ID], [Well-SampleID_CrossWalk].[QA/QC], UnionAll_LDC.ANALYTE, UnionAll_LDC.ANALYTE_VALUE, UnionAll_LDC.FINAL_QUALIFIER, Val(UnionAll_LDC.ANALYTE_VALUE) AS Expr1
FROM ((UnionAll_LDC INNER JOIN [Well-SampleID_CrossWalk] ON UnionAll_LDC.SAMPLE_NAME = [Well-SampleID_CrossWalk].SampleID) INNER JOIN [AnalyteMethod-Group_CrossWalk] ON UnionAll_LDC.ANALYTICAL_METHOD = [AnalyteMethod-Group_CrossWalk].ANALYTICAL_METHOD) INNER JOIN WellConstrSummary ON [Well-SampleID_CrossWalk].[Well ID] = WellConstrSummary.[Well ID]
WHERE (((WellConstrSummary.[Location Desc]) Like "*First Sand*") AND (([Well-SampleID_CrossWalk].[QA/QC])="Duplicate" Or ([Well-SampleID_CrossWalk].[QA/QC])="ms/msd" Or ([Well-SampleID_CrossWalk].[QA/QC]) Is Null) AND ((UnionAll_LDC.ANALYTE)="vinyl chloride") AND ((Val(UnionAll_LDC.ANALYTE_VALUE)))>5) AND ((UnionAll_LDC.REPORTABLE_RESULT)="y"));