query to get records between two Dates

[jack123]

Hi All,

I am developing a Windows Application using .Net Framework 2.0 and My Database is MS ACCESS 2007.

In my table , to store Date I used Text as datatype for Column and storing the date in dd/MM/yyyy format.

My problem is when I am finding records between two dates like this.......

select * from tblEmployee where [DateofJoin] >= '01/06/2009' and [DateofJoin] <= '20/06/2009'

it is working fine....

but when I changing dates like this.....

select * from tblEmployee where [DateofJoin] >= '20/05/2009' and [DateofJoin] <= '20/06/2009'

it is not fetching records......

Please help me as I am new to Access....

I know SQL Server, in that Convert function is available to convert date in any format......

Is Any thing Available in MS Access 2007 like Convert Function to convert date in any format..

Thaks in advance....

its Urgent

 

[Galaxiom]

Dates are formatted as #MM/DD/YYYY# when directly entered into SQL.
They are actually worked with as the number of days since 01/01/1900. The time is after the decimal point as a fraction of a day.

The ## tells SQL that it is working with a date.

Not sure what your format would do as it would be treated as a string.

 

[jack123]

Please tell me the Solution If Possible......

what Should I do.....

 

[Galaxiom]

I just read your post again. Because i wondered why you dnd't get a datatype conflict. Why on earth are you storing dates in a text format?

There is no hope at all that SQL is going to make any sense of your comparison. What it is returning is the result of an alphanumeric comparison between the two strings.

 

[Galaxiom]

Format your dates as dates.
Don't use dates directly in the statement. Make the comparison between date formatted field values. If it knows they are dates it understands the format.

 

[Galaxiom]

WHERE yourtable.datefield < Forms!formname!somedatefield etc

 

[jihadt_80]

I want to make a query that filter a list between 2 dates and its related data which when I make run to this query asked me about these dates.