CODEWITHSUNDEEP
sqlms-accessdatetime
rahularyansharma
rahularyansharma

Reputation: 10755

value not retriving in ms access query with date time

need to select the amount of given date which will be in between start date and end date

its working fine for me when i am using '2/27/2012' or '2/28/2012'

but for '2/29/2012' its not selecting the amount 

select amount  from tblPricePeriod where cdate(2/1/2012) <=cdate('2/29/2012 12:00:00 AM') 
and  cdate(2/29/2012) >= cdate('2/29/2012 12:00:00 AM')

tblpriceperiod

Upvotes: 0

Views: 2012

Answers (1)

HansUp
HansUp

Reputation: 97101

I wonder if this query might be close to what you want.

PARAMETERS WhichDate DateTime;
SELECT amount
FROM tblPricePeriod
WHERE
        [WhichDate] >= StartDate
    AND [WhichDate] <= EndDate;

If you don't want to do it as a parameter query, but create the query with a literal date value instead, try it like this ...

SELECT amount
FROM tblPricePeriod
WHERE
        #2012-02-29# >= StartDate
    AND #2012-02-29# <= EndDate;

Another option would be to use a BETWEEN expression in the WHERE clause.

SELECT amount
FROM tblPricePeriod
WHERE #2012-02-29# BETWEEN StartDate AND EndDate;

These suggestions assume StartDate and EndDate are both Date/Time data type. If they are text data type, you can use that CDate() function to use their date equivalents in your query.

CDate(StartDate)
CDate(EndDate)

Also the situation can be more complicated if your StartDate and EndDate values include time components other than midnight. You could however use the DateValue() function to cast those Date/Time values to midnight on the same date.

DateValue(StartDate)
DateValue(EndDate)

or

DateValue(CDate(StartDate))
DateValue(CDate(EndDate))

Upvotes: 2

Related Questions