update query gives type conversion error for 1435 fields

[TroyB]

I have a simple update query that should update a table field to a value caluculated from a table joined to the one I want to update. When I run it, I get the error in the title: didn't update 1435 field(s) due to a type conversion failure. The fields used in the calculation and the field being updated are all number data types. The query was created in design view, but here is the SQL view:



UPDATE [LogTrip with Landings & Hails W TICKET] INNER JOIN LogDetail ON [LogTrip with Landings & Hails W TICKET].ID = LogDetail.TripID SET LogDetail.proportion_string = 1/[LogTrip with Landings & Hails W TICKET]!CountOfStringNum;

I'm confused as to why it would try to update 1435 fields when I am only asking it to update 1 and there is not 1435 fields to in the table I'm trying to update anyway. I've checked several forums for similar problems, and found some with the same error, but it is always becuase the field they are trying to update doesn't match the data type for the value they are trying to set it to. Since I'm trying to update a number field with a number calculated from another number field, I don't see how this could be the case here.

PS - Don't make fun of my table names, I didn't create them, I know access pros hate blanks and special characters in table and field names.

[TheShabz]

Make sure the Number datatype is set to "Double" and not "Long Integer". 1/something is going to wind up as a decimal. Integers do not handle decimals.

[June7]

It is updating 1435 records because there is no WHERE clause in the sql. Do these tables have a 1 to many relationship?

Why are you saving calculated data anyway? A principle of relational database is to save raw data and calculate on reports.

[TroyB]

Thanks for the thought, but it is set to double not long integer.

[TroyB]

June7 - It's not updating 1435 records but updating 1435 fields. Do you think the WHERE clause could still be the problem? Conceptually, the tables have 1-many relationship, with [LogTrip with Landings & Hails W TICKET] being on the 1 side and [LogDetail] on the many side. However, the relationship is not defined at the database level, the join is just created in the query.

Why save calculated data? I don't know that there is a good reason, I inherited the database. I don't think reports would be useful becuase ultimately the data is exported to more powereful analysis software where the dissagregated data is needed, and I think reports are really for summarizing data. However, I'm sure the same thing could be accomplished with a series of queries that don't update the tables, which would have the same end result of only saving the raw data, and may very well be the better way to go. Right now I'm just trying to understand the behaviour before making any major modifications.

[June7]

It is trying to update a single field for each of 1435 records. I don't understand why you are joining tables but should not be an issue. Yes, need WHERE clause to restrict which records will be updated.

[TroyB]

It is trying to update a single field for each of 1435 records. I don't understand why you are joining tables but should not be an issue. Yes, need WHERE clause to restrict which records will be updated.

Here's a screen capture of the error message. Why would it say can't update 1444 fields if it means 1444 records in 1 field (not sure why the number changed from my original post)? Just a really misleading error message?
Attachment 8761

[TroyB]

Just answered my own question, and IMO it is a really misleading error message because as June7 suggested it was 1444 records in that single field Access couldn't update, not 1444 fields. If anyones interested, the reason it couldn't update those records was becuase the [LogTrip with Landings & Hails W TICKET]!CountOfStringNum field containted 0s, and you can't divide by 0. June7 gets credit for resolving this by putting me on the right track of looking at records instead of fields.

[June7]

The query obviously addresses only one field so can't answer for Microsoft programmers. The error message also states:

Microsoft Access can't update all the records in the update query.

Ouch! I should have thought of possible zero as soon as I saw the division operator.