update query to conditionally alter the value of cells

[Guest]

Hello everyone, I am trying to alter the value of a field where, due to my
own errors in the input field, users have been able to input incorrect values
into a table. The cell is called flats and is supposed to have values as
follows:

0016

However i had allowed (& ive fixed the problem since) inputs such as 016 or
even 16 to be placed into the table, as different people have different
styles inconsistencies have developed and there are 1300 such corrupted
results. I had figured on using an Update Query to amend the existing
results, this is what i was thinking along the lines off.

UPDATE Students SET Students.flat = IIF(Len([students].[flat]<> 4) ,
[students].[flat] = "0" & [students].[flat], [students].[flat] =
[students].[flat])

I cant seem to find the documentation on the IIF expression, it seems to
come up with examples from which i've deduced its workings, though this
solution doesnt work.

Thanks in advance

Amit

 

[John Spencer (MVP)]

Move a your parentheses around a wee bit. And probably I would change the condition

UPDATE Students
SET Students.flat = IIF(Len([students].[flat])<> 4 ,
[students].[flat] = "0" & [students].[flat],
[students].[flat] = [students].[flat])

A more efficient method might be the following which should find all
Students.Flat that is two or three numeric characters long and change them to be
four characters long.

UPDATE Students
SET Students.flat = Right("00" & [students].[flat],4)
WHERE Students.Flat Like "###" or Students.Flat Like "##"

As usual, try this ON A COPY of your data first. OR make a BACKUP of the data
first. This is just in case something goes wrong.

 

[John Vinson]

However i had allowed (& ive fixed the problem since) inputs such as 016 or
even 16 to be placed into the table, as different people have different
styles inconsistencies have developed and there are 1300 such corrupted
results. I had figured on using an Update Query to amend the existing
results, this is what i was thinking along the lines off.

UPDATE Students SET Students.flat = IIF(Len([students].[flat]<> 4) ,
[students].[flat] = "0" & [students].[flat], [students].[flat] =
[students].[flat])

No IIF is needed!

Update to

Right("0000" & [Flat], 4)

The concatenation will turn "16" into "000016", and "0016" into
"00000016"; the Right() function will return the rightmost four
characters.

Note that this might cause duplicates in the table if one record
contains "0016", and another contains "16".


John W. Vinson[MVP]

 

[Guest]

Thanks guys, those were clever solutions to my problem.

Amit