Combine cells into one cell

In this example, if cell A1 was "123" and cell B1 was "456," the result would be "123-456."

Combine fields into one field

In a created query field, type:
New Field: [FName] & " " & [LName]

In this example, if the [FName] was "Ralph" and [LName] was "Jones," the result in [New Field] would be "Ralph Jones."

Convert string of numbers (text format) to number format (Integer)

CInt Function

This is used for numbers up to 33,767 (Integer)

Convert string of numbers (text format) to number format (Integer)

CLng Function

This is used for numbers up to 2,147,483,647 (Long Integer)

Convert string of numbers (text format) to number format (Integer)

VAL Function

Returns a variant. It can take a bit longer due to this. If the string contains a character, it stops.

Convert string of numbers (text format) to currency format

CCur Function

Returns a currency. This will allow values up to approximately 922 trillions dollars.

Isolate certain digits in a fixed string of numbers (our example will be a 10-digit number).

In the results cell, type:
=MID(A1,4,3)

"A1" is the cell containing the string. "4" represents the digit location. "3" represnts how many digits you want from the "4". In our example, if "A1" was 7351254784, it would return "125".

Isolate certain digits in a fixed string of numbers (our example will be a 10-digit number)

Formula Definition Box:
=Substr ( [FieldName] ; 4 ; 3 )

"4" represents the digit location. "3" represnts how many digits you want from the "4". In our example, if the string is 7351254784, it would return "125".

Isolate certain digits in a fixed string of numbers (our example will be a 10-digit number)

In a created query field, type:
ABC:MID([FieldName],4,3)

Counts then number of characters in a string.

In the results cell, type:
=LEN(A1)

Returns the first (left) three characters of a string.

In a created query field, type:
X:Left([Y]![Z],3)
Or another method could be the following:
X:Left([FieldName],3)

"X" is the name of our new field. "Y" is the name of the table. "Z" is the name of the field with the string. "3" is used because we want to return the first three (3) digits. Note: The "[Y]!" reference might be optional, depending on the complexity of the query.

Returns the first (left) three characters of a string.

In the results cell, type:
=LEFT(A1,3)

"A1" is the cell containing the string. You want the first three digits, so this is where the "3" comes from. In our example, if "A1" was 7351254784, it would return "735".

Returns the last (right) three characters of a string.

In a created query field, type:
X:Right([FieldName],3)

Returns the last (right) three characters of a string.

In the results cell, type:
=RIGHT(A1,3)

"A1" is the cell containing thestring. You want the last three digits, so this is where the "3" comes from. In our example, if "A1" was 7351254784, it would return "784".

Create a wildcard parameter query

In the "criteria" of a query, type:
Like"*"&[FieldName]&"*"

Take a string, and capitalize the first letter in each word.

In a created query field, type:
NewField:StrConv([FieldName],3)

Converting Names to Regular Names with Upper Case in first Character Only.

ACCESS

FirstCharacter = Left(Me.txtLastName, 1)
FirstCharacterCap = UCase(FirstCharacter)
HowLong = Len(Me.txtLastName)
Lastcharacters = LCase(Right(Me.txtLastName, HowLong - 1))
MoveLastName = FirstCharacterCap & Lastcharacters

Take string, and pulls only the first word.

In a created query field, type:
x:
IIf(InStr([FieldName]," ")>0,Left([FieldName],InStr([FieldName]," ")-1),[FieldName])

Remove the dashes, parenthesis, slashes, etc., from a text field.

In a created query field, type:
X: Replace(Replace(Replace(Replace([YourField],"-",""),"/",""),chr(39),"")," ","")

Parse a [FullNm] field to [Last Name],[First Name]

In a created query field, type:
X:Mid([FullNm],InStr([FullNm]," ")+1) & ", " & Left([FullNm],InStr([FullNm]," ")-1)

This only works if the [FullNm] field contains two names.