filtering dates

[Peter Timbol]

how can i filter dates in vb6 and msaccess 2000.. im using adodc my code is

pickfrom and pickto are datetime picker controls..
dtr is my table

Adodc3.CommandType = adCmdText
Adodc3.RecordSource = "select * from dtr where dtr.[employee number]='" & Label4.Caption & "' and dtr.date between " & pickfrom.Value & " and " & pickto.Value & " order by dtr.date"
Adodc3.Recordset.Requery
Adodc3.Refresh

but the results are not correct..
i also tried to used the logical operators rather than BETWEEN..

the data types are date/time..

[Peter Timbol]

got it guys you must include the # symbol... hope it helps you too..

[aaryan]

got it guys you must include the # symbol... hope it helps you too..

hi peter,
cd u just write the full code for me as i am also caught with the same.

[Killer42]

It would be something along these lines...

Adodc3.CommandType = adCmdText
Adodc3.RecordSource = _
  "select * from dtr where dtr.[employee number]='" _
  & Label4.Caption & "' and dtr.date between #" _
  & Format(pickfrom.Value, "mm/dd/yyyy") _
  & "# and #" & Format(pickto.Value, "mm/dd/yyyy") _
  & "# order by dtr.date"
Adodc3.Recordset.Requery
Adodc3.Refresh

(I've just chopped up the long line for readability in the Code box here).

It's important to remember that when you insert date literals into SQL using the # delimiters, you must put the date in American (m/d/y) format. Some databases such as Access might try to interpret other formats, but it can cause confusion, especially since in certain cases they might get it right only for days after the 12th of the month.

See NeoPa's "sticky" threads at the top of the Access forum for more info.

[Peter Timbol]

hi peter,
cd u just write the full code for me as i am also caught with the same.

Adodc3.CommandType = adCmdText
Adodc3.RecordSource = "select * from dtr where dtr.[employee number]='" & Label4.Caption & "' and dtr.date between #" & pickfrom.Value & "# and #" & pickto.Value & "# order by dtr.date"
Adodc3.Recordset.Requery
Adodc3.Refresh

or you can include FORMAT given by Killer42,

be sure you have DATE/TIME data type in your database and the DATAFORMAT of your datetime picker control is the same as the DATA FORMAT in your database, if you don't want to include the FORMAT.

[aaryan]

Adodc3.CommandType = adCmdText
Adodc3.RecordSource = "select * from dtr where dtr.[employee number]='" & Label4.Caption & "' and dtr.date between #" & pickfrom.Value & "# and #" & pickto.Value & "# order by dtr.date"
Adodc3.Recordset.Requery
Adodc3.Refresh

or you can include FORMAT given by Killer42,

be sure you have DATE/TIME data type in your database and the DATAFORMAT of your datetime picker control is the same as the DATA FORMAT in your database, if you don't want to include the FORMAT.

thank u all guys, i got it

[MAcGYVER]

Good Job Thanks guys!