3: สารพัน FUNCTION ใน ACCESS

สารพัน Function ใน Access

วัตถุประสงค์

รู้จัก FUNCTION สำคัญๆ ใน Access และวิธีการไปประยุกต์ใช้บน Query หรือ  Form

เนื้อหา

            Access มี Function จำนวนมากสำหรับใช้ในการประมวลผล หรือ แปลงค่าตามที่เราต้องการ การใช้งาน Function ใน Access มีการใช้งานหลักๆได้จาก 3 จุด ดังนี้

1.        ใช้งาน Function บน Control ของ Form  หรือ Report 

2.        เรียกใช้ Function ใน Query  

3.        เรียกใช้ Function จาก Sub หรือ Function ด้วยกันเองใน Code ของ VBA

สมมุติว่าท่านต้องการแสดงวันที่ปัจุบัน บน Form ที่ใช้ในการบันทึกข้อมูล  Function ในการเรียกวันที่ปัจจุบัน คือ Date()  ซึ่งจะให้ค่าวันที่ปัจจุบันออกมา    วิธีทดสอบค่าของ Function ง่ายๆ ใน Access ก็ให้ไปที่ Debug Windows ( Ctrl + G )   แล้วพิมพ์คำสั่ง ? date()

ภาพแสดงการนำ Function ไปใช้งาน

จากภาพตัวอย่างจะเห็นว่า  ค่าวันที่ปัจจุบันได้มากจาก 3 วิธี   แบบแรกจะเห็นว่าใส่ Function ไว้ในค่า Control ตรงๆ  ส่วน ลำดับที่ 2 จะใช้วิธีสร้าง Query และมี การเรียก Function Date() ใน Query แล้วตั้งชื่อ Column เป็น curDate ( สังเกตุที่ Record Source ของ Form ) แล้วนำค่านั้นไปใส่ไว้ใน Control  และลำดับสุดท้าย คือการเขียนโปรแกรม VBA ไว้ใน FORM เป็น Event on_Current  เป็นส่วนที่เขียนไปปะไว้ที่ Control ชื่อ Text27

ถ้าถามว่าวิธีไหนดีสุด ก็ต้องบอกว่า การใส่ค่าใน Control แบบแรกดีที่สุดครับ ง่ายและเร็ว แบบที่ 2 ไม่จำเป็นอย่าใช้ เพราะทำให้ การเรียก Query ช้า  ส่วนแบบที่ 3 ที่ทำใน VBA ใช้กรณีที่การกำหนดค่าที่ซับซ้อนมากมาก

           

Function ใน Access

ใน ACCESS มี Function อยู่มากมายให้เลือกใช้เต็มไปหมด   ผมจะขอแนะนำ Function ที่ใช้บ่อยๆ และสำคัญๆ ตามลำดับ   ในตารางจะแสดง ตย การใช้งาน Function ไว้แล้วนะครับ

กลุ่ม Convert ข้อมูล

สำหรับแปลง ข้อมูลที่แตกต่างกัน เช่น ทำตัวเลขเป็นตัวอักษร แปลงวันที่เป็นตัวอักษร เป็นต้น

-  Function การแปลงข้อมูลทั่วไป

Function	คำอธิบาย	ตัวอย่าง
Int	แปลงจากตัวแปรประเภทอื่นให้เป็น Integer โดยไม่ปัดเศษ แต่ถ้าตัวแปรนั้นมีค่าเป็น ลบ จะทำการ ปัดเศษ	I = 1.6 Msgbox(int(I)) Msgbox(int(-I))
Fix	แปลงจากตัวแปรประเภทอื่นให้เป็น Integer โดยไม่ปัดเศษ  ต่างกับ Int ตรงที่จะไม่ปัดเศษ แม้เป็นค่าลบ	I = 1.6 Msgbox(Fix(I)) Msgbox(Fix(-I))
VAL	แปลงตัวอักษรให้เป็นค่าตัวเลข ถ้ามีตัวอักษรปนอยู่กับตัวเลข Val อาจให้ค่าเป็น 0 ถ้าตัวอักษรนำหน้าตัวเลข	Val(“asd12”) = 0 Val(“12asd12”) = 12 Val(“12.23”) = 12.23
DateSerial	ใช้ในการแปลง ค่าปีเดือนวัน ให้เป็นตัวแปรแบบวันที่ โดยมีการกำหนดค่าให้ Function นี้ 3 ค่า คือ ปี, เดือน , วัน DateSerial(Year,Month,Day)	YX = 1999 MX = 12 DX = 10 Msgbox(DateSerial(YX,MX,DX))
DateValue	* แปลง String ที่มีการจัดเรียงแบบวันที่ ให้เป็นตัวแปรวันที่ DateValue(String)	msgbox(DateValue(“12/10/1999”))
TimeSerial	ใช้ในการแปลงค่าชั่วโมง นาที วินาที เป็นตัวแปรวันที่ เฉพาะตรงส่วนของเวลา TimeSerial(Hour,Minute,Sec)	HX = 12 MX = 10 SX = 05 Msgbox(timeSerial(HX,MX,SX)
TimeValue	* แปลง String ที่มีการจัดเรียงแบบเวลา ให้เป็นตัวแปรแบบวันที่ ในส่วนของเวลา TimeValue(String)	Msgbox(TimeValue(“12:23”))

* DateValue, TimeValue ใช้ด้วยความระมัดระวังนะครับ เพราะการแปลงจะขึ้นกับ Regional Setting ของเครื่องแต่ละเครื่อง ทำให้ผลที่ได้อาจแตกต่างกัน

-   FORMAT

เราใช้ Format ในการแปลงข้อมูลเช่นกัน แต่เป็นการแปลงจากตัวแปรอื่นๆ ให้เป็นตัวอักษร โดยเราสามารถกำหนดรูปแบบตัวอักษรในการแสดงได้ เหมือนที่เรากำหนดไว้ใน Format Properties ของ Form หรือ Report

Syntax

Format(Variable,Picture)

การกำหนด Picture หรือรูปแบบการแสดงผลของ String ที่ Convert แล้ว จะใช้ตัวอักษรพิเศษในการบังคับ ตัวอย่างเช่น Format(Now(),”hh:nn dd/mm/bbbb”)  จะได้เป็น  15:01 05/05/2542 ( สมมุติว่าขณะนี้เป็นวันเวลาดังกล่าว )  ตัวอักษรพิเศษที่ใช้กำหนดคือ

กลุ่มวันเวลา

เครื่องหมาย	คำอธิบาย
: (colon)	ใช้เป็นตัวขั้นเวลา
/	ใช้เป็นตัวขั้นวันที่
C	บังคับให้ใช้ตามแบบของ System Date ใน Windows
D	บังคับให้แสดงตัวเลขวันที่ 1 – 31 ไม่ต้องใส่ 0 นำหน้ากรณีที่เป็นวันที่น้อยกว่า 10
Dd	บังคับให้แสดงวันที่ 01-31 ใส่ 0 นำหน้าถ้าวันที่น้อยกว่า 10
Ddd	ให้ค่าวันในสัปดาห์ เป็นตัวย่อ ( Mon, Tue,..)
Dddd	ให้ค่าวันในสัปดาห์เป็นตัวเต็ม ( Monday, Tuesday ,..)
Ddddd	บังคับให้ใช้ Short Date ที่กำหนดไว้ใน Windows
Dddddd	บังคับให้ใช้ Long Date ที่กำหนดไว้ใน Windows
W	ให้ค่า 1- 7 ของวันในสัปดาห์
Ww	ให้ค่าลำดับสัปดาห์ที่ ใน  1 ปี (1 to 53).
M	บังคับให้แสดงเดือน 1-12 ไม่ใส่ 0 นำหน้าถ้าไม่ถึงเดือน 10
Mm	บังคับให้แสดงเดือน 01-12 ใส่ 0 นำหน้าถ้าไม่ถึงเดือน 10
Mmm	ชื่อเดือนเป็นตัวย่อ 3 ตัวอักษร ( Jan, Feb,..)
Mmmm	ชื่อเดือนเป็นตัวเต็ม ( January, February,… )
Q	แสดงลำดับที่ไตรมาศของปี
Y	Number of the day of the year (1 to 366).
Yy	แสดง 2 หลักสุดท้ายของปี
Yyyy	แสดงปีเป็น 4 หลักเต็ม
H	แสดงชั่วโมง ไม่มี 0 นำหน้าถ้าน้อยกว่า 10
Hh	แสดงชั่วโมง มี 0 นำหน้าถ้าน้อยกว่า 10
N	แสดงนาที ไม่มี 0 นำหน้าถ้าน้อยกว่า 10
Nn	แสดงนาที มี 0 นำหน้าถ้าน้อยกว่า 10
S	แสดงวินาที ไม่มี 0 นำหน้าถ้าน้อยกว่า 10
SS	แสดงวินาที มี 0 นำหน้าถ้าน้อยกว่า 10
Ttttt	กำหนดให้แสดงตาม Long Time ของ Windows
AM/PM	แสดงค่า AM/PM เป็นตัวใหญ่
Am/pm	แสดงค่า am/pm เป็นตัวเล็ก
A/P	แสดงค่า A/P เป็นตัวใหญ่
a/p	แสดงค่า a/p เป็นตัวเล็ก
AMPM	แสดง AMPM ตามที่กำหนดใน Windows
ว	วันที่เป็นเลขไทย ไม่มี 0 นำหน้า ถ้าน้อยกว่า 10
วว	วันที่เป็นเลขไทย มี 0 นำหน้า ถ้าน้อยกว่า 10
ววว	วันที่ไทยในสัปดาห์ เป็นตัวย่อ
วววว	วันที่ไทยในสัปดาห์ เป็นตัวเต็ม
ววววว	วันที่ไทยตาม Short Date
วววววว	วันที่ไทยตาม Long Date
ด	เดือนเป็นเลขไทย ไม่มี 0 นำหน้าถ้าน้อยกว่า 10
ดด	เดือนเป็นเลขไทย มี 0 นำหน้าถ้าน้อยกว่า 10
ดดด	ชื่อเดือนไทยเป็นตัวย่อ
ดดดด	ชื่อเดือนไทยเป็นตัวเต็ม
ปป	ปีพุทธศักราช เป็นเลขไทย 2 หลัก
ปปปป	ปีพุทธศักราช เป็นเลขไทย 4 หลัก
BB	ปีพุทธศักราช เป็นเลขอาราบิค 2 หลัก
BBBB	ปีพุทธศักราช เป็นเลขอาราบิค 4 หลัก

กลุ่มตัวเลข

กลุ่มตัวเลขสามารถกำหนดเงื่อนไขได้เป็น 4 แบบ ตามค่าของตัวแปร การกำหนด Format ของทั้ง 4 แบบ จะขั้นด้วย  ; ( Semi-Colon ) เช่น Format(X,”0.00;(0.00);-;?) ทดลอง Run Program ต่อไปนี้ แล้ว Step ดูทีละขั้นจะเข้าใจมากขึ้น

Sub TestFormat()

    Dim i

    Do Until i = 2

       MsgBox (Format(i, "0.00;(0.00);-;?"))

       If IsEmpty(i) Then

           i = -2

       End If

       i = i + 1

    Loop

End Sub

การกำหนดแบบการแสดงผลทั้ง 4 ตามลำดับคือ

·        แบบ 1 สำหรับผลเมื่อค่าเป็นบวก

·        แบบ 2 สำหรับผลเมื่อค่าเป็นลบ

·        แบบ 3 สำหรับค่าเป็น 0

·        แบบ 4 สำหรับค่าเป็น Null หรือ Empty

( Funtion isEmpty ใช้สำหรับตรวจค่าว่า ตัวแปรนั้น เคยกำหนดถูกกำหนดค่าหรือยัง )

สำหรับเครื่องหมายที่ใช้กำหนดรูปแบบการแสดงบ่อยๆ ในทั้ง 4 แบบคือ

เครื่องหมาย	คำอธิบาย
. (period)	ระบุตำแหน่งจุด ทศนิยม
, (comma)	ระบุ Commar กั้นหลัก 1000
0	บังคับให้แสดงตัวเลข ถ้าไม่มีค่าก็จะแสดง 0 เช่น Format( 1,”000”) จะได้เป็น 001
#	ไม่บังคับการแสดงตัวเลข ถ้าค่านำหน้า หรือทศนิยมไม่มี ก็จะไม่แสดง 0 นำหน้า
$	แสดงเครื่องหมาย $
%	บังคับให้ หาร 100 และมีการแสดงเครื่องหมาย %

กลุ่ม Function String

ใช้ในการจัดการกับตัวแปรที่เป็น String เช่นการตัดคำ Scan หาคำเป็นต้น

Function	คำอธิบาย	ตัวอย่าง
MID	ใช้ในการตัดคำบน String ณ ตำแหน่งที่กำหนด โดยระบุจำนวนที่ตัวอักษรที่ต้องการ Mid(String, StartAt, Length) ถ้าไม่มีการระบุความยาวตัวอักษรที่ต้องการ จะหมายถึงเอาตัวอักษรตั้งแต่ StartAt จนถึงตัวสุดท้าย	Dim Strx Dim StrTmp StrX = “STU10046AB” StrTmp = Mid(Strx,4,5) Msgbox(StrTmp) StrTmp = Mid(Strx,4) Msgbox(StrTmp)
Left	ตัดตัวอักษรจากซ้าย ตามจำนวนตัวที่กำหนด มีรูปแบบคือ Left(String,Length) คล้ายกับคำสั่ง Mid ที่เริ่มจากตัวแรกเสมอ	Dim Strx Dim StrTmp StrX = “STU10046AB” StrTmp = Left(Strx,3) Msgbox(StrTmp)
Right	ตัดตัวอักษรจากขวา ตามจำนวนตัวที่กำหนด มีรูปแบบคือ Right(String,Length)	Dim Strx Dim StrTmp StrX = “STU10046AB” StrTmp = Right(Strx,2) Msgbox(StrTmp)
Instr	ใช้ในการ Scan หาตำแหน่งของตัวอักษรที่ต้องการค้นหาใน String เช่นเราต้องการทราบว่า มี “.” อยู่ในตัวแปร String ที่ตำแหน่งใด  กำหนด 3 Input คือ ตำแหน่งเริ่มต้น , String ที่ถูกค้น, คำที่ต้องการค้น Instr(Start,StrSearch,StrX)	Dim StrTmp, Sname StrTmp = "COMMAND.COM" Sname = Mid(StrTmp, 1, InStr(1, StrTmp, ".") - 1) MsgBox (Sname)
Trim	ตัด Space หน้าหลังของตัวอักษร Trim(String)	Dim Strx Strx =”   HELLO  “ Msgbox(“-“ & Strx & “-“) Strx = Trim(Strx) Msgbox(“-“ & Strx & “-“)
Ltrim, RTrim	ตัด Space หน้าหน้าสำหรับ Ltrim และ Space หลังสำหรับ Rtrim Rtrim(String) Ltrim(String)	Dim Strx Strx =”   HELLO  “ Msgbox(“-“ & RTrim(Strx) & “-“) Msgbox(“-“ & LTrim(Strx) & “-“)
Len	Return ค่าความยาวของ String Len(String)	Msgbox(Len(“Hello”)) Msgbox(Len(“Hello-“)
Lcase	ปรับตัวอักษรให้เป็นตัวเล็กทั้งหมด Lcase(String)	Msgbox(Lcase(“Hello”))
Ucase	ปรับตัวอักษรให้เป็นตัวใหญ่ทั้งหมด Ucase(String)	Msgbox(Ucase(“Hello”))

กลุ่ม Function วันที่ / เวลา

ใช้ในการจัดการเกี่ยวกับวันที่เวลา การประมวลผลเกี่ยวกับเวลา ซึ่ง Function เหล่านี้ จะช่วยหักลบ ปี ที่มี 27, 28 วันให้อัตโนมัติ เราใช้ในเชิงธุรกิจมากเช่น กำหนดวัน Deal ชำระเงิน หรือกำหนดงวดบัญชีเป็นต้น

Function	คำอธิบาย	ตัวอย่าง
Now	ให้ค่าวันที่ เวลา ปัจจุบัน ไม่ต้องกำหนดค่า input	Msgbox(Now)
Time	ให้ค่าเวลาปัจจุบัน ไม่ต้องกำหนดค่า input	Msgbox(Time)
Date	ให้ค่าวันที่ปัจจุบัน ไม่ต้องกำหนดค่า input	Msgbox(Date)
Year, Month, Day, Hour, Minute, Second, Weekday	ให้ค่า ปี เดือน วัน ชั่วโมง นาที วินาที และวันที่ในสัปดาห์   กำหนดค่าเป็นตัวแปรที่เป็นวันที่	Year(Now) Month(Now) Day(Now) Hour(now) Minute(Now) Second(Now) WeekDay(Now)
DateAdd	ใช้ในการบวกค่าวันเวลา มี 3 Input คือ กำหนดหน่วยที่ต้องการบวก, จำนวนที่ต้องการบวก, ค่า หรือ ตัวแปร Date ที่ทำการ DateAdd(Unit, Number,Date) กรณีที่ต้องการลบ เราจะใช้ Number เป็นค่าติดลบ Unit ที่ใช้ ให้ดูตาราง Unit ที่แสดงถัดไป	บวกวันปัจจุบันไปอีก 30 วัน DateAdd(“d”,30,Date()) ย้อนวันไป 2 สัปดาห์ที่ผ่านมา DateAdd(“ww”,-2,Date()) เวลาอีก 30 นาทีข้างหน้า DateAdd(“n”,30,Time())
DateDiff	ใช้ในการหาค่าความแตกต่างของช่วงเวลาที่กำหนด โดยกำหนดหน่วยที่ต้องการทราบ กับตัว วันเวลา 2 ตัวที่ต้องการหาความแตกต่าง DateDiff(Unit,DateTime1,DateTime2)	หาจำนวนชั่วโมงที่ผ่านไปนับจากเช้าวันนี้ DateDiff(“H”,Date(),Now()) จำนวนวันที่เหลือก่อนถึงปี 2000 DateDiff("D", Date, DateValue("1/1/2000"))
DatePart	คล้ายกับ Format บน Date ใช้ในการหาค่าวันเวลา ตามหน่วยที่เรากำหนด โดย Return ค่าเป็น Variant แบบ Integer ( Format ให้ค่าเป็น String ) DatePart(Unit,DateTime)	Msgbox(DatePart(“D”,Date())) Msgbox(DatePart(“H”,Date()))

ตารางแสดงการกำหนดหน่วยของวันเวลา

หน่วย	คำอธิบาย
yyyy	ปี
q	ไตรมาส
m	เดือน
y	วันในหนึ่งปี
d	จำนวนวัน
w	วันที่ของสัปดาห์ ( WeekDay )
ww	สัปดาห์ที่
h	ชั่วโมง
n	นาที
s	วินาที

กลุ่ม Function ดึงข้อมูล

ใช้ในการเปิดฐานข้อมูล แล้วดึงค่าตามที่กำหนด เรียกอีกอย่างว่า Domain Aggregate Function กลุ่มFunction นี้ จะคล้ายกับการใช้ Query แต่ทำเป็น Function เพื่อใช้ในการดึงค่า และ Return ค่า แบบเป็น Function เช่นการดึงชื่อลูกค้าจากฐานข้อมูล โดยค้นหารหัสเป็นต้น การใช้ Function นี้ อาจจะดูยุ่งยาก และต้องเข้าใจการต่อ String เป็นอย่างดี

โดยปกติ Function นี้ จะประกอบด้วย 3 ส่วน  คือ

1.        ส่วนแรก ( Field /  Expression ) เป็นค่าที่ต้องการอ่านซึ่งอาจเป็นชื่อ Field หรือ Expression ที่เราเคยทำใน Query

2.        ส่วนที่ 2 ( Domain- Table / Query ) เป็นชื่อ Table หรือ Query ที่ต้องการเปิดข้อมูล

3.        ส่วนที่ 3 ( Criteria ) เป็น Option ใช้ระบุ Criteria ที่ต้องการค้นหา การระบุในส่วนนี้ จะบอกเป็นเงื่อนไข เหมือนที่เรากำหนด IF

วิธีกำหนดค่า

การกำหนดค่าลงในทั้ง 3 ส่วน จะต้องระบุเป็น String ตัวอย่างเช่นการอ่านชื่อสินค้า ( ProductName ) จาก Table Products จากรหัสของสินค้า ( ProductID )

Dim tmpStr as String

TmpStr = Dlookup(“ProductName”,”Products”,”ProductID = 19”)

Msgbox(TmpStr)

จะเห็นว่าทั้ง 3 Input จะต้องมีเครื่องหมาย “ คลุม เพื่อให้ VBA ทราบว่าส่วนที่อยู่ระหว่าง “ เป็นค่าที่ต้องการส่งผ่าน  ถ้าค่าที่ต้องการส่งมาจากตัวแปร  จะเป็นตาม ตย นี้

Dim tmpStr As String

Dim strScope As String

Dim strField As String

Dim strWhere As String

Dim X

X = 19

strScope = "ProductName"

strField = "Products"

strWhere = "ProductID =" & X

tmpStr = DLookup(strScope, strField, strWhere)

MsgBox (tmpStr)

การ Where

ตัวอย่างด้านบนเป็นการ Where โดยใช้ ProductID ซึ่งเป็นตัวเลข  ถ้ากรณีที่ Field ที่กำลัง Where เป็น String จะต้องกำหนดขอบเขต String ด้วย ‘  แทนการใช้ “ แบบปกติ ตัวอย่างเช่น

    Dim tmpStr As String

    tmpStr = DCount("Country", "Suppliers", "Country = 'USA'")

    MsgBox (tmpStr)

เราสามารถกำหนดเงื่อนไขเพิ่มเติมบนส่วน Where ได้เหมือนการกำหนดเงื่อนไขทั่วไป เช่นการใช้ AND, OR, NOT สามารถใช้การเปรียบเทียบได้ทุกแบบ เช่น >=, >,< ,<=, Like  เป็นต้น

    Dim tmpStr As String

    tmpStr = DCount("Country", "Suppliers", "Country = 'USA' and SupplierID > 10")

    MsgBox (tmpStr)

ถ้าเราไม่กำหนดเงื่อนไขที่ 3 ก็เท่ากับไม่มีการ Where ใดใด   Scope ของข้อมูลก็จะเป็นทั้ง Table หรือทั้ง query  กรณีคำสั่ง Dlookup จะไม่สามารถบอกได้ว่า ค่าที่ได้ จะเป็นค่าใด หากมีข้อมูลมากกว่า 1 Record ในการ Lookup ครั้งนั้น

Function	คำอธิบาย	ตัวอย่าง
Dlookup	สำหรับการดึงค่าโดยตรง	Dlookup(“ProductName”,”Products”,”ProductID = 19”)
Dsum	หาค่ารวมของ Field ที่กำหนด	Dsum(“UnitInStock”,”Products”,”ProductID >10”
Dcount	นับจำนวนของ Field ที่กำหนด ( ไม่รวมค่า Null )	DCount(“UnitInStock”,”Products”,”ProductID >10”
Davg	หาค่าเฉลี่ยของ Field ที่กำหนด	Davg(“UnitPrice”,”Products”,”ProductID >10”
Dmin	หาค่าต่ำสุด	Dmin(“UnitInStock”,”Products”,”ProductID >10”
Dmax	หาค่าสูงสุด	Dmax(“UnitInStock”,”Products”,”ProductID >10”

ทดลอง Function

            Function ที่กล่าวมาจะดูเยอะซะหน่อยนะครับ   อยากให้ผู้อ่านลองทดลอง ใช้ Function บน Database ที่ท่านมี เพื่อสร้างความคุ้นคยไว้ก่อน  โดยการทำ Sub หรือ ใช้ Debug Windows ( Ctrl+g) ก็ได้ครับ  ก่อนจะจบตอนนี้ไป เราลองมาใช้ Function Dlookup  ไปปรับแก้ไข หน้า Login Password ที่ได้ทำไว้ในฉบับที่แล้ว แต่คราวนี้สั่งให้ไปตรวจ Login กับ Password ว่าตรงกับในฐานข้อมูลที่เตนียมไว้หรือไม่

ภาพแสดงการแก้ไขโปรแกรมจากฉบับที่แล้วมาใช้ FUNCTION DLOOKUP ในการอ่านค่าจากตาราง User