Query Sql Database Date Fields

[afeets]

Hoping for some advice regarding an Access Form I'm designing.

When I do a select query from Query Analyser the date field comes back for example 2007-10-12 00:00:00.

Now when I run a select query from the front end using vb code, the select query is written with a where statement as 12/10/2007. This select query returns no results.

How do I get the front end to do a date query the backend will understand??

 

[PHV]

WHERE yourDate = #2007-10-12#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[afeets]

Thanks for getting back to me. It's not as simple as that unfortunatly. I'm using an ActiveX Calendar Object to select the Dates to Query.

When you click on the Date the value returned is in the dd/mm/yyyy format.

It's curious that if my where statement is either =< 12/10/2007 or =>12/10/2007 then I do return a recordset. However if I do a Where statement "between Date1 and Date2" then no records can be found.

 

[Alruk]

It could be the date format.

Ive found that when passing dates into recordsets you need to make sure the dates are formated "mm/dd/yy" for them to work.

Try using format(Date,"mm/dd/yy") in your recordset.

Hope this helps.

 

[afeets]

Again thanks for the advice. I added the format function to my Sub, so the Select Query to open the recordset now looks like this.

select vw.intVisitID,vw.FullName,Vw.dtmDateOfVisit From view_AllSchoolVisits As Vw Where (vw.intSchoolId =4) And (vw.DtmDateOfVisit Between 10/01/07 And 10/31/07)

However I still don't get any records.

 

[Alruk]

Try putting the below is your sqlstring


And (vw.DtmDateOfVisit between # " & format(Date1,'mm/dd/yy') & "# And # " & format(Date2,'mm/dd/yy') & "#)

 

[afeets]

After your latest suggestion Query now looks like this

select vw.intVisitID,vw.FullName,Vw.dtmDateOfVisit From view_AllSchoolVisits As Vw Where (vw.intSchoolId =4) And (vw.DtmDateOfVisit Between #10/02/07# And #10/28/07#)

Unfortunately when opening the recordset it says there is a syntax error. I think that is because Access SQL recognises # for dates whereas t-sql doesn't. Correct me if i'm wrong.

 

[PHV]

And this ?

vw.DtmDateOfVisit BETWEEN '" & Format(Date1, 'yyyy-mm-dd') & "' And '" & Format(Date2, 'yyyy-mm-dd') & "'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[afeets]

Yeah thanks for all the advice, I worked it out for myself (so I win a cookie!!).

In the end all it required was for the dates to be surrounded by a single quote

'01/10/2007' Easy when you know how.

Thanks again Top Forum!!