Longest Data Element

Here is a bit of MS Access code to determine the longest text in a field. Useful if you’ve imported a text file and want to trim the size of the resulting text field down from the default of 255. Create a query with the following formula in a field:

Text_Length: Max(Len([Table_name]![Field_name]))

Removing Spaces from a Field

How to remove a spaces from data within a field, either from the sides or from the middle

Case 1

The spaces are on either side of the data and any spaces in the middle should stay.
Example: ” Go To ” becomes “Go To”

Access QBE

In an update query, use this expression in “Update To”

Trim ([field1])

Case 2

The space are in the middle of the data and should be removed
Example: ” Go To ” becomes “GoTo”

MS SQL

use [database]
update [table “” not found /]

set [field1] = replace([field1],’ ‘,”)
go

Note that you must use the single quote. Also there is a space between the first pair of single quotes and there is nothing between the second pair of single quotes. So you are basically telling the system to replace a space with nothing.

Access QBE

In an update query, use this expression in “Update To”

Replace ([field1],” “,””)

Excel

=SUBSTITUTE([cell];” “;””)

Status Messages in Access

Here is a way to be able to show status messages when running a macro in MS Access.

1. Create a form and name it something useful. In my example, the form is called “Status”
2. On this form, create a text box. When you create the box, you will have the text box itself as well as a label. The label is optional. Select the text box and look at the property sheet. In the Other tab, either make note of the name, or rename the element to something memorable. In my example, the element is called “Text1”.

Access_Status_Form

3. In your macro, add an “OpenForm” event to open the form you’ve just created. Window Mode should be “Normal.” I thought it would be cool, if it was a pop up windows, but the macro will not continue to run as long as the window is open.
4. In your macro, add “SetValue” at locations where you want to update the status message. The Item value for my example is “[Forms]![Status]![Text1]”. You can use the magic want to search for the element. The value for Expression is the message you want to display in quotes.

Access_Status_Macro

Every SetValue step will change the message in the form.