IIF or Len Statement Help

  • Thread starter Thread starter golfinray
  • Start date Start date
G

golfinray

I have some school districts that are plain, in other words Malvern School
District, and I have some with 4 numbers and a dash in front of them, like
2043 - Hartford School District. The ones that have the numbers and dash I
would like to strip off and the others leave alone. I have tried several
combinations and have not been able to get it right. Thanks!!!!
 
golfinray said:
I have some school districts that are plain, in other words Malvern
School District, and I have some with 4 numbers and a dash in front
of them, like 2043 - Hartford School District. The ones that have the
numbers and dash I would like to strip off and the others leave
alone. I have tried several combinations and have not been able to
get it right. Thanks!!!!
Click to expand...

1. Backup your database
2. Try this:
UPDATE districts
Set District = Mid([District],8)
WHERE Mid([District],6,1) = "-"

(to use this, switch your query to SQL View, paste it in, correct the
table and field names, and switch back to Design View to see how to do
this in the Design grid)
 
golfinray said:
I have some school districts that are plain, in other words Malvern
School District, and I have some with 4 numbers and a dash in front
of them, like 2043 - Hartford School District. The ones that have the
numbers and dash I would like to strip off and the others leave
alone. I have tried several combinations and have not been able to
get it right. Thanks!!!!
Click to expand...

Oh wait, is your intent to leave the data alone and do this in a SELECT
statement? If so, the solution is similar:

Select Mid([District],8) As ShortDistrictName
From Districts
WHERE Mid([District],6,1) = "-"
 
If the pattern is exactly 4 numbers, a space, a dash, and a space then try the
following as a calculated column in your query.

IIF([DistrictName] Like "#### - *",Mid([DistrictName],*),[DistrictName])

If you want to permanently remove the numbers and the dash, the above would
work in an update query as the new value for the field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top