error "Type mismatch in expression" when trying to run a query

A

Amy Schmid

The query is based on two main tables. On table has two smaller tables that
are linked. The other table has just one table linked. I have the
relationships established and have inputed data in the tables. I cannot get
the query to run as I get the error message. I have tried searching the help
index and cannot get a grip on what to do here. I am sing Access 2000.
 
K

KARL DEWEY

It would be a great help to us non-physic types it you posted the SQL of your
query. Open the query in design view, click on menu VIEW - SQL View,
highlight all, copy, and paste in a post.
 
A

Amy Schmid

Sorry about that. I am just learning Access and having to kinda wing it . . .

SELECT dbo_Clients.Cltnum, dbo_Clients.Cltname,
dbo_ajs_Ag_FS_Section.FSSection, dbo_ajs_Ag_FS_Detail.FSDetailName,
dbo_ajs_Ag_FS_Data.FSYear, dbo_ajs_Ag_FS_Data.FSAmount,
dbo_ajs_Ag_NFS_Data.NFSYear, dbo_ajs_Ag_NFS_Data.[DCQtr12-31PY],
dbo_ajs_Ag_NFS_Data.[DCQtr3-31], dbo_ajs_Ag_NFS_Data.[DCQtr6-30],
dbo_ajs_Ag_NFS_Data.[DCQtr9-30], dbo_ajs_Ag_NFS_Data.[DCQtr12-31],
dbo_ajs_Ag_NFS_Data.[MCQtr12-31PY], dbo_ajs_Ag_NFS_Data.[MCQtr3-31],
dbo_ajs_Ag_NFS_Data.[MCQtr6-30], dbo_ajs_Ag_NFS_Data.[MCQtr9-30],
dbo_ajs_Ag_NFS_Data.[MCQtr12-31], dbo_ajs_Ag_NFS_Data.CWTMilk,
dbo_ajs_Ag_NFS_Data.OwnHrs, dbo_ajs_Ag_NFS_Data.EmpHrs,
dbo_ajs_Ag_NFS_Data.MlkPr
FROM (dbo_Clients INNER JOIN (dbo_ajs_Ag_FS_Section INNER JOIN
(dbo_ajs_Ag_FS_Data INNER JOIN dbo_ajs_Ag_FS_Detail ON
dbo_ajs_Ag_FS_Data.FSDetailID = dbo_ajs_Ag_FS_Detail.FSDetailID) ON
dbo_ajs_Ag_FS_Section.FSSectionID = dbo_ajs_Ag_FS_Detail.SectionID) ON
dbo_Clients.Cltnum = dbo_ajs_Ag_FS_Data.Cltnum) INNER JOIN
(dbo_ajs_Ag_NFS_MilkPrice INNER JOIN dbo_ajs_Ag_NFS_Data ON
dbo_ajs_Ag_NFS_MilkPrice.MilkPrice = dbo_ajs_Ag_NFS_Data.MlkPr) ON
dbo_Clients.Cltnum = dbo_ajs_Ag_NFS_Data.Cltnum;
 
K

KARL DEWEY

This is a guess but I would check dbo_ajs_Ag_NFS_MilkPrice.MilkPrice and
dbo_ajs_Ag_NFS_Data.MlkPr to see if the fields are the same data type -
Identical number type.
If they are then check the data type of each join --
dbo_ajs_Ag_FS_Data.FSDetailID = dbo_ajs_Ag_FS_Detail.FSDetailID
dbo_ajs_Ag_FS_Section.FSSectionID = dbo_ajs_Ag_FS_Detail.SectionID
dbo_Clients.Cltnum = dbo_ajs_Ag_FS_Data.Cltnum
dbo_Clients.Cltnum = dbo_ajs_Ag_NFS_Data.Cltnum
dbo_ajs_Ag_NFS_MilkPrice.MilkPrice = dbo_ajs_Ag_NFS_Data.MlkPr

--
KARL DEWEY
Build a little - Test a little


Amy Schmid said:
Sorry about that. I am just learning Access and having to kinda wing it . . .

SELECT dbo_Clients.Cltnum, dbo_Clients.Cltname,
dbo_ajs_Ag_FS_Section.FSSection, dbo_ajs_Ag_FS_Detail.FSDetailName,
dbo_ajs_Ag_FS_Data.FSYear, dbo_ajs_Ag_FS_Data.FSAmount,
dbo_ajs_Ag_NFS_Data.NFSYear, dbo_ajs_Ag_NFS_Data.[DCQtr12-31PY],
dbo_ajs_Ag_NFS_Data.[DCQtr3-31], dbo_ajs_Ag_NFS_Data.[DCQtr6-30],
dbo_ajs_Ag_NFS_Data.[DCQtr9-30], dbo_ajs_Ag_NFS_Data.[DCQtr12-31],
dbo_ajs_Ag_NFS_Data.[MCQtr12-31PY], dbo_ajs_Ag_NFS_Data.[MCQtr3-31],
dbo_ajs_Ag_NFS_Data.[MCQtr6-30], dbo_ajs_Ag_NFS_Data.[MCQtr9-30],
dbo_ajs_Ag_NFS_Data.[MCQtr12-31], dbo_ajs_Ag_NFS_Data.CWTMilk,
dbo_ajs_Ag_NFS_Data.OwnHrs, dbo_ajs_Ag_NFS_Data.EmpHrs,
dbo_ajs_Ag_NFS_Data.MlkPr
FROM (dbo_Clients INNER JOIN (dbo_ajs_Ag_FS_Section INNER JOIN
(dbo_ajs_Ag_FS_Data INNER JOIN dbo_ajs_Ag_FS_Detail ON
dbo_ajs_Ag_FS_Data.FSDetailID = dbo_ajs_Ag_FS_Detail.FSDetailID) ON
dbo_ajs_Ag_FS_Section.FSSectionID = dbo_ajs_Ag_FS_Detail.SectionID) ON
dbo_Clients.Cltnum = dbo_ajs_Ag_FS_Data.Cltnum) INNER JOIN
(dbo_ajs_Ag_NFS_MilkPrice INNER JOIN dbo_ajs_Ag_NFS_Data ON
dbo_ajs_Ag_NFS_MilkPrice.MilkPrice = dbo_ajs_Ag_NFS_Data.MlkPr) ON
dbo_Clients.Cltnum = dbo_ajs_Ag_NFS_Data.Cltnum;


KARL DEWEY said:
It would be a great help to us non-physic types it you posted the SQL of your
query. Open the query in design view, click on menu VIEW - SQL View,
highlight all, copy, and paste in a post.
Click to expand...
Click to expand...
 
G

Golfinray

I have found that most of the time when you get that error you have one field
in your set to date/time or text or currency when it it should be set to
another type. Or you have one field in one table set to a type and the same
field in another table set to a different type. Go to design view on your
tables and check.
 
A

Amy Schmid

Thanks for your help.

I fixed the data types for dbo_ajs_Ag_NFS_MilkPrice.MilkPrice and
dbo_ajs_Ag_NFS_Data.MlkPr. They had not been identical. That required
removing the lookup wizard that was attached to
dbo_ajs_Ag_NFS_MilkPrice.MilkPrice. Is there a way to have
dbo_ajs_Ag_NFS_Data.MlkPr look to dbo_ajs_Ag_NFS_MilkPrice.MilkPrice and
bring in the data as a dollar amount?

I checked the others and they are matching now too but I still get the error
message.

Amy

KARL DEWEY said:
This is a guess but I would check dbo_ajs_Ag_NFS_MilkPrice.MilkPrice and
dbo_ajs_Ag_NFS_Data.MlkPr to see if the fields are the same data type -
Identical number type.
If they are then check the data type of each join --
dbo_ajs_Ag_FS_Data.FSDetailID = dbo_ajs_Ag_FS_Detail.FSDetailID
dbo_ajs_Ag_FS_Section.FSSectionID = dbo_ajs_Ag_FS_Detail.SectionID
dbo_Clients.Cltnum = dbo_ajs_Ag_FS_Data.Cltnum
dbo_Clients.Cltnum = dbo_ajs_Ag_NFS_Data.Cltnum
dbo_ajs_Ag_NFS_MilkPrice.MilkPrice = dbo_ajs_Ag_NFS_Data.MlkPr

--
KARL DEWEY
Build a little - Test a little


Amy Schmid said:
Sorry about that. I am just learning Access and having to kinda wing it . . .

SELECT dbo_Clients.Cltnum, dbo_Clients.Cltname,
dbo_ajs_Ag_FS_Section.FSSection, dbo_ajs_Ag_FS_Detail.FSDetailName,
dbo_ajs_Ag_FS_Data.FSYear, dbo_ajs_Ag_FS_Data.FSAmount,
dbo_ajs_Ag_NFS_Data.NFSYear, dbo_ajs_Ag_NFS_Data.[DCQtr12-31PY],
dbo_ajs_Ag_NFS_Data.[DCQtr3-31], dbo_ajs_Ag_NFS_Data.[DCQtr6-30],
dbo_ajs_Ag_NFS_Data.[DCQtr9-30], dbo_ajs_Ag_NFS_Data.[DCQtr12-31],
dbo_ajs_Ag_NFS_Data.[MCQtr12-31PY], dbo_ajs_Ag_NFS_Data.[MCQtr3-31],
dbo_ajs_Ag_NFS_Data.[MCQtr6-30], dbo_ajs_Ag_NFS_Data.[MCQtr9-30],
dbo_ajs_Ag_NFS_Data.[MCQtr12-31], dbo_ajs_Ag_NFS_Data.CWTMilk,
dbo_ajs_Ag_NFS_Data.OwnHrs, dbo_ajs_Ag_NFS_Data.EmpHrs,
dbo_ajs_Ag_NFS_Data.MlkPr
FROM (dbo_Clients INNER JOIN (dbo_ajs_Ag_FS_Section INNER JOIN
(dbo_ajs_Ag_FS_Data INNER JOIN dbo_ajs_Ag_FS_Detail ON
dbo_ajs_Ag_FS_Data.FSDetailID = dbo_ajs_Ag_FS_Detail.FSDetailID) ON
dbo_ajs_Ag_FS_Section.FSSectionID = dbo_ajs_Ag_FS_Detail.SectionID) ON
dbo_Clients.Cltnum = dbo_ajs_Ag_FS_Data.Cltnum) INNER JOIN
(dbo_ajs_Ag_NFS_MilkPrice INNER JOIN dbo_ajs_Ag_NFS_Data ON
dbo_ajs_Ag_NFS_MilkPrice.MilkPrice = dbo_ajs_Ag_NFS_Data.MlkPr) ON
dbo_Clients.Cltnum = dbo_ajs_Ag_NFS_Data.Cltnum;


KARL DEWEY said:
It would be a great help to us non-physic types it you posted the SQL of your
query. Open the query in design view, click on menu VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

The query is based on two main tables. On table has two smaller tables that
are linked. The other table has just one table linked. I have the
relationships established and have inputed data in the tables. I cannot get
the query to run as I get the error message. I have tried searching the help
index and cannot get a grip on what to do here. I am sing Access 2000.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I suggest making a copy and then start removing pieces and trying it until it
runs. That will tell you where the problem is.
--
KARL DEWEY
Build a little - Test a little


Amy Schmid said:
Thanks for your help.

I fixed the data types for dbo_ajs_Ag_NFS_MilkPrice.MilkPrice and
dbo_ajs_Ag_NFS_Data.MlkPr. They had not been identical. That required
removing the lookup wizard that was attached to
dbo_ajs_Ag_NFS_MilkPrice.MilkPrice. Is there a way to have
dbo_ajs_Ag_NFS_Data.MlkPr look to dbo_ajs_Ag_NFS_MilkPrice.MilkPrice and
bring in the data as a dollar amount?

I checked the others and they are matching now too but I still get the error
message.

Amy

KARL DEWEY said:
This is a guess but I would check dbo_ajs_Ag_NFS_MilkPrice.MilkPrice and
dbo_ajs_Ag_NFS_Data.MlkPr to see if the fields are the same data type -
Identical number type.
If they are then check the data type of each join --
dbo_ajs_Ag_FS_Data.FSDetailID = dbo_ajs_Ag_FS_Detail.FSDetailID
dbo_ajs_Ag_FS_Section.FSSectionID = dbo_ajs_Ag_FS_Detail.SectionID
dbo_Clients.Cltnum = dbo_ajs_Ag_FS_Data.Cltnum
dbo_Clients.Cltnum = dbo_ajs_Ag_NFS_Data.Cltnum
dbo_ajs_Ag_NFS_MilkPrice.MilkPrice = dbo_ajs_Ag_NFS_Data.MlkPr

--
KARL DEWEY
Build a little - Test a little


Amy Schmid said:
Sorry about that. I am just learning Access and having to kinda wing it . . .

SELECT dbo_Clients.Cltnum, dbo_Clients.Cltname,
dbo_ajs_Ag_FS_Section.FSSection, dbo_ajs_Ag_FS_Detail.FSDetailName,
dbo_ajs_Ag_FS_Data.FSYear, dbo_ajs_Ag_FS_Data.FSAmount,
dbo_ajs_Ag_NFS_Data.NFSYear, dbo_ajs_Ag_NFS_Data.[DCQtr12-31PY],
dbo_ajs_Ag_NFS_Data.[DCQtr3-31], dbo_ajs_Ag_NFS_Data.[DCQtr6-30],
dbo_ajs_Ag_NFS_Data.[DCQtr9-30], dbo_ajs_Ag_NFS_Data.[DCQtr12-31],
dbo_ajs_Ag_NFS_Data.[MCQtr12-31PY], dbo_ajs_Ag_NFS_Data.[MCQtr3-31],
dbo_ajs_Ag_NFS_Data.[MCQtr6-30], dbo_ajs_Ag_NFS_Data.[MCQtr9-30],
dbo_ajs_Ag_NFS_Data.[MCQtr12-31], dbo_ajs_Ag_NFS_Data.CWTMilk,
dbo_ajs_Ag_NFS_Data.OwnHrs, dbo_ajs_Ag_NFS_Data.EmpHrs,
dbo_ajs_Ag_NFS_Data.MlkPr
FROM (dbo_Clients INNER JOIN (dbo_ajs_Ag_FS_Section INNER JOIN
(dbo_ajs_Ag_FS_Data INNER JOIN dbo_ajs_Ag_FS_Detail ON
dbo_ajs_Ag_FS_Data.FSDetailID = dbo_ajs_Ag_FS_Detail.FSDetailID) ON
dbo_ajs_Ag_FS_Section.FSSectionID = dbo_ajs_Ag_FS_Detail.SectionID) ON
dbo_Clients.Cltnum = dbo_ajs_Ag_FS_Data.Cltnum) INNER JOIN
(dbo_ajs_Ag_NFS_MilkPrice INNER JOIN dbo_ajs_Ag_NFS_Data ON
dbo_ajs_Ag_NFS_MilkPrice.MilkPrice = dbo_ajs_Ag_NFS_Data.MlkPr) ON
dbo_Clients.Cltnum = dbo_ajs_Ag_NFS_Data.Cltnum;


:

It would be a great help to us non-physic types it you posted the SQL of your
query. Open the query in design view, click on menu VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

The query is based on two main tables. On table has two smaller tables that
are linked. The other table has just one table linked. I have the
relationships established and have inputed data in the tables. I cannot get
the query to run as I get the error message. I have tried searching the help
index and cannot get a grip on what to do here. I am sing Access 2000.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Number to Number "Data Type Mismatch in Expression" in Join 1
Type Mismatch in Expression 1
Error: Data type mismatch in criteria expression 0
Type mismatch in JOIN expression. (Error 3615) Join data stored asText and Number 4
Type Mismatch in Expression 4
Type Mismatch in Expression Error 1
Type Mismatch Error depends on relation scheme appearance 3
data type mismatch in criteria expression when no criteria specified 1

Top