access date query

[DeOiD]

hi

i want to obtain a value of a table with date restrictions, so i created two fields, one for the inicial date and other for the final date. when i try to do that the result is always null...i don't no why...

i use this statement:
"SELECT caminho_foto FROM destaques WHERE (data_inicial <= #" & Date.Today & "#) AND (data_final >= #" & Date.Today & "#) ORDER BY id_destaque DESC"

please help me...

[campkev]

can you give us some sample data?

[DeOiD]

by sample data you mean values from the bd table?
the attached imagem is a print of my table, the last two fields are for the inicial and final date (their names are in portuguese, date(eng) = data(pt)), and their are both date/time fields, theres no error but theres no results also.
strangely, if i use the web developer query builder on a accessdatasource with the value 07-05-2007, it works...

[DeOiD]

well, after one giant headaque i figured out why it didn't work...

in my sql statement i used date.today (or date.today.tostring("d")) to obtain the date. the result was in this format 08-05-2007. the same format as the date fields in my DB. so i thought that the error could not be this, but it is...althought i don't understand i... to work i had to change the dd/mm order to mm/dd using Date.Today.ToString("MM-dd-yyyy") .

so i had: "SELECT caminho_foto FROM destaques WHERE (data_inicial <= #" & Date.Today.ToString("d") & "#) AND (data_final >= #" & Date.Today.ToString("d") & "#)" and now i have: "SELECT caminho_foto FROM destaques WHERE (data_inicial <= #" & Date.Today.ToString("MM-dd-yyyy") & "#) AND (data_final >= #" & Date.Today.ToString("MM-dd-yyyy") & "#)" :)