Dealing with Manipulation of Data using Functions in Access:

FirstName

MidInit

LastName

Whole

Susan

M

Ash

Susan M Ash

David

R

Costa

David R Costa

The results of concatenating the first name with a space and then with the middle initial followed by a space and then with the last name are shown in the field whole.

To get the word Whole as the name at the top of the margin, I inserted it with the formula in the formula in the design as shown below:

Whole: [FirstName] & " " & [MidInit] & " " & [LastName]

Note that I could have used the same construction in the Expression Builder to get the title as well. The default before I added the title was Expr1.

The SQL that was generated for this query was:

SELECT [NAtomanipulate].[FirstName], [NAtomanipulate].[MidInit], [NAtomanipulate].[LastName], [FirstName] & " " & [MidInit] & " " & [LastName] AS Whole

FROM NAtomanipulate;

In this example, I have a field called WholeStAdr which is 30 characters of text. I want to break it up into the parts. The problem is that the street name can be made up of multiple words. In the first example, I know that the street type is there and that it is two characters. Expr1 is simply showing the length of the fields.

WholeStAdr

StreetNumber

Street Name

StreetType

Expr1

123 Elm St

123

Elm

St

10

34 Main St

34

Main

St

10

1234 Heritage Rd

1234

Heritage

Rd

16

345A North Main St

345A

North Main

St

18

 

StreetNumber: Left([WholeStAdr],InStr([WholeStAdr]," ")-1)

The street number is calculated by taking the left characters of WholeStAdr up to the location of the space -1.

StreetName:Mid([WholeStAdr],InStr([WholeStAdr],"

")+1,(Len([WholeStAdr])-(InStr([WholeStAdr]," ")+3)))

The street name is calculated by taking characters in the middle of street name starting with the location of the first space +1. Then you have to figure out the end which is the length of the field with the number and the street type subtracted. To do this I locate the first space, add 3 for the type and space at the end and determine where the street name ends.

StreetType: Right([WholeStAdr],2)

StreetType is simply the last two characters.

Different people will use different logic to extract parts, in this example I wanted to use a variety of functions such as Mid, InStr, and Len.

In the next examples I am using functions that involve dates, the results are shown below.

ItemNumber

ItemName

DateStartCarry

Elapsed Weeks

TodayDate

Elapsed Days

11111

Desk Lamp

10/12/00

22222

Victorian Lamp

1/23/96

246

10/12/00

1724

33333

Pen Set

10/12/00

44444

Pen & Pencil Set

3/20/00

29

10/12/00

206

55555

Chinese Vase

5/19/00

21

10/12/00

146

66666

Tea Set

10/1/00

1

10/12/00

11

Elapsed Weeks: DateDiff("ww",[Inventory]![DateStartCarry],Date())

In this example, I am using the DateDiff function to find the number of weeks that have elapsed between the DateStartCarry and the system date which is Date( ). The code ww is what specifies the number of weeks. In the next example, I am showing the number of elapsed days by using y instead of ww.

Elapsed Days: DateDiff("y",[Inventory]![DateStartCarry],Date())

In these examples I am doing more with the date functions:

ItemNumber

DateStartCarry

Quarter

DayWeek

DayName

22222

1/23/96

1

3

Weekday

44444

3/20/00

1

2

Weekday

55555

5/19/00

2

6

Saturday

66666

10/1/00

4

1

Sunday

Quarter: DatePart("q",[Inventory]![DateStartCarry])

DatePart tells the part of the year, in this case I am using q with the date on the record to tell what quarter the date is in.

DayWeek: Weekday([Inventory]![DateStartCarry])

Weekday returns the day of the week of the date on the record. The number returned is a number with Sunday being 1 and Saturday being 6.

IIf:

DayName: IIf([DayWeek]="1","Sunday",IIf([DayWeek]="6","Saturday","Weekday"))

In the example above, I am doing an IIf test on DayWeek which was calculated above. If the DayWeek is 1, I will display Sunday, else I will test to see if the DayWeek is 6. If it is I will display Saturday. For all other options, I will display Weekday.

In the next few example, I am going to use IIF to gather some information. This is testing to see if Location is null. If it is, I an displaying Empty, otherwise I display the location.

TheLocation: IIf(IsNull([Location]),"Empty",[Location])

ItemNumber

ItemName

TheLocation

11111

Desk Lamp

RN1234

22222

Victorian Lamp

RN2345

33333

Pen Set

AZ9876

44444

Pen & Pencil Set

AZ8765

55555

Chinese Vase

Empty

66666

Tea Set

Empty

ItemNumber

ItemName

DateCheck

NumToOrder

OrderMsg

11111

Desk Lamp

Date needed

-5

Need to Order

22222

Victorian Lamp

1/23/96

Okay

33333

Pen Set

Date needed

-56

Need to Order

44444

Pen & Pencil Set

3/20/00

-20

Need to Order

55555

Chinese Vase

5/19/00

Okay

66666

Tea Set

10/1/00

5

Okay

DateCheck: IIf(IsNull([DateStartCarry]),"Date needed",[DateStartCarry])

In the example above I did the same thing with DateStartCarry.