Help with Queries...

[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;

 

[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

 

[Jon Ley]

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.

 

[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..

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

[quoted text clipped - 34 lines]

 

[Jon Ley]

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
at all...

Confused..

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

[quoted text clipped - 34 lines]

idCountry = tblSuppliers.idCountry) ON tblCategories.idCategory = tblResults.
idCategory;

 

[malhyp via AccessMonster.com]

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

Mally.

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

[quoted text clipped - 15 lines]