type conversion

[John Sh]

I have three fields, all are numeric, double and general number with no locks or validation rules and no nulls.
field1 contains integers from 1 to 2000.
filed2 contains decimals from 0.1 to 0.6.
field3 is empty.
I want to finish up with 100.2 etc. Fields 1 & 2 will be discarded after the update.
I run an update query as " field3:[field1]+[field2]" and get an error message saying "fields didn't update due to a conversion error".
Field3 remains empty!
Your thoughts please.

 

[arnelgp]

on design view of your table, make field3 as numeric, double.

 

[John Sh]

Thanks but all three are, as stated above, numeric, double. I can see no conflict to cause this error.
To simplify things I will take the table into Excel and do the additions there.

 

[arnelgp]

what is your SQL update statement?

Update yourTable Set [Field3] = [Field1] + [Field2];

 

[John Sh]

Thank you. I had field3 mentioned twice. I took one out and we're away.

 

[The_Doc_Man]

field3:[field1]+[field2]

As you discovered and correctly revealed, you named field3 twice. That colon syntax creates an ALIAS named field3, but you already have another (explicit) field of the same name. You knew that but I'm coming back to explain it for the folks who are new to Access.

I'm also not going to tell you why you got that "type mismatch" error message - because when Access issues error messages due to being confused, it sometimes picks an error we wouldn't have guessed. You SHOULD have gotten a name duplication, but the query analysis code works in mysterious ways.

 

[John Sh]

Being, myself, reasonably new to access I find many of the descriptions of how things work to be basic at best. I am not at all familiar with SQL but seeing your explanation in SQL gave me the clue as to what was wrong. Doing it in design view is easier when it works but can be somewhat confusing when things don't quite go to plan.,
Thanks to all for setting me straight, again...

And to any other newbies out there, there is a wealth of information readily available on this forum so don't be afraid to ask.