CODEWITHSUNDEEP
ms-accessvb.net-2010ms-access-2003
saneep george
saneep george

Reputation: 21

DATETIME Format issue in VB.Net with MS Access database when using SQL statements

I am using vb.net and ms access 2003 as backend. I am not getting any records in my result when I'm using the sql query shown below. I'm trying to find records with a date lower (or coming before) the current date.

Dim Sql, CurrentDate as String

Dim TrDate as Date

Dim DataVal As Integer

TrDate=DateTime.Now

CurrentDate=Format(TrDate, "dd/MM/yyyy hh:mm:ss tt")

Sql="Select count(*) from Table1 where InvDate<=#" & CurrentDate & "#"

IF DBConOpen()=True

        cmdOledb.CommandText = sql
         cmdOledb.CommandType = CommandType.Text
         cmdOledb.Connection = ConOledb
         DataVal = cmdOledb.ExecuteScalar
         msgbox(DataVal)

End IF

For example if my current date is 01/09/2012 or 02/09/2012 or .. 12/09/2012 and the date in database is 01/09/2012 or 02/09/2012 or .. 12/09/2012. I should get the respective record count. But in this case im getting 0 records.

But if my current date is 01/09/2012 or 02/09/2012 or .. 12/09/2012 and the date in database is 20/08/2012 or 15/06/2012 or .. 30/05/2012. I am getting the correct record count.

The date format I defined in MS access table is 'General Date'. Is anyone able to spot what is wrong?

Upvotes: 2

Views: 12985

Answers (2)

HansUp
HansUp

Reputation: 97101

Unless you need to determine your Date/Time value in Dot.Net before submitting the query, you could make use of the Access db engine's Now() function.

Sql="Select count(*) from Table1 where InvDate<=Now()"

That approach avoids issues about formatting and delimiters.

If you want just the date without the current time, use Date() instead of Now(). (Actually, Date() will return midnight as the time component.)

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91316

You should use:

CurrentDate=Format(TrDate, "yyyy/MM/dd hh:mm:ss tt")

Access needs an unambiguous date.

You might like to remove the time element as well.

Upvotes: 1

Related Questions