Select Returns No Row When There Is An Empty Value in Select Column

Hello, I want to take all rows from Excel data only where the “Access Date” Column is today.
This is my code:
dt1.Select(string.Format("[Access Date] = ‘"+todayDate+"’")) where todayDate was “2018/01/25”.

This works fine with Data 1 (returned 1 row), but not with Data 2 (return 0 row).
Data 1:
Data 2 (notice Data 2 has some rows with empty Access Date):

Could anyone suggest me:

  1. Why this happened?
  2. How to handle this?

Sample .xaml and Excel files:
Select (18.1 KB)

Thank you!

@whyyouandi, you can try this,

dt1.Select(’'Convert(Access Date, System.String) = '"“yyyy/MM/dd”)+" ’ ")

Dominic :slight_smile:

Hello @Dominic, tried your code (changed it a bit since it threw an error) to
dt2.Select(“Convert([Access Date], System.String) = '”“yyyy/MM/dd”)+"’")

but this time it returns 0 row for Data 1, and 0 row for Data 2 :frowning:


1.If you ran your code on jan 26 , with same excel sheet what you have posted then it will return 0 rows only, because the excel sheet is not having today’s date…
2.Better add the current day date into excel sheet and run once…



I don’t understand your problem fully. It sounds like you want to take only the rows that are todayDate from Data 2?

I will use the .Where() method cause I feel like its syntax is easier to read.
In this case, however, you must only compare items that are dates.

dt2.AsEnumerable.Where(Function(row) If( IsDate(row("Access Date").ToString), CDate(row("Access Date").ToString) = CDate(todayDate), False) ).CopyToDataTable

You can also replace .CopyToDataTable to .ToArray if you would like a list of rows to process from your original table.

So logically speaking, my example is taking all rows “Where” item is a date and is equal to todayDate as date and convert to another datatable (or to array of rows if you use .ToArray).

Give that a try if you are still having problems.


1 Like

Hello @MAHESH1, thank you for your comment!
But yes, I remember to change the data to the current date before I tried the code, but it returned me zero :open_mouth:

Thank you @ClaytonM for your solution, it works.
So it seems like what was missing was I must compare the Access Date only when it contains date.
Thanks for your help, really appreciate it.