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

Question

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?

Fionnuala · Accepted Answer · 2012-09-04 20:23:50Z

1

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.

answered Sep 4, 2012 at 20:23

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HansUp · Accepted Answer · 2012-09-05 05:40:55Z

1

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.)

answered Sep 5, 2012 at 5:40

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related