data type mismatch between vb date datatype and ms access datetime data type

[GR M]

I'v designed a ms access database table with one field having Datetime datatype. I can Insert data into it from vb.net front end by using

trns_dt = DateTime.Now.Date
 

.
But when I fetch records from the tableand assign the dataset datasource as da, it shows datatype mismatch error.

 
da = New OleDbDataAdapter("Select * from Daily_Transaction where trans_date between '" & dtp_from_dt.value & "' and '" & dtp_To.value & "' ", con)
ds = New DataSet
                ds.Clear()
                da.Fill(ds, "Daily_Transaction")
                DGV_Admin.DataSource = ds.Tables(0)
                DGV_Admin.Show()
 

Plz help ASAP

[Stewart Ross]

Hi. In line 2 you are treating the dates as text by enclosing them in quotes, which I think is the source of your type mismatch. The Access query engine uses the pound sign (#) as a delimiter for dates, so line 2 should be rewritten as:

da = New OleDbDataAdapter("Select * from Daily_Transaction where trans_date between #" & dtp_from_dt.value & "# and #" & dtp_To.value & "# ", con)

Please note that for comparisons such as BETWEEN to work consistently date values presented as literals need to be in ANSI standard format - that is, month-first as mm/dd/yyyy.

-Stewart

[GR M]

Thanks A lot sir.