query on date range

[siobhanh]

i've take a datetime value and converted it to a date using the left function and the datevalue function. when i try to select a range of dates (e.g. 4/01/2013 to 4/24/2013) it gives me the month and day but not the year (e.g. i get records with 4/15/2013 and 4/15/2004 and 4/1/2005 etc.). does anyone understand why it doesn't work? when i just select on one date i get that date only. but for a range it doesn't work.

[mohanvijay]

try this

convert date to string and format your date like this "dd-mmm-yyyy" by using format function

and pass query like below

select * from tablename where datefieldname>=#date string# AND datefieldname<=#date String#

[siobhanh]

that was worse i got all dates and years

[mohanvijay]

I have worked both MS Access and MS SQL. i use this ("dd-mmm-yyyy") date for both works great

Here is my example

From date ="01-Apr-2013"
To Date = "30-Apr-2013"

Query

select * from tablename where datefieldname>=#01-Apr-2013# AND datefieldname<=#30-Apr-2013#

for this your date field must be in date data type

[siobhanh]

thank you Mohanvijay. i am using a string value in the format yyyy/mm/dd and that works. I don't understand why the other way does not work but at least i've got something to work.

[siobhanh]

I also found that if I create the field and save the query as a table(make table) then test for the date range in a new query, that works.

[rahmasoft1]

best solution is to do the following
convert the current date to the formula MM/DD/YYYY ie the original formula for acces

the pice of code will help you

mydate1 = DateSerial(Me.comb_year1.value, Me.comb_month_no1.value, Me.comb_day1.value)
mydate2 = DateSerial(Me.comb_year2.value, Me.comb_month_no2.value, Me.comb_day2.value)

mydate1 = Format(mydate1, "mm / DD / yyyy")
mydate2 = Format(mydate2, "mm / DD / yyyy")

payed_filter = "[mydate]<= " & Chr(35) & mydate2 & Chr(35)

payed_filter = payed_filter & " And [mydate]>=" & Chr(35) & mydate1 & Chr(35)

[rahmasoft1]

it will help and it is work for many years ago correctly