the most weird thing just happened ... please help... thanks!

[raybristol]

have a table call 'ClinicTimeTable' there is a column call 'ClinicDate', another column called 'DrID', different row could have same 'ClinicDate' and 'DrID', so I wanna to count how many Drs in a particular day, I have following code, but it acts so werid, it onlys work for some dates! say if I put 02/02/2006 then I can get the number of DrID by observing 'count', however, if I put 01/02/2006 like below, count is 0! but there is some records with 02/02/2006 (I open the table directly in Access), how can this happening? how can it only partly works? it will make more sense if it don't work at all.

Dataset ds = new Dataset();
query = "SELECT DISTINCT DrID FROM ClinicTimeTable WHERE ClinicDate = #01/02/2006#" ;
OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, oleDbConnection1 );
myAdapter.Fill ( ds );
int count = dds.Tables[0].Rows.Count;

any helps will be so appreciated! I am going mad at this problem!



by the way, i just tested that if any day in Jan 2006 is fine, say if i search 23/02/2006 or 22/02/2006 is fine... can't imagine why...

 

[Ron_dK]

You might try changing your query to :

query = "SELECT * DrID FROM ClinicTimeTable WHERE ClinicDate = #01/02/2006#" ;
OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, oleDbConnection1 );
myAdapter.Fill ( ds );
int count = dds.Tables[0].Rows.Count;

 

[WindSailor]

I have some experience with DataSets, usually when I try a SQL string I 'preview' it with Access in a 'test' query to see if I can get the desired results and then port it over to .Net.
Bind a datagrid to your dataset and see if you get your desired results.

 

[RV]

raybristol,

VBA uses the US date format (mm/dd/yyyy), I guess that's your "problem".
Use a user defined function to convert your dats into US date format.
See for instance:

http://www.access-programmers.co.uk/forums/showthread.php?t=100833

RV