Searching in Excel using select in vb.net

Question

I have written the following code to search for records in Excel for a specific date range. This code works when the month of the date range is the same. But if the month if different, search returns empty records.

Ex: I have few records that were entered for the month of May and June. If I enter Jun 01 to Jun 30 as my start and end dates, I get the results. But if I enter May 01 as start and Jun 30 as end, I get 0 records. Not sure why. Any help is greatly appreciated.

Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\Folder1\Excel_Files\File1.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';" 
Dim connAccess As New System.Data.OleDb.OleDbConnection(connString) 
Dim sSql As String sSql = "Select [Employee ID], [Date], [Name], [Total Hours], [OT Hours] From [Sheet$] Where [Employee ID] = '" & EmpID & "' and Convert(DATE,[Date]) >= '" & StartDate & "' and [Date] <= '" & EndDate & "'"  
If connAccess.State = ConnectionState.Closed Then 	
connAccess.Open() 
End If  
Dim dtAdp As New OleDb.OleDbDataAdapter(sSql, connAccess) 
Dim bsMaster As New BindingSource 
Dim ds As New DataSet 
Dim dt1 As New DataTable 
ds.Tables.Add(dt1) 
bsMaster.DataMember = ds.Tables(0).TableName 
bsMaster.DataSource = ds 
grdData.DataSource = bsMaster 
dtAdp.Fill(dt1) 
dt1.Dispose() 
dt1 = Nothing 

Answer 1

Hi @Don75 ,

You used a date in a custom format, which can cause errors in the query. You can use the DateTime.ParseExact method to parse a custom-formatted date string and then insert it into the query statement.

Dim StartDateStr As String = "May 01"
Dim EndDateStr As String = "Jun 30"

Dim StartDate As DateTime = DateTime.ParseExact(StartDateStr, "MMM dd", CultureInfo.InvariantCulture)
Dim EndDate As DateTime = DateTime.ParseExact(EndDateStr, "MMM dd", CultureInfo.InvariantCulture)

Dim sSql As String = "SELECT [Employee ID], [Date], [Name], [Total Hours], [OT Hours] FROM [Sheet$] WHERE [Employee ID] = '" & EmpID & "' AND Convert(DATE, [Date]) >= '" & StartDate.ToString("yyyy-MM-dd") & "' AND [Date] <= '" & EndDate.ToString("yyyy-MM-dd") & "'"

Best Regards.

Jiachen Li

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I fixed this issue by creating another column called DateString and saving the date there as yyyyMMdd format. Now I use this in my sql and it's working fine.

sSql = "Select * from [Sheet$] Where [DateString] >= " & Val(Format(dtStartDate.Value, "yyyyMMdd")) & " and [DateString] <=" & Val(Format(dtEndDate.Value, "yyyyMMdd"))