[VB.NET] การค้นหาข้อมูลด้วยการใช้เงื่อนไขของวันที่ใน MS Access

thongkorn

เรื่องของวันที่เป็นปัญหาอันน่าปวดขมับพอสมควรสำหรับคนที่พึ่งเริ่มต้นใช้งาน MS Access แต่แท้ที่จริงแล้วแอดมินขอบอกเลยว่ามันไม่ได้ยากหรอกครับ หากเราเข้าใจหลักการ (ง่ายๆ) ของมัน ซึ่งไม่ว่าเราจะตั้งค่าอย่างไรก็ตามที่อยู่ใน Control Panel --> Region/Language ให้เป็นปี พ.ศ. หรือ ปี ค.ศ. ก็ยังมีวิธีคิดแบบเดิม ...

รูปแบบการเก็บข้อมูลชนิดวันที่ใน MS Access คือ เดือน/วันที่/ปี ค.ศ. เช่น 1/30/2019 คือ วันที่ 30 เดือน 1 ปี ค.ศ.2019 ...


หลักการคิด ... ตรวจสอบก่อนว่าปีที่อยู่ในเครื่องเป็นปี ค.ศ. หรือ ปี พ.ศ. แอดมินคิดง่ายๆคือ เอา 4 หลักท้ายสุดของวันที่ซึ่งจะเป็นค่าปี (yyyy) มาคิด หากได้ค่ามากกว่า 2500 มันคือปี พ.ศ. (เช่น 2562) หากน้อยกว่า (เช่น 2019) เป็นปี ค.ศ. กรณีที่เป็นปี พ.ศ. ให้เอา 543 มาลบออกก่อน แล้วทำการจัดรูปแบบใหม่ให้ตรงกับฟอร์แมตของ MS Access นั่นคือ เดือน/วันที่/ปี ค.ศ. ... อนึ่ง!!! การตั้งค่าเงื่อนไขไว้ที่ 2500 หากลบจาก 2019 คุณๆทั้งหลายจะสามารถใช้งานโปรแกรมได้ไปถึงอีก 481 ปี ไม่ต้องห่วงหรอกครับว่าจะเกิดบั๊กในอนาคต 5555+ ...


มาดูโค้ดฉบับเต็มกันเถอะ ...

1. #Region "ABOUT"
   
2. ' / --------------------------------------------------------------------------
   
3. ' / Developer : Mr.Surapon Yodsanga (Thongkorn Tubtimkrob)
   
4. ' / eMail : thongkorn@hotmail.com
   
5. ' / URL: http://www.g2gnet.com (Khon Kaen - Thailand)
   
6. ' / Facebook: https://www.facebook.com/g2gnet (For Thailand)
   
7. ' / Facebook: https://www.facebook.com/commonindy (Worldwide)
   
8. ' / Purpose: How to search data by date with VB.NET (2010).
   
9. ' / Microsoft Visual Basic .NET (2010) + MS Access
   
10. ' /
    
11. ' / This is open source code under @CopyLeft by Thongkorn/Common Tubtimkrob.
    
12. ' / You can modify and/or distribute without to inform the developer.
    
13. ' / --------------------------------------------------------------------------
    
14. #End Region
    
15. 
    
16. Imports System.Data.OleDb
    
17. Imports Microsoft.VisualBasic
    
18. 
    
19. Public Class frmSearchByDate
    
20.     '// Declare variable one time but use many times.
    
21.     Public Conn As OleDbConnection
    
22.     Public Cmd As OleDbCommand
    
23.     Public DS As DataSet
    
24.     Public DR As OleDbDataReader
    
25.     Public DA As OleDbDataAdapter
    
26.     Public strSQL As String '// Major SQL
    
27.     Public strStmt As String    '// Minor SQL
    
28. 
    
29.     '// Data Path
    
30.     Public strPathData As String = MyPath(Application.StartupPath)
    
31. 
    
32.     Public Function ConnectDataBase() As System.Data.OleDb.OleDbConnection
    
33.         strPathData = MyPath(Application.StartupPath) & "Data"
    
34.         '"Provider = Microsoft.Jet.OLEDB.4.0;"
    
35.         Dim strConn As String = _
    
36.             "Provider = Microsoft.ACE.OLEDB.12.0;"
    
37.         strConn += _
    
38.             "Data Source = " & strPathData & "Sample.accdb"
    
39. 
    
40.         Conn = New OleDb.OleDbConnection(strConn)
    
41.         ' Create Connection
    
42.         Conn.ConnectionString = strConn
    
43.         ' Return
    
44.         Return Conn
    
45.     End Function
    
46. 
    
47.     ' / --------------------------------------------------------------------------------
    
48.     ' / Get my project path
    
49.     ' / AppPath = C:\My Project\bin\debug
    
50.     ' / Replace "\bin\debug" with ""
    
51.     ' / Return : C:\My Project\
    
52.     Function MyPath(AppPath As String) As String
    
53.         '/ MessageBox.Show(AppPath);
    
54.         AppPath = AppPath.ToLower()
    
55.         '/ Return Value
    
56.         MyPath = AppPath.Replace("\bin\debug", "").Replace("\bin\release", "").Replace("\bin\x86\debug", "")
    
57.         '// If not found folder then put the \ (BackSlash) at the end.
    
58.         If Microsoft.VisualBasic.Right(MyPath, 1) <> "" Then MyPath = MyPath & ""
    
59.     End Function
    
60. 
    
61.     Private Sub frmSearchByDate_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
    
62.         If Conn.State = ConnectionState.Open Then Conn.Close()
    
63.         Me.Dispose()
    
64.         Application.Exit()
    
65.     End Sub
    
66. 
    
67.     Private Sub frmSearchByDate_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    
68.         Conn = ConnectDataBase()
    
69.         '// Load Data
    
70.         Call RetreiveData(False)
    
71.     End Sub
    
72. 
    
73.     ' / --------------------------------------------------------------------------------
    
74.     '// Data Retrieval if blnSearchDate is True, it's search data by date.
    
75.     Private Sub RetreiveData(Optional ByVal blnSearchDate As Boolean = False)
    
76.         '// Show all data.
    
77.         If Not blnSearchDate Then
    
78.             strSQL = _
    
79.                 "SELECT * FROM Sample ORDER BY PrimaryKey"
    
80. 
    
81.             '// Between Date condition.
    
82.         Else
    
83.             strSQL = _
    
84.                 "SELECT * FROM Sample " & _
    
85.                 " WHERE " & _
    
86.                 " (CDate(Sample.DateField) BETWEEN " & _
    
87.                 "#" & FormatMyDate(dtpDateBegin.Value) & _
    
88.                 "#" & " AND " & _
    
89.                 "#" & FormatMyDate(dtpDateEnd.Value) & "#)" & _
    
90.                 " ORDER BY PrimaryKey "
    
91.         End If
    
92.         '// START
    
93.         Try
    
94.             '// Open connection
    
95.             If Conn.State = ConnectionState.Closed Then Conn.Open()
    
96.             DA = New OleDb.OleDbDataAdapter(strSQL, Conn)
    
97.             DS = New DataSet
    
98.             DA.Fill(DS, "Sample")
    
99.             Me.dgvData.DataSource = DS.Tables("Sample").DefaultView
    
100.             Label1.Text = "Total: " & DS.Tables("Sample").Rows.Count & " Records."
     
101.             Call SetupDataGridView(dgvData)
     
102.             DS.Dispose()
     
103.             DA.Dispose()
     
104.             Conn.Close()
     
105.         Catch ex As Exception
     
106.             MessageBox.Show(ex.Message)
     
107.         End Try
     
108.     End Sub
     
109. 
     
110.     Private Sub btnSearchDate_Click(sender As System.Object, e As System.EventArgs) Handles btnSearchDate.Click
     
111.         Call RetreiveData(True)
     
112.     End Sub
     
113. 
     
114.     ' / --------------------------------------------------------------------------------
     
115.     '// Adjust Date Format in MS Access.
     
116.     Function FormatMyDate(ByVal MyDate As Date) As String
     
117.         FormatMyDate = String.Empty
     
118.         Dim sYear As Integer
     
119.         Dim sDay As Integer = Microsoft.VisualBasic.Left(Format(MyDate, "dd/MM/yyyy"), 2)
     
120.         Dim sMonth As Integer = Microsoft.VisualBasic.Mid(Format(MyDate, "dd/MM/yyyy"), 4, 2)
     
121.         Dim y As Integer = Microsoft.VisualBasic.Right(Format(MyDate, "dd/MM/yyyy"), 4)
     
122.         '// If more than 2500, it is the year of the Buddhist era.
     
123.         If y > 2500 Then
     
124.             sYear = CStr(y - 543)
     
125.             '// Month/Date/Year in AD.
     
126.             FormatMyDate = CStr(Microsoft.VisualBasic.Right("00" & CStr(sMonth), 2) & "/" & Microsoft.VisualBasic.Right("00" & CStr(sDay), 2) & "/" & "20" & Microsoft.VisualBasic.Right("00" & CStr(sYear), 2))
     
127.         Else
     
128.             FormatMyDate = CStr(Microsoft.VisualBasic.Right("00" & CStr(sMonth), 2) & "/" & Microsoft.VisualBasic.Right("00" & CStr(sDay), 2) & "/" & Microsoft.VisualBasic.Right("00" & CStr(y), 2))
     
129.         End If
     
130.     End Function
     
131. 
     
132.     Private Sub SetupDataGridView(ByRef DGV As DataGridView)
     
133.         With DGV
     
134.             .RowTemplate.Height = 22
     
135.             .AllowUserToOrderColumns = True
     
136.             .AllowUserToDeleteRows = False
     
137.             .AllowUserToAddRows = False
     
138.             .ReadOnly = True
     
139.             .MultiSelect = False
     
140.             .SelectionMode = DataGridViewSelectionMode.FullRowSelect
     
141.             .Font = New Font("Tahoma", 8)
     
142.             .AlternatingRowsDefaultCellStyle.BackColor = Color.Orange
     
143.             .DefaultCellStyle.SelectionBackColor = Color.LightSkyBlue
     
144. 
     
145.             '.AlternatingRowsDefaultCellStyle.BackColor = Color.LightYellow
     
146.             '.DefaultCellStyle.SelectionBackColor = Color.LightBlue
     
147. 
     
148.             '/ Auto size column width of each main by sorting the field.
     
149.             .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
     
150.             .AutoResizeColumns()
     
151.             '//
     
152.         End With
     
153.     End Sub
     
154. 
     
155. End Class

คัดลอกไปที่คลิปบอร์ด

ดาวน์โหลดโค้ดต้นฉบับ VB.NET (2010) ได้ที่นี่ ...

puklit

ขอบพระคุณอาจารย์ทองก้อนเป็นอย่างสูงครับ
ผมขออนุญาตอาจารย์ทองก้อนแบ่งปันให้สมาชิกทุกท่านในอีกรูปแบบหนึ่ง
นั่นก็คือการเรียกใช้คลาสใน .Net (CultureInfo Class & DateTimeFormatInfo Class)
ทุกท่านสามารถดูเพิ่มเติมได้ที่
CultureInfo Class => https://docs.microsoft.com/en-us/dotnet/api/system.globalization.cultureinfo?view=netframework-4.7.2
DateTimeFormatInfo Class => https://docs.microsoft.com/en-us/dotnet/api/system.globalization.datetimeformatinfo?view=netframework-4.7.2

ช่วยจัดการเกี่ยวกับวันที่ , สกุลเงิน ให้อยู่ในรูปแบบสอดคล้องกับการตั้งค่า Region/Language
(อธิบายให้เข้าใจง่ายขึ้นคือ เช่น ได้ค่าปี พ.ศ. มาจาก DatetimePicker = 2019
กรณีที่ผมกำหนด CultureInfo = "th-TH" ผลลัพธ์ที่ได้คือ เลข พ.ศ. = 2562

กรณีที่ผมกำหนด CultureInfo = "en-US" ผลลัพธ์ที่ได้คือ เลข ค.ศ. = 2019 เป็นต้น)

ก่อนอื่น ต้อง Import Namespace: Imports System.Globalization เข้ามาใน Project เพื่ออ้างอิงการใช้ CultureInfo Class & DateTimeFormatInfo Class


มาดูตัวอย่างโค้ดกันครับ
วิธีที่ 1 : ผมจะเขียนให้มันยืดหยุ่น เพื่อกำหนด CultureInfo และ Date_Format ได้
เวลานำไปใช้งาน เช่น
พ.ศ.
FormatMyDate(วันที่,"th-TH","MM/dd/yyyy") ผลลัทธ์ที่ได้ คือ พ.ศ. => รูปแบบวันที่ เดือน/วัน/ปี (สำหรับ MS-Access)
FormatMyDate(วันที่,"th-TH","dd/MM/yyyy") ผลลัทธ์ที่ได้ คือ พ.ศ. => รูปแบบวันที่ วัน/เดือน/ปี
FormatMyDate(วันที่,"th-TH","dd-MM-yyyy") ผลลัทธ์ที่ได้ คือ พ.ศ. => รูปแบบวันที่ วัน-เดือน-ปี
FormatMyDate(วันที่,"th-TH","yyyy-MM-dd") ผลลัทธ์ที่ได้ คือ พ.ศ. => รูปแบบวันที่ ปี-เดือน-วัน (สำหรับ SQL Server)
FormatMyDate(วันที่,"th-TH","yyyy") ผลลัทธ์ที่ได้ คือ พ.ศ. => รูปแบบวันที่ ปี (เช่น 2562)


ค.ศ.
FormatMyDate(วันที่,"en-US","MM/dd/yyyy") ผลลัทธ์ที่ได้ คือ ค.ศ. => รูปแบบวันที่ เดือน/วัน/ปี (สำหรับ MS-Access)
FormatMyDate(วันที่,"en-US","dd/MM/yyyy") ผลลัทธ์ที่ได้ คือ ค.ศ. => รูปแบบวันที่ วัน/เดือน/ปี
FormatMyDate(วันที่,"en-US","dd-MM-yyyy") ผลลัทธ์ที่ได้ คือ ค.ศ. => รูปแบบวันที่ วัน-เดือน-ปี
FormatMyDate(วันที่,"en-US","yyyy-MM-dd") ผลลัทธ์ที่ได้ คือ ค.ศ. => รูปแบบวันที่ ปี-เดือน-วัน (สำหรับ SQL Server)
FormatMyDate(วันที่,"en-US","yyyy") ผลลัทธ์ที่ได้ คือ ค.ศ. => รูปแบบวันที่ ปี (เช่น 2019)



หมายเหตุ หากต้องการใช้ร่วมกับ SQL Server ตรง Return ให้ลบ String "#" ตามที่ผมแต้มสีแดงออก
Return "#" & MyDate.ToString(Date_Format, dtfi) & "#"

1. '// Return Date by CultureInfo Class(Ex. th-TH / en-US) and Date format(Ex. MM/dd/yyyy or dd/MM/yyyy)
   
2.     Function FormatMyDate(ByVal MyDate As Date, ByVal CultureInfo_Name As String, ByVal Date_Format As String) As String
   
3. 
   
4.         Dim dtfi As DateTimeFormatInfo = CultureInfo.GetCultureInfo(CultureInfo_Name).DateTimeFormat
   
5.         Return "#" & MyDate.ToString(Date_Format, dtfi) & "#"
   
6. 
   
7.     End Function

คัดลอกไปที่คลิปบอร์ด

วิธีที่ 2 : หากท่านใดต้องการใช้งานฟังก์ชั่นแบบตรง ๆ ไม่ต้องการกำหนดค่าพารามิเตอร์จะเขียนแบบนี้
เช่น ต้องการรูปแบบวันที่ => เดือน/วัน/ปี (โดยปีอยู่ในรูปแบบของ ค.ศ.)

1. '// Return Date by CultureInfo Class with condition (CultureInfo= en-US and Dateformat = MM/dd/yyyy)
   
2.     Function FormatMyDate2(ByVal MyDate As Date) As String
   
3. 
   
4.         Dim dtfi As DateTimeFormatInfo = CultureInfo.GetCultureInfo("en-US").DateTimeFormat
   
5.         Return "#" & MyDate.ToString("MM/dd/yyyy", dtfi) & "#"
   
6. 
   
7.     End Function

คัดลอกไปที่คลิปบอร์ด

ส่วนการใช้งานคิวรี่ ผมใช้ฟังก์ชั่นในวิธีที่ 1 เขียนดังนี้

1. strSQL = "SELECT * FROM Sample " &
   
2.                      " WHERE " &
   
3.                      " DateField BETWEEN " &
   
4.                      FormatMyDate(dtpDateBegin.Value, "en-US", "MM/dd/yyyy") &
   
5.                      " AND " &
   
6.                      FormatMyDate(dtpDateEnd.Value, "en-US", "MM/dd/yyyy") &
   
7.                      " ORDER BY PrimaryKey "

คัดลอกไปที่คลิปบอร์ด

สุดท้ายนี้ ผมอยากแบ่งปันในสิ่งที่ผมได้จากอาจารย์มาแบ่งปันให้กับสมาชิกหลาย ๆ ท่าน
เพื่อให้ท่านใดที่สนใจได้นำไปศึกษาต่อยอดแล้วกลับมาแบ่งปันกันต่อไปแบบไม่สิ้นสุดครับ
ไม่ว่าจะเป็นวิธีที่อาจารย์ถ่ายทอดให้ (ท่านก็ต้องรู้จักวิธีคิด ถึงจะได้ผลลัพธ์) หรือวิธีที่ผมใช้ (ท่านก็ต้องรู้จักคลาสใน .Net)
สิ่งที่เหมือนกันคือต้องเรียนรู้และเข้าใจใช้งานครับ

ท่านสามารถดาวน์โหลดตัวอย่างโปรแกรมได้ตามด้านล่างนี้

downrung

ผมก็ชอบสั้นๆ เหมือนกันครับ
sSQL = "Select * From QueryDispenseDay Where Ddate= #" & DateTimePicker1.Value.ToString("M/dd/yyyy", New System.Globalization.CultureInfo("en-US")) & "#"

thongkorn

ขอบคุณทั้ง 2 ท่านที่เข้ามาให้คำแนะนำเพิ่มเติมครับ ... ผมลืมบอกกับทุกๆท่านไปว่าฟังค์ชั่นที่ผมเขียนให้ดูนั้น ผมเอามาจาก VB6 ที่ผมใช้งานประจำก่อนหน้าจะย้ายมา VB.NET ครับผม ความหมายที่ผมสื่อออกมา คือการไม่เสียเวลาไปทำการค้นหาคำสั่งที่มันมีอยู่เยอะแยะมากมายใน Dot Net เพราะกว่าที่จะหามันเจอ อาจจะใช้เวลานานมากเกินไป แต่ใช้กลวิธีในการคิดแก้ปัญหาแบบพื้นฐานแทนครับ ...

1.     '// Adjust Date Format in MS Access.
   
2.     Function FormatDateGlobal(ByVal MyDate As Date) As String
   
3.         FormatDateGlobal = MyDate.ToString("MM/dd/yyyy", New System.Globalization.CultureInfo("en-US")) ' th-TH
   
4.     End Function
   

คัดลอกไปที่คลิปบอร์ด

anuyut1995

ขอบคุณทุกความรู้นะครับ

sivan2532

ผมติดปัญหานี้นานอยู่พอสมควร ลองแก้อะไรหลายๆอย่างให้มันเข้ากับระบบงานของผม ก็ไม่สามารถทำได้สักทีจนเริ่มเซ็งแต่ก็พยายามทุกครั้งจนมาเจอวิธีขั้นตอนในทีนี้ผมลองเอามาปรับใช้จนกับระบบงานของผมจนประสบความสำเร็จ ดีใจน้ำตาไหลพราก ขอบคุณความรู้ในที่นี้ครับ

ooddog

อาจารย์ครับ ถ้าต้องการให้ค้นหา และ แสดงเฉพาะ field ที่ต้องการ มีแนวทางอย่างไรบ้างครับ

thongkorn

ooddog ตอบกลับเมื่อ 2021-5-10 19:04
อาจารย์ครับ ถ้าต้องการให้ค้นหา และ แสดงเฉพาะ field ที่ต้องการ มีแนวทางอย่างไรบ้างครับ

ไม่ค่อยเคลียร์คำถามครับ หากเราต้องการเฉพาะฟิลด์ที่ต้องการ ก็แค่ SELECT ฟิลด์นั้นๆออกมาเลย เช่น SELECT Name FROM TableName WHERE คือเงื่อนไข ... แต่เงื่อนไขการค้นหาข้อมูลแบบ String กับ Number จะมีความแตกต่างกัน เช่น String จะเป็นแบบ WHERE Name = 'ทองก้อน' ส่วน Number ก็จะเป็น WHERE PK = 123 ... ประมาณนี้ครับ

sontha2499

ขอบคุณ สำหรับ ข้อมูล น่ะครับ