Query Should run but Data Type Mismatch error

  • Thread starter Thread starter news.paradise.net.nz
  • Start date Start date
N

news.paradise.net.nz

I have been developing access databases for over 5 years.
I have a large database and I have struck this problem with it before
but can find nothing in help or online. Access 2000

I have a query that will run fine without any criteria but as soon as I
add any criteria it gives a "Data type mismatch" error.
As soon as I remove any criteria it runs perfectly.
I know this query is based on another query but I have other processes
based on this query with no issues.
I tried saving this query and basing another query on this one and got
the same error.
I have tried passing back variables from functions and still get a "Data
type mismatch"

eg: the following will run okay with the field b_cg_1 calculating
corectly as a boolean (-1,0)

SELECT qry_010_project_calc_base_forcast.*, CBool(nz([repl_cg_1],21)<20-
[element_base_life]) AS b_cg_1
FROM qry_010_project_calc_base_forcast

If I add the criteria below I get an error
WHERE (CBool(nz([repl_cg_1],21)<20-[element_base_life]))=True

When I run the following type of thing I get an error (typing from
memory)
SELECT qry_010_project_calc_base_forcast.*
FROM qry_010_project_calc_base_forcast
WHERE repl_cg_1 is not null

As an aside this is the database I could not set a breakpoint in VBA to
debug and had to import the entire contents into a new database to
eliminate this error and get breakpoints working again.

I hope someone has struck this issue before and has a fix as I look like
a bit of a noddy saying to my client that I believe I have discovered
and Access error.

TIA, Gavin.
 
Any chance the underlying database has one/more tables with a lookup data
type defined? This could be why the criterion entered doesn't match the
data type of the underlying field.
 
Back
Top