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

Help
, ,
1

Scenario:
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”.

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

Question
Could anyone suggest me:

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

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

Thank you!

2

@whyyouandi, you can try this,

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

Regards,
Dominic :slight_smile:

3

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

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

4

@whyyouandi

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…

Regards,
Mahesh

5

Hi,

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() vb.net 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.

Regards.

1 Like
6

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:

7

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.