Data type correct for some records, not for others

I have a one-to-many relationship set up between practiceType.id and mentors.practiceTypeID in the Relationships window in Access-- is that what you mean? When I open the practiceType table and expand a record, it shows both new and old records under the correct practice types. I have looked extensively at any possible differences between the new and old records, and there are none! I really don't know what to do here. I know that my queries need work, but they used to do what they were supposed to do, and now they don't! Thank you again for all of your help...

Thanks for all of your replies! I know that the queries are messy right now, but what confuses me is that the records that were present before I added new records with an append query display perfectly, while I get the above error ONLY with the records that were added with the append query in Access. There is always a value for practiceTypeID, and it works correctly with older records! I can't figure it out. The field types are exactly the same, and even if I go into the database and retype in the practiceTypeID in the mentors table in one of the new records, it still throws the error.

Quick update... if I enter a new record through the online form, the record displays correctly, with the PracticeTypeID and all. If I enter it manually into the database, the error pops up. Why would it be different entering it manually or through the web? It's just a number field, Long Integer type.

you forgot to put ## around mentors.practiceTypeID in your query.
it should be:

WHERE
mentors.id = #id# and internOps_new.mentorID = #id# and practiceType.id
= #mentors.practiceTypeID#

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

semi star gazer wrote:
> I have attached the error and the query, but my big question is,
> why do some records work and others do not?

There are a number of potential problems with those queries.

1) Does the "practiceTypeID" column always have a value? Empty or null values would likely cause a syntax error in the second query.

2) A column named "id" exists in both the mentors and practiceType table. Because you are using SELECT mentors.*, (ie select all columns), the query results contain two columns with the same name: "id". That may is cause issues.

Use a column list instead, and eliminate any duplicate column names by using aliases. Aliases let you assign a different name to an object within a query.

SELECT

mentors.id,
practiceType.id AS PracticeTypeID, <!--- use an alias to rename the column "PracticeTypeID" --->
... other columns ...

FROM mentors,
internOps_new,
practiceType,
practiceArea
WHERE
.... other conditions ...

3) Your query appears to be missing some join conditions. There are four (4) tables in the FROM clause but only three (3) conditions in your WHERE clause. So the query may return too many records. How are the tables related?

> getPracticeID

4) Since you are already retrieving "practiceTypeID" in the first query, what is the purpose of the "getPracticeID" query?