update records in table if less characters than another field

[machumpion]

I want to update a field (X) in a table to the value of another field (Y) in the same record if the current number of characters in X is less than Y.

I tried putting this criteria in Update Query Design:

Len([table1].[x])<Len([table1].[y])

but it didn't work.

Thank you for the excellent tips to come!

 

[sneuberg]

If you put that in the WHERE clause it shows up in the design view like:

This work. Is that how you have it? The SQL of this is:

SELECT Table1.ID, Table1.x, Table1.y
FROM Table1
WHERE Len([table1].[x])<Len([table1].[y]);

 

[machumpion]

If you put that in the WHERE clause it shows up in the design view like:


This work. Is that how you have it? The SQL of this is:

SELECT Table1.ID, Table1.x, Table1.y
FROM Table1
WHERE Len([table1].[x])<Len([table1].[y]);

I noticed that your query is a select query. I would like it to update x with y where len(x) < len. To update it, would i simply put <len([table1].[y]) in the Update Query Criteria? It doesn't seem to work.

 

[sneuberg]

In that case it would look like

and the SQL is

UPDATE Table1 SET Table1.x = [Table1].[y]
WHERE (((Len([table1].[x]))<Len([table1].[y])));

if you mean to update x to equal y.

 

[machumpion]

thanks for that, it worked splendidly. But i need to change the criteria due to something I noticed in my data:

what if I wanted to update x to y simply if len([table1].[y])>7?

 

[sneuberg]

You should be able to just put what you want in the WHERE clause of the SQL like:

UPDATE Table1 SET Table1.x = [Table1].[y]
WHERE Len([table1].[y])>7 ;

 

[machumpion]

Hi Steve,

the query worked beautifully. As a followup, what if i wanted to set x equal to another field in table1 if len(table1.y) is less than 7 characters (else criteria) in the same query?

Thanks!

 

[sneuberg]

Hi Steve,

the query worked beautifully. As a followup, what if i wanted to set x equal to another field in table1 if len(table1.y) is less than 7 characters (else criteria) in the same query?

Thanks!

I have no idea and would just run two queries rather than rack my brain trying to figure it out.

Edit: But now that will be on my mind all day like an ear worm.

 

[machumpion]

update:

i can update it to another field if y is less than 7 characters with an iif statement in sql

update table1 set x = iif(len([table1.[y])>7, [table1].[y], [table1].[z])

 

[MarkK]

It might be worth observing too, that it is unusual to need to internally update data like this in a table. You don't want to make this a routine part of your data management strategy. Rather, what you want to do is store all your data in its most raw form, and then process it at retrieval time, suitable for whatever purpose is required at that time.

Consider a query like...

SELECT IIF(Len(y) > 7, y, z) As x
FROM Table1

In this situation you just store your data as is, no internal processing or saved calculations, and write queries that present that data in the form you need it for your immediate requirement.

This is the difference between a push and pull on your data. You always want to pull data from source, rather than push data to a destination.

 

[sneuberg]

update:

i can update it to another field if y is less than 7 characters with an iif statement in sql

update table1 set x = iif(len([table1.[y])>7, [table1].[y], [table1].[z])

Interesting that you can. I get an "is not a valid name" error when I try to run that query.

Edit: But if you add the missing bracket

UPDATE table1 SET x = iif(len([table1[COLOR="Red"]][/COLOR].[y])>7, [table1].[y], [table1].[z]);

it does work.