append string to current record

[garethl]

hello

does anyone know how to append a string to an existing text field so i have a record and the value of this text field is say "abc" i run an update query which pulls the string "def" out of another table and then now the value is "abcdef"

I have tried the following sql

UPDATE TBL_TmpSubmission
LEFT JOIN TBL_PropertyType ON TBL_TmpSubmission.PropertyType = TBL_PropertyType.PropertyType SET TBL_TmpSubmission.ErrorCode = [TBL_TmpSubmission].[ErrorCode] & "property"
WHERE (((TBL_PropertyType.PropertyType) Is Null));

which I expected to append the string "property" to the field which already had the value "measure" but it just overwrites the string having the same effect as

UPDATE TBL_TmpSubmission
LEFT JOIN TBL_PropertyType ON TBL_TmpSubmission.PropertyType = TBL_PropertyType.PropertyType SET TBL_TmpSubmission.ErrorCode = "property"
WHERE (((TBL_PropertyType.PropertyType) Is Null));

i have a temporary table in my db which is checked for errors, that is to say there are three fields in a further lookup table and three of the fields in this temporary table TBL_TmpSubmission are checked for nonexistent property types, fuel types and measure types by queries with the structure given above

it all has the effect i need of filling in this error code field so the error types can be displayed using a later select query - except when there is say a measure error AND a property error because then one query just overwrites the effect of the other

what i want is to be able to use say letter codes "P", "F", "M", append them to one another and then in my later select display that code so the user can see all the fields which need to be corrected

 

[Brianwarnock]

Did you cut and paste that SQL or retype it into the post.
The reason I ask is that I thought it should work, ran a little test, but without the where clause which shouldn't affect it, and it worked whether it was
Set table1.field1 = [field1] & "abc"
or Set field1=[field1] & [field2]

where field2 comes from the other table.

Brian

 

[garethl]

ok hang on i'll try it again, i was suprised that didn't work

 

[garethl]

no it doesn't work for me, its strange

i was wrong about what the actual behaviour is though, had to check because i had pasted the sql but what i had actually done was run one query twice - the one that updated the field to 'measure' so i just expected to see it say 'measuremeasure'

so i checked using 'abc' the first time and and 'def' the second and you don't end up with 'abcdef' but you don't end up with 'def' either as it would if the query was overwriting which i simply assumed it was (sorry about that) you end up with 'abc' so the second query is not having no effect

i'll see now if its because of the WHERE clause

 

[garethl]

right i get the same kind of strange effect without thge WHERE clause

UPDATE TBL_TmpSubmission LEFT JOIN TBL_MeasureType ON TBL_TmpSubmission.Measure = TBL_MeasureType.MeasureType SET TBL_TmpSubmission.ErrorCode = "abc";

sets everything to "abc"

subsequently running

UPDATE TBL_TmpSubmission LEFT JOIN TBL_PropertyType ON TBL_TmpSubmission.PropertyType = TBL_PropertyType.PropertyType SET TBL_TmpSubmission.ErrorCode = [TBL_TmpSubmission].[ErrorCode] & "def";

leaves everything set to "abc"

so does

UPDATE TBL_TmpSubmission SET TBL_TmpSubmission.ErrorCode = [TBL_TmpSubmission].[ErrorCode] & "def";

so it isn't the join either

 

[Brianwarnock]

UPDATE copytable1 LEFT JOIN Table4 ON copytable1.id = Table4.id SET copytable1.Field1 = [copytable1].[field1] & [a];


That's one version of my query that works a is a field in table4, great naming standards , but whether I use strings "CDE" or different joins , no joins or whatever I cannot get it to fail, weird.
Can you attach yours for a look see?

Brian

 

[garethl]

it is weird i must be doing something else wrong somewhere probably something really silly

your sql there is different in that [a] is a field in a table but you say it works with a string instead and otherwise it completly equivalent to what i'm using save for the table names

can't really just post the db coz its full of confidential information but i could copy it empty the tables and fill them with nonsense i guess

i need to have a proper look at it, i had actually worked round it by just using three different fields and using OR's in the subsequent select but it would be better to get it working like this i guess

 

[garethl]

what is strange though is that the first update seems to update the field to the required string plus lots of spaces

i think this is the problem - probably the spaces are taking the field up to its length limit and the subsequent update is failing because the resulting string would be too long

i am running these queries in the query builder though and each time i run the second update it tells me that i'm updating 4 records but then when i look at the table the strings are unchanged

 

[Brianwarnock]

I'm flummoxed, if you could post a small amount of nonconfidential nonsense it might help.

Brian

 

[garethl]

got a meeting to go to but i will do this afternoon - like i say i'll bet its something to do with the field length and the random blank characters

its good to know that the code should work and the problems somewhere else anyway

 

[garethl]

yeah going back into meeting in a second but the upshot of it is that they want to completly change the way the error checking works anyway! they just want it printed out to excel so i can use my workaround

i'll work through this anyway though coz its bugging me not knowing why it doesn't work, might not be today now

 

[Brianwarnock]

Ok , thanks for keeping us posted.

Brian