Fields configured as either a text or memo data type in an Access table are typically handled in code -- if, that is, there's a need to handle them in code -- using string variables.
(If you want to see any of the following examples in action, drag and drop a button onto a test form and copy and paste the code into the button's click event handler subprocedure. You will probably want to create a separate button for each block of code below that interests you. All of the code was written and tested in Access 2000 only.)
Think of strings as labels that may contain a mix of letters and digits.
The Replace function makes it easy to manipulate characters in a string. It can replace one pattern of characters with another pattern of characters. Say, for instance, that Bill is really a Jill. The code below will replace all instances of "B" in a string with a "J."
The replace function is a flexible tool. Say we have the following problem string.
Using an empty substring as our replacement value -- represented as so, "" -- we can effectively remove problem characters.
A new problem: our postal code of 80801 should really be 60801. This is a bit more tricky. We only want to change a single 8. We do not want to change any other 8s sitting peacefully in the string. Experience the power:
Power should always be paired with caution. What if we had another string that looked like the following? 97881. The replace function as it's used above would change it to 97681. Assuming we don't want that -- then we're suddenly unhappy. The point yet again: be careful. In the meantime, here's a quick fix using a simple If statement.
If there were a Replace function to fix the many mistakes I've made in my life, things would be better. As your database grows in complexity and your skills develop, you may find yourself entering or changing data in a table using code and a SQL string. And you may, along the way, find yourself stuck if the string you'd like to add to a field contains an apostrophe. Consider the following code that attempts to add a record to a table.
Note: To test the example below, create a table named TablePeople with at least two fields...
MyID = AutoNumber (Primary Key)
Last_Name = Text
The code doesn't work because the apostrophe in O'Malley confuses Access and SQL, which "think" it is there to delimit the data. The classic SQL fix to this problem is to add what's called an escape character to the silly string. That is, change O'Malley to O''Malley. Replace function to the rescue...
For more help and examples, please consult the Access help file and search this forum or, for that matter, the web. The Replace function is found in most all major programming languages, working much the same across platforms.
Regards,
Tim
(If you want to see any of the following examples in action, drag and drop a button onto a test form and copy and paste the code into the button's click event handler subprocedure. You will probably want to create a separate button for each block of code below that interests you. All of the code was written and tested in Access 2000 only.)
Think of strings as labels that may contain a mix of letters and digits.
Code:
'Create a couple of string variables.
Dim MyName as String
Dim MyPostalCode as String
'Assign values to the variables.
MyName = "Bill"
MyPostalCode = "80801"
'Test: Display the values using a message box.
MsgBox "Name = " & MyName
MsgBox "Postalcode = " & MyPostalCodeThe Replace function makes it easy to manipulate characters in a string. It can replace one pattern of characters with another pattern of characters. Say, for instance, that Bill is really a Jill. The code below will replace all instances of "B" in a string with a "J."
Code:
'Create a string and assign a value to it.
Dim MyName As String
MyName = "Bill"
'Test.
MsgBox "Original Name: " & MyName
'Change the name from Bill to Jill.
MyName = Replace(MyName, "B", "J")
'Test.
MsgBox "New Name: " & MyNameThe replace function is a flexible tool. Say we have the following problem string.
Code:
Dim MyName as String
'A couple of meaningless x's are inadvertently added to our string.
MyName = "Jxillx"Using an empty substring as our replacement value -- represented as so, "" -- we can effectively remove problem characters.
Code:
Dim MyName as String
MyName = "Jxillx"
'Test.
Msgbox "Bad data: " & MyName
'Remove all instances of x in the MyName string.
MyName = Replace(MyName, "x", "")
'Test.
Msgbox "Fixed data: " & MyNameA new problem: our postal code of 80801 should really be 60801. This is a bit more tricky. We only want to change a single 8. We do not want to change any other 8s sitting peacefully in the string. Experience the power:
Code:
Dim MyPostalCode As String
MyPostalCode = "80801"
MsgBox "Original Code " & MyPostalCode
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'In English:
' In the MyPostalCode string, start searching
' for an 8 from the first character in the string.
' Once the 8 is found, replace it with a 6.
' Only do one substitution in the string -- do
' not carry on and replace other 8s.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'In code:
MyPostalCode = Replace(MyPostalCode, "8", "6", 1, 1)
'Test
MsgBox "Changed to: " & MyPostalCode
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Power should always be paired with caution. What if we had another string that looked like the following? 97881. The replace function as it's used above would change it to 97681. Assuming we don't want that -- then we're suddenly unhappy. The point yet again: be careful. In the meantime, here's a quick fix using a simple If statement.
Code:
If MyPostalCode = "80801" Then
MyPostalCode = Replace(MyPostalCode, "8", "6", 1, 1)
End IfIf there were a Replace function to fix the many mistakes I've made in my life, things would be better. As your database grows in complexity and your skills develop, you may find yourself entering or changing data in a table using code and a SQL string. And you may, along the way, find yourself stuck if the string you'd like to add to a field contains an apostrophe. Consider the following code that attempts to add a record to a table.
Note: To test the example below, create a table named TablePeople with at least two fields...
MyID = AutoNumber (Primary Key)
Last_Name = Text
Code:
'Create a SQL string to insert a record
'into a table named TablePeople with a
'field named Last_Name, putting O'Malley
'into the Last_Name field.
Dim s As String
s = _
"INSERT INTO TablePeople " & _
"(Last_Name) " & _
"VALUES(" & "'" & "O'Malley" & "'" & ")"
'Access halts at the line below, raising a syntax error.
CurrentDb.Execute sThe code doesn't work because the apostrophe in O'Malley confuses Access and SQL, which "think" it is there to delimit the data. The classic SQL fix to this problem is to add what's called an escape character to the silly string. That is, change O'Malley to O''Malley. Replace function to the rescue...
Code:
'Fix apostrophes.
Dim MyName As String
MyName = Replace("O'Malley", "'", "''")
'Create a SQL string to insert a record
'into a table named TablePeople with a
'field named Last_Name, putting O'Malley
'into the Last_Name field.
Dim s As String
s = _
"INSERT INTO TablePeople " & _
"(Last_Name) " & _
"VALUES(" & "'" & MyName & "'" & ")"
'This works now, adding a new record to the table,
'O'Malley's apostrophe safely in place.
CurrentDb.Execute sFor more help and examples, please consult the Access help file and search this forum or, for that matter, the web. The Replace function is found in most all major programming languages, working much the same across platforms.
Regards,
Tim
