I'm developing application in vb6.0 with MS-Access. When i try to fetch data between two dates using DTPicker for Ex. i select 15-Jan-15 , 16-Jan-15 in DTPickers it selects only datas from 16-Jan-15 it's not select data from 15-Jan-15.I'm using the following Query :

sql = "select * from ministmnt where Format(dat, ""dd-MMM-yy"") between '" & DTPicker2.Value & "' and '" & DTPicker3.Value & "' order by dat"

Recommended Answers

All 5 Replies

No need to use Format() function because SQl doesn't understand it and also the single quatation marks. Only for String Type value you can use the Single Quatation Marks .
The SQL Statement statement would be

sql = "select * from ministmnt where (dat between " & DTPicker2.Value & " and " & DTPicker3.Value & ") order by dat"


I don't mean to come across as rude but your statement about Format and quotation marks is incorrect.

In fact, for Access the # character should be used for date formats, ' character for strings and nothing for numbers.

With regards to Format, it can be used to prepare the data being searched for. It is particularly useful when the data field contains a time part and you want to omit that.

With regards to the problem, is the format of your DateTimePickers the same as "dd-MMM-yy"?

i'm using MS-Access and DataType is Text thats why i'm using ' and in table dat i've inserted Data and Time for that only i'm using format(),

So your SQL statement should be fine but what is the format of your Date Pickers? Does it match the same format.

Also, if you are storing date and time values you should really use a better Data Type like DateTime.

Ya DTPicker also same format, I'm not problem with queries... If i select two dates the leaste date's datas are not fetched.. Why i'm gettg this problem

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.