Error message on update query

[wilkob]

hello

I am trying to run an update query on a database with about 12000 items

First I run the query, get the message that it will update 12000 items and then I get a next screen which shows me the following message
(my applogies for the translation )

not all records in the update query could update.
Microsoft Access was not able to update the following: 144 records due to an error of converting the type .......

what exactly does this mean?
Is there a way to find out which 144 records have the problem?

 

[KenHigg]

I think you will have to poke around with this and figure out for yourself what the issue is. How many fields are you trying to update? If it's just one it should be fairly easy to figure out, but if there are several it could get complicated real fast...

 

[maxmangion]

probably you are trying to update a field with values of different datatype than the one set for your field ... take a look at the records you're trying to update and make sure that you're not trying to update number fields with text for example.

 

[khawar]

1-Tell us your table structure i-e field names and data types
2-Update statement you are using

 

[wilkob]

Fields:

Spec
Thick
Wdth
Lgth
Dia
OD
ID
HB
Rm
Rp02
Rp05
A5
A4
Yield
Elong
IACS

All fields are text field 255 characters long




UPDATE TagsTraceDatabase
SET TagsTraceDatabase.Thick = IIf(InStr(1,[Spec],"THICK=")>0,Mid([Spec],InStr(1,[Spec],"THICK=")+7,InStr(InStr(1,[Spec],"THICK="),[Spec],">")-InStr(1,[Spec],"THICK=")-7),""),
TagsTraceDatabase.Wdth = IIf(InStr(1,[Spec],"WIDTH=")>0,Mid([Spec],InStr(1,[Spec],"WIDTH=")+7,InStr(InStr(1,[Spec],"WIDTH="),[Spec],">")-InStr(1,[Spec],"WIDTH=")-7),""),
TagsTraceDatabase.Lgth = IIf(InStr(1,[Spec],"LG=")>0,Mid([Spec],InStr(1,[Spec],"LG=")+4,InStr(InStr(1,[Spec],"LG="),[Spec],">")-InStr(1,[Spec],"LG=")-4),""),
TagsTraceDatabase.Dia = IIf(InStr(1,[Spec],"DIA=")>0,Mid([Spec],InStr(1,[Spec],"DIA=")+5,InStr(InStr(1,[Spec],"DIA="),[Spec],">")-InStr(1,[Spec],"DIA=")-5),""),
TagsTraceDatabase.OD = IIf(InStr(1,[Spec],"OD=")>0,Mid([Spec],InStr(1,[Spec],"OD=")+4,InStr(InStr(1,[Spec],"OD="),[Spec],">")-InStr(1,[Spec],"OD=")-4),""),
TagsTraceDatabase.ID = IIf(InStr(1,[Spec],"ID=")>0,Mid([Spec],InStr(1,[Spec],"ID=")+4,InStr(InStr(1,[Spec],"ID="),[Spec],">")-InStr(1,[Spec],"ID=")-4),""),
TagsTraceDatabase.HB = IIf(InStr(1,[Spec],"HB=")>0,Mid([Spec],InStr(1,[Spec],"HB=")+4,InStr(InStr(1,[Spec],"HB="),[Spec],">")-InStr(1,[Spec],"HB=")-4),""),
TagsTraceDatabase.Rm = IIf(InStr(1,[Spec],"Rm=")>0,Mid([Spec],InStr(1,[Spec],"Rm=")+4,InStr(InStr(1,[Spec],"Rm="),[Spec],">")-InStr(1,[Spec],"Rm=")-4),""), TagsTraceDatabase.Rp02 = IIf(InStr(1,[Spec],"Rp0.2=")>0,Mid([Spec],InStr(1,[Spec],"Rp0.2=")+7,InStr(InStr(1,[Spec],"Rp0.2="),[Spec],">")-InStr(1,[Spec],"Rp0.2=")-7),""), TagsTraceDatabase.Rp05 = IIf(InStr(1,[Spec],"Rp0.5=")>0,Mid([Spec],InStr(1,[Spec],"Rp0.5=")+7,InStr(InStr(1,[Spec],"Rp0.5="),[Spec],">")-InStr(1,[Spec],"Rp0.5=")-7),""), TagsTraceDatabase.A5 = IIf(InStr(1,[Spec],"A5%=")>0,Mid([Spec],InStr(1,[Spec],"A5%=")+4,InStr(InStr(1,[Spec],"A5%="),[Spec],">")-InStr(1,[Spec],"A5%=")-4),""), TagsTraceDatabase.A4 = IIf(InStr(1,[Spec],"A4%=")>0,Mid([Spec],InStr(1,[Spec],"A4%=")+4,InStr(InStr(1,[Spec],"A4%="),[Spec],">")-InStr(1,[Spec],"A4%=")-4),""), TagsTraceDatabase.Yield = IIf(InStr(1,[Spec],"YIELD=")>0,Mid([Spec],InStr(1,[Spec],"YIELD=")+4,InStr(InStr(1,[Spec],"YIELD="),[Spec],">")-InStr(1,[Spec],"YIELD=")-4),""), TagsTraceDatabase.Elong = IIf(InStr(1,[Spec],"ELONG=")>0,Mid([Spec],InStr(1,[Spec],"ELONG=")+4,InStr(InStr(1,[Spec],"ELONG="),[Spec],">")-InStr(1,[Spec],"ELONG=")-4),""), TagsTraceDatabase.IACS = IIf(InStr(1,[Spec],"IACS=")>0,Mid([Spec],InStr(1,[Spec],"IACS=")+4,InStr(InStr(1,[Spec],"IACS="),[Spec],">")-InStr(1,[Spec],"IACS=")-4),"");

 

[KenHigg]

In my humble opinion this is too much garbage to be trying to manage through a query. At a minium you should evaluate all of the if stuff before you get to the query.

Something like:

dim strThick as string

strThick = IIf(InStr(1,[Spec],"THICK=")>0,  Mid([Spec],InStr(1,[Spec],"THICK=")+7,InStr(InStr(1,[Spec],"THICK="),[Spec],">")-InStr(1,[Spec],"THICK=")-7),""),

Then use strThick in the query string

Second, clean your post up with a code block to make it more readable if you do intend to proceed with it.

Just my opinions -