Date format in access and vb 2008

[sofiashaharom]

Hi

The purpose of my system is to calculate the number of motors (stored in a microsoft access table) that is being sent for repair according to the months. This information will then have to be displayed in a form developed in vb2008.

Below are the coding used:

Public Class FormReportMonthlyOverhaul
Dim connstr As String = "Provider= \..\Database.mdb"
Dim rowIndex As Integer = 0

Private Sub FormReportMonthlyOverhaul2_Load(..) Handles Me.Load

Dim sqlJan As String = "SELECT * FROM MotorTable WHERE Start_Date >'" & Format$(2009 / 12 / 31, "dd mm yyyy") & "'AND Start_Date < '" & Format$(2010 / 2 / 1, "dd mm yyyy") & "'"

Dim countJan As Integer
Dim dtJan As New DataTable
Dim daJan As New OleDb.OleDbDataAdapter(sqlJan, connstr)

daJan.Fill(dtJan)
daJan.Dispose()
daJan.Update(dtJan)

countJan = CInt(dtJan.Rows.Count)
lblJan.Text = countJan

End Sub
End Class


The Start_Date column in the MotorTable db is of 'Text' format. I have a feeling that the there's something wrong with the sql. That is why it returns 0 value.

However, i just don't know how to get it done..Really appreciate a helping hand..Thank you in advance!

[Stewart Ross]

Hi. Your main problem is that the start date, as you have mentioned, is not a date at all - it's text. Internally, dates are represented as whole numbers counting up (or down) from a fixed reference point. This is quite different from how they are represented in text form.

As the column in your MotorTable db is not a date but text representing a date you can't use the format function on it (which would otherwise turn a real date into a text representation of the date in whatever format you specify), nor can you use comparators until you can convert what is currently text back into a real date.

If you could post the exact format of your dates as currently stored in the text field we can assist you in converting these to dates so that your comparisons will work correctly.

We'd need to know if the day and month components take up variable space (e.g. d/m/yyyy for values under 10, and dd/mm/yyyy for values of 10 and over). We'd also need to know whether the year is stored as four digits or two.

It would also help to know what the separator characters are - are they slash marks, and are they always used? If not, what character are used to separate days from months from years.

-Stewart