Proper Case Issue in Address

[CanFlightSim]

I am using the function
StrConv([Address 1],3)
In a query for a report

Everything is fine except the Quadrants that exist in most records...
SE, SW, NE, and NW

535 CLEVELAND CRES SE
yields
535 Cleveland Cres Se

604 1 ST SW, 224
Yields
604 1 St Sw, 224

If they were all at the end, I could use string handling to do
something but they are throughout the string.

I can't think of a way to deal with these except a snakey bunch of if
statements.

I would sure appreciate some light on this.

Thank you in advance.
Will

 

[Marshall Barton]

I am using the function
StrConv([Address 1],3)
In a query for a report

Everything is fine except the Quadrants that exist in most records...
SE, SW, NE, and NW

535 CLEVELAND CRES SE
yields
535 Cleveland Cres Se

604 1 ST SW, 224
Yields
604 1 St Sw, 224

If they were all at the end, I could use string handling to do
something but they are throughout the string.

I can't think of a way to deal with these except a snakey bunch of if
statements.

How about something like this air code:

Dim aryParts As Variant, k As Integer
Const cstrCap As String - ",N,E,W,S,NE,SE,SW,NW,"
aryParts = Split(StrConv(textbox, 3) , " ")
For k = 0 To UBound(aryParts)
If cstrCap Like "*," & aryParts(k) & ",*" Then
aryParts(k) = UCase(aryParts(k))
End If
Next k
textbox = Join(aryParts, " ")