DateTimePicker problems

[ComITSolutions]

If I do a debug.print (DateTime1.text), the date is in UK format i.e 11/05/2009 but when these variables are used in a Select statement, the dates returned seem to be in US format e.g. selecting dates between 01/04/2009 and 7/05/2009 selects dates between 04/01/2009 and 05/07/2009 when the results are displayed in the DataGridView!

Getting DateTime variables:

SDate = sDateTime.Text
FDate = fDateTime.Text

Select Statement:

Dim da As New OleDb.OleDbDataAdapter("Select * From Sanctions Where [Group] Like '%EN%' AND [Date] BETWEEN #" & SDate & "# AND #" & FDate & "# AND [Description] <> 'Merits' ", cnDept)

I think its problem with query not with datetimepicker, you can try as below.

Dim da As New OleDb.OleDbDataAdapter("Set dateformat DMY Select * From Sanctions Where [Group] Like '%EN%' AND [Date] BETWEEN #" & SDate & "# AND #" & FDate & "# AND [Description] <> 'Merits' ", cnDept)

[brjames32]

ComITSolutions - thanks for the reply

I get the error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE

pointing to your line of code.

Any ideas?

[ComITSolutions]

Which database are you using? SQL Server, MSAccess,MYSql ...?
You have not mentioned... IF you are using SQL server it should work!

[brjames32]

Hi

Sorry - I am using MSAccess

[ComITSolutions]

I still feel, it is nothing to do with Datetimepicker. It is something related to windows regional longdate, shortdate setting or Ms Access.

I would like to suggest following points to try

1) Try to run the query in msaccess directly and check the result set.

Select * From Sanctions Where [Group] Like '%EN%' AND [Date] BETWEEN #01/04/2009# AND 
#07/05/2009# AND [Description] <> 'Merits' "

2) I have observed one more peculiar thing while sending date value to MSaccess, Usually Date value is passed to access delimited by # i.e,
#01/04/2009#, When I tried to pass #31/05/2008#, I received error but when I send the date delimited with ' (single quote){'31/05/2008'}.
it worked.

I have little knowledge in MSAccess because I work only on SQL Server.
With my above experience I would like to suggest you to try sending date value delimited by " ' " instead "#"(this is just guess work).
I may be wrong also.

3) Instead of executing query directly, try create parametrized query(Stored Procedure) in your access database and call the same in Front end coding.

If possible Post Connection String.

[ComITSolutions]

Read this article, This may be useful to you.


http://www.codeguru.com/forum/showthread.php?t=316275

http://bytes.com/topic/access/insigh...eir-delimiters