data type mismatch in criteria expression

[Guest]

In the following query, I am getting the "data type mismatch in criteria
expression" error. It's kind of weird, because the datasheet view appears to
be normal behind the error popup, but after I click ok all the fields change
to #Name. Here is the query:

SELECT qryJobs.JOB_NO, qryJobs.[PURCHASE ORDER NUMBER], qryJobs.PNFixed,
[PART MASTER].[REF #], [PART MASTER].[PART DESCRIPTION], [PART
MASTER].MATERIAL, [PART MASTER].Finish, [PART MASTER].HeatTreat
FROM qryJobs INNER JOIN [PART MASTER] ON (qryJobs.CUSTOMER = [PART
MASTER].CUSTOMER) AND (qryJobs.PNFixed = [PART MASTER].PART_NO);

One confession (which is probably relevant): There is a relationship
between the PART_NO field in the table upon which qryJobs is based and the
PART_NO field in the [PART MASTER] table. I am actually trying to circumvent
this in this query by instead relating the qryJobs.PNFixed and [PART
MASTER].PART_NO fields. I'm guessing that this is the source of my problem.
I was hoping to avoid using a bunch of DLookups because they perform so
slowly on larger tables/queries. Does anyone have any advice?

Thanks,

Keith

 

[Jeff Boyce]

Keith

"data type mismatch" suggests that two fields don't have the same data type.
What data types are your criteria in their underlying tables?

Could you use one of the "convert" functions (e.g., CCur(), CInt(), ...) to
coerce one of your fields to match up?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Jeff,

All of the data fields in question are of type text . The field PNFixed is
the result of a function with a string as the return, so I don't think that's
the problem. Any other thoughts?

Thanks,

Keith

Keith

"data type mismatch" suggests that two fields don't have the same data type.
What data types are your criteria in their underlying tables?

Could you use one of the "convert" functions (e.g., CCur(), CInt(), ...) to
coerce one of your fields to match up?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

In the following query, I am getting the "data type mismatch in criteria
expression" error. It's kind of weird, because the datasheet view appears to
be normal behind the error popup, but after I click ok all the fields change
to #Name. Here is the query:

SELECT qryJobs.JOB_NO, qryJobs.[PURCHASE ORDER NUMBER], qryJobs.PNFixed,
[PART MASTER].[REF #], [PART MASTER].[PART DESCRIPTION], [PART
MASTER].MATERIAL, [PART MASTER].Finish, [PART MASTER].HeatTreat
FROM qryJobs INNER JOIN [PART MASTER] ON (qryJobs.CUSTOMER = [PART
MASTER].CUSTOMER) AND (qryJobs.PNFixed = [PART MASTER].PART_NO);

One confession (which is probably relevant): There is a relationship
between the PART_NO field in the table upon which qryJobs is based and the
PART_NO field in the [PART MASTER] table. I am actually trying to circumvent
this in this query by instead relating the qryJobs.PNFixed and [PART
MASTER].PART_NO fields. I'm guessing that this is the source of my problem.
I was hoping to avoid using a bunch of DLookups because they perform so
slowly on larger tables/queries. Does anyone have any advice?

Thanks,

Keith

 

[OfficeDev18 via AccessMonster.com]

Hi, Keith,

How large is your database? If you're using A1997, you're only allowed 1 gig;
later versions (I'm not sure which version this started) allow 2 gig. If
you're almost at that point, try compacting your database and running the
query again. The syntax seems alright.

Sam

Jeff,

All of the data fields in question are of type text . The field PNFixed is
the result of a function with a string as the return, so I don't think that's
the problem. Any other thoughts?

Thanks,

Keith

Keith

[quoted text clipped - 30 lines]

 

[Guest]

My database backend is about 40 MB. I tried compacting anyway, but that
didn't resolve it either. BTW, I am running Access 2000 if that's relevant.
Somehow I think I've got some data anomoly somewhere. Any other ideas?

Thanks,

Keith

Hi, Keith,

How large is your database? If you're using A1997, you're only allowed 1 gig;
later versions (I'm not sure which version this started) allow 2 gig. If
you're almost at that point, try compacting your database and running the
query again. The syntax seems alright.

Sam

Jeff,

All of the data fields in question are of type text . The field PNFixed is
the result of a function with a string as the return, so I don't think that's
the problem. Any other thoughts?

Thanks,

Keith

Keith

[quoted text clipped - 30 lines]

Keith