Update Query to update only part of a field 1

[wwiSports]

I have a table with the field SchoolName. This field has School names such as: Park View Hs, Herndon Hs, Broad Run Hs. I would like to get the Hs to be HS (both letters Capitalized)

I was trying to use:

Right([SchoolName],2) = "HS"

But it is changing the entire field to -1

Why is this happening, and how can I fix it?

Beth

 

[Steve101]

In Access Basic (and many other languages, -1 represents the value for TRUE). What is happening is when you provide the statement

Right([SchoolName],2) = "HS"

in the context where a name is expected (eg. in the field column of a query grid), the query when it is running will "evaluate" the above expression, essentially taking the last two characters of the School and comparing them to the right hand side of the equal sign "HS". In all cases, the last two characters of the school are "hs", so the expression evaluates to "hs" = "HS". Under default conditions, comparing two strings ignores whether the characters being compared are upper or lower case, so the comparision always yields true for each record, and as stated, true is represented by -1. Hense the effect of what you are getting.

To solve your problem, use the following expression in place of what you have above:

Left(SchoolName, Len(SchoolName)-2) & UCase(Right(SchoolName,2))

This expression leaves all but the last two letters of the SchoolName expression in their current case, and then uppercases the last two letters.

Another way to do the same thing might be:

Left(SchoolName, Len(SchoolName)-2) & "HS"

this whilst shorted, would assume that all schools were high schools, which may or may not be the case.

Hope this helps,


Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au