How to get one entry before the start and end dates

Question

I want to get one entry before the start and end date of the date

DtpFrom means – 'date picker from DtpTo means – 'date picker to 

VB 6 CODE

 sdate = DateToString(dtpFrom) edate = DateToString(dtpTo) 

QUERY

 Where DATE BETWEEN '" & sdate & "' AND '" & edate & "'" 

I want to get one entry before sdate and edate

I tried this code

VB 6 CODE

 s1sdate = -sdate e1edate= -edate 

QUERY

 Where DATE BETWEEN '" & s1date & "' AND '" & e1date & "'" 

But he minus the next day

Example

 Selecting 03/05/2009 to 03/06/2009 from date picker, but it showing record from 02/05/2009 to 02/06/2009. 

I want to display one record before the selection date and the end date, and not one day before, because my table is not a continuous date.

ADDITIONAL EXAMPLE:

If we have a table and rows [ID (int), Value (Money)], and we have several rows in it

 ID --Value 1------70 2------100 3------150 8------200 20-----250 45-----280 

and we want Query to get every row id, value and previous row value in which the data is displayed, the following

 ID --- Value ---Prev_Value 1 ----- 70 ---------- 0 2 ----- 100 -------- 70 3 ----- 150 -------- 100 8 ----- 200 -------- 150 20 ---- 250 -------- 200 45 ---- 280 -------- 250 

I am making the following query, but I think it is so poor in performance with a huge amount of data.

 select t1.id, t1.value, t2.value from table t1 inner join table t2 on t1.id = t2.id where t2.value = (select max(value) from table t where t.value< t1.value and t.id = t1.id ) and T1.value BETWEEN '" & sdate & "' AND '" & edate & "' 

Requires VB 6 CODE or ACCESS QUERY HELP.

Answer 1

 SELECT * FROM whatever WHERE DATE < sdate ORDER BY DATE DESC LIMIT 1 

I don’t think the end date really matters if you want "one before the entries between the start and end date"

But if you want the record right before the start date and the record before the end date, you can simply repeat the above query with edate instead of sdate.

This is what the query actually means: select each record from DATE before (sdate). For all of these records, order them by DATE Descending (DESC). Return only the first (LIMIT 1).

Note. Some implementations vary. You may need "TOP 1" after the word "SELECT" instead of "LIMIT 1"