Help with Queries...

  • Thread starter malhyp via AccessMonster.com
  • Start date
M

malhyp via AccessMonster.com

Hi there, I am having big problems with my database. I have created a query
which seemed to work just fine. Recently I had to change the column
properties from numeric to text in some of the tables. Since then when I try
to open a query, i get something like, Type Mismatch in Expression. I re
created the query again still get the same result. Can anyone help??? Also I
have included the sql that it generates.

SELECT tblSpecies.TimberSpecies, tblCategories.CategoryTitle, tblSuppliers.
SupplierName, tblSuppliers.ContactName, tblSuppliers.StreetAddress,
tblSuppliers.Suburb, tblLocation.Location, tblSuppliers.PostCode, tblCountry.
Country, tblSuppliers.PhoneNumber, tblSuppliers.Facsimile, tblSuppliers.
EmailAddress, tblSuppliers.WebsiteAddress, tblSuppliers.Image, tblSuppliers.
ShortDescription, tblSuppliers.Description, tblSuppliers.idSupplier,
tblSuppliers.fHits, tblSuppliers.BusinessType
FROM tblCategories INNER JOIN (tblCountry INNER JOIN (tblLocation INNER JOIN
((tblResults INNER JOIN tblSuppliers ON tblResults.idSupplier = tblSuppliers.
idSupplier) INNER JOIN tblSpecies ON tblResults.idSpecies = tblSpecies.
idSpecies) ON tblLocation.idLocation = tblSuppliers.idLocation) ON tblCountry.
idCountry = tblSuppliers.idCountry) ON tblCategories.idCategory = tblResults.
idCategory;
 
M

malhyp via AccessMonster.com

Also, someone said that you can not join text to numeric in the query. So
what I did was change all the relationships in the query so that they were
text to text and I get no results. SQL below.

SELECT tblSpecies.TimberSpecies, tblCategories.CategoryTitle, tblSuppliers.
SupplierName, tblSuppliers.ContactName, tblSuppliers.StreetAddress,
tblSuppliers.Suburb, tblLocation.Location, tblSuppliers.PostCode, tblCountry.
Country, tblSuppliers.PhoneNumber, tblSuppliers.Facsimile, tblSuppliers.
EmailAddress, tblSuppliers.WebsiteAddress, tblSuppliers.Image, tblSuppliers.
ShortDescription, tblSuppliers.Description, tblSuppliers.idSupplier,
tblSpecies.fHits, tblSuppliers.BusinessType
FROM tblCountry INNER JOIN ((((tblResults INNER JOIN tblSpecies ON tblResults.
idSpecies = tblSpecies.TimberSpecies) INNER JOIN tblSuppliers ON tblResults.
idSupplier = tblSuppliers.SupplierName) INNER JOIN tblCategories ON
tblResults.idCategory = tblCategories.CategoryTitle) INNER JOIN tblLocation
 
G

Guest

Wherever you change a column from numeric to text, if that column is used as
a join column to another table, then the relevant column in the other table
must also be changed to the same data type. I would first go through all the
"ON tbl1.x = tbl2.y" elements of your query and confirm that the data type of
tbl1.x is the same as that of tbl2.y

Jon.
 
M

malhyp via AccessMonster.com

Jon thanks for the reply, I went through all the tables and made sure that
they were all the same format. Now I find that the query, returns no results
at all...

Confused..

Jon said:
Wherever you change a column from numeric to text, if that column is used as
a join column to another table, then the relevant column in the other table
must also be changed to the same data type. I would first go through all the
"ON tbl1.x = tbl2.y" elements of your query and confirm that the data type of
tbl1.x is the same as that of tbl2.y

Jon.
Also, someone said that you can not join text to numeric in the query. So
what I did was change all the relationships in the query so that they were
Click to expand...
[quoted text clipped - 34 lines]
Click to expand...
 
G

Guest

This is good. We've got rid of the data type mismatch problem. What I would
do now is build up the query in small steps. Start with just two tables and
confirm that you get some rows back. Add in a third table and make sure that
you still get data back. Keep adding tables one at a time until it goes
wrong. This will help you pinpoint where the problem is.

Good luck.

malhyp via AccessMonster.com said:
Jon thanks for the reply, I went through all the tables and made sure that
they were all the same format. Now I find that the query, returns no results
at all...

Confused..

Jon said:
Wherever you change a column from numeric to text, if that column is used as
a join column to another table, then the relevant column in the other table
must also be changed to the same data type. I would first go through all the
"ON tbl1.x = tbl2.y" elements of your query and confirm that the data type of
tbl1.x is the same as that of tbl2.y

Jon.
Also, someone said that you can not join text to numeric in the query. So
what I did was change all the relationships in the query so that they were
Click to expand...
[quoted text clipped - 34 lines]
idCountry = tblSuppliers.idCountry) ON tblCategories.idCategory = tblResults.
idCategory;
Click to expand...
Click to expand...
Click to expand...
 
M

malhyp via AccessMonster.com

Jon, thanks for the info. I will give that a ry tonight.

Mally.

Jon said:
This is good. We've got rid of the data type mismatch problem. What I would
do now is build up the query in small steps. Start with just two tables and
confirm that you get some rows back. Add in a third table and make sure that
you still get data back. Keep adding tables one at a time until it goes
wrong. This will help you pinpoint where the problem is.

Good luck.
Jon thanks for the reply, I went through all the tables and made sure that
they were all the same format. Now I find that the query, returns no results
Click to expand...
[quoted text clipped - 15 lines]
Click to expand...
 
Top