Populate field based on character in another field

[DJ44]

I am attempting populate a field (String) in a table based on the value of the last character in a string of another field in the same table.

The field I want to populate is called PhotoCaption, the field that contains the string whose last character it to be tested is called FileName.

The format of FileName is XXXXX-X. eg 40150-1,40150-2 etc.

I have written code that will do most of this, except I am unsure how to test what that last character is. (I think it has something to do with Mid or Len but ....

Could someone look at my code and suggest how I accomplish this.

I am new to VBA and programming in general so if you notice other faults I am open and thankful for all suggestions.

All help appreciated.

DJ


Public Sub PopulateField()
On Error GoTo ErrorHandler
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim intFileNum As Integer
Dim lngCount As Long
Dim AndFile As String

'Update tblPhoto.PhotoCaption based on FileName

Set cnn = CurrentProject.Connection

rst.Open "tblPhotos", cnn, , , acCmdTable

rst.MoveFirst
With rst
Do While Not .EOF

'Populate based on last character in FileName

'If last character =1 then PhotoCaption is Looking North
If rst!Fields("FileName") Then _
rst!Fields("PhotoCaption") = "Looking North"

'If last character =2 then PhotoCaption is Looking East
ElseIf rst!Fields("FileName") Then _
rst!Fields("PhotoCaption") = "Looking East"

'If last character =3 then PhotoCaption is Looking South
ElseIf rst!Fields("FileName") Then _
rst!Fields("PhotoCaption") = "Looking South"

'If last character =4 then PhotoCaption is Looking West
ElseIf rst!Fields("FileName") Then _
rst!Fields("PhotoCaption") = "Looking West"

'If last character =5-9 then PhotoCaption is Other
ElseIf rst!Fields("FileName") Then _
rst!Fields("PhotoCaption") = "Other"

End If
'Goto next record
.MoveNext
Loop
End With

'Close the Recordset and Connection Objects
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

Exit Sub
ErrorHandler:
Set rst = Nothing
Set cnn = Nothing
Err.Clear

End Sub

 

[Incisor]

Is what your looking for or have I over simplified the issue?

lastchar = Right(Filename,1)

 

[Ed207_us]

Evaluating Individual Characters

The Mid and Len functions are exactly what you use.

In your case you dont need the Len() function since you know before hand that the format is always the same.

'If last character =1 then PhotoCaption is Looking North
If rst!Fields("FileName") Then _
rst!Fields("PhotoCaption") = "Looking North"

If you want the last character of FileName just use
Mid([Filename],7,1)

Mid( <Field or String Variable>, <Starting Position within string, ALWAYS STARTS AT 1 NOT 0>,< How Many Characters to grab>)

If Mid(Rst!Fields("FileName"),7,1) = "1" Then _
rst!Fields("PhotoCaption") = "Looking North"

Your last statement is a little more complicated. Use the ASC equivalent of the character.

If (ASC(Mid(Rst!Fields("FileName"),7,1) >=53) AND (ASC(Mid(Rst!Fields("FileName"),7,1) <=57)


Also, if you want some error checking in here so that your PhotoCaption field is not blank, you might want to validate your FILENAME field before hand.

Try:
If Len(RST!Field("FileName")) <>7 Then RST!Fields("PhotoCaption") = "Error in FileName"

If Mid(RST!Fields("FILENAME"),Len(RST!Field("FileName") - 1,1) <> "-" Then rst!Fields("PhotoCaption") = "Not Available from File Name"

 

[DJ44]

Thanks for your replies.

I followed your suggestion and it worked great --- as well as understanding how to use the Mid function.

Cheers. DJ