Get Records By Date

[pendragon]

I have a program that asks the user for a date and then returns a recordset of Orders that have an order date greater than or equal to the date entered.

 

In the SQL Startement I have 'WHERE OrderDate >= ' & UserDate, this does not work .

 

Can anyone tell me what the SQL statement should be.

 

Thanks

[PlausiblyDamp]

What error does it give?

What is the code you are using to build the SQL string (the problem may be down to not adding enough single quotes around the relevant parameters)

[pendragon]

Sorry, a bit more info would be a good idea as 'this does not work' doesn't really say much.

 

To start I am still using ADO 2.7 as not had time to fully learn ADO.NET yet, I also use the Microsoft Masked Edit Control to ask for the dates.

 

I have an access database with orders in it for this year, and I build the SQL statement my self.

 

The Code looks like this

 

sSQL = "SELECT * FROM POPHeader WHERE POPOrdDate >= " & me.mebStartDate.CtlText & " ORDER BY POPOrdNo"

 

this gives me

 

'SELECT * FROM POPHeader WHERE POPOrdDate >= 31/01/2003 ORDER BY POPOrdNo'

 

What I get returned are all the orders in the file, when I started to write this I was asking for a Start & End date that looked like this

 

sSQL = "SELECT * FROM POPHeader WHERE POPOrdDate >= " & me.mebStartDate.CtlText & " AND POPOrdDate <= " & me.mebEndDate.CtlText & " ORDER BY POPOrdNo"

 

This gives me

 

'SELECT * FROM POPHeader WHERE POPOrdDate >= 01/01/2003 AND POPOrdate <= 31/01/2003 ORDER BY POPOrdNo'

 

If I put single quotes in I get the following error

 

An unhandled exception of type 'System.Exception' occurred in microsoft.visualbasic.dll

 

Additional information: Data type mismatch in criteria expression.

 

Any Help would be appreciated

Thanks

[pendragon]

Forgot to say that the search with a start and end date returned nothing

 

Thanks

[pendragon]

Sorted.

 

You have to use the DateValue Function in the sql statement so that it takes into account the International settings