SQL statement to create dataset where table date field is > than a datepicker.value

Blue_Steel
Blue_Steel used Ask the Experts™
on
Hi
  I'm new to VS2008 (an old VB6 programmer), and I'm really struggling with this. I've googled until I'm blue in the face with no joy.

I'm sure it's a very simple solution. I've spent hours with no luck.

Basically, I have a table that contains a date field (Access 2007 database, and I'm using VB.Net). I have a form with DateTimepicker on it for user selection.

I'm then trying to create a dataset with an SQL statement that looks to see if the table date field (FDOB1) is a later date than the DateTimepicker value.

Any help would be appreciated, as it's a bit of a showstopper at the moment (aren't they all!)

Al
sql = "SELECT NAME,SURNAME,FNAME1,FDOB1 FROM BTUC where FNAME1 <> '' and FDOB1 > " + "'" + DateTimePicker1.Value + "'"

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
if you are using sql serve follow below steps
1. Create sqlconnectiono object with connectionstring to connect to DB
2. Create sqldataadapter object. you can skip ponit 3 & 4 by using one of the constructor which allows direct sql and connection object.
3. Create sqlcommand object and set connection property
4. SelectCommand property of adapter object
5. Use fill  method of dataadapter and pass a dataset/datatable object to it

hope this helps :)

Author

Commented:
Hi and thanks for your reply.

 I did state that I'm using Access 2007, not SQL.

I changed the query slightly to ensure DateTimepicker1 was returning a short date. The Access field FDOB1 is a short date too. The new query is
 
  sql = "SELECT NAME,SURNAME,FNAME1,FDOB1 FROM BTUC WHERE FNAME1 <> '' AND FDOB1 > " + "'" + DateTimePicker1.Value.ToShortDateString() + "'"
 
 
 When I run the full query the error is {"Data type mismatch in criteria expression."}
 
 The Msgbox (which I use often to check any sql problems) shows  as SELECT NAMe,SURNAME,FNAME1,FDOB1 FROM BTUC WHERE FDOB1 <> '' AND FDOB1 > '25/01/2002'
 
 If I run the query SELECT NAME,SURNAME,FNAME1,FDOB1 FROM BTUC WHERE FDOB1 <> '' it works fine.
 
 As both FDOB1 and DateTimepicker1 are dates and not strings, I'm lost.

Application Development Manager
Commented:
sql = "SELECT NAME,SURNAME,FNAME1,FDOB1 FROM BTUC where FNAME1 <> '' and FDOB1 > '" & FORMAT(DateTimePicker1.Value,"MM/dd/yyyy") & "'"


Author

Commented:
Many thanks for this!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial