Date format confusion

[Gerry Abbott]

Hi all
Im in Ireland, so date format is always dd/mm/yy.

It trying use query as a filter for a recordset, containing a date field,

SELECT tblInspections.InspDate FROM tblInspections WHERE
(tblInspections.InspDate Between #01/06/03# and #30/06/03#);

but when I look at the result of this in teh QBE window, I get dates
Between #06/01/03# And #03/06/30#,
and the results of the filter bear this out.

Ive been working with access for some years now, and this problem still gets
me confused.
Could someone please exlpain the best approach to handing dates, given that
they must always be displayed to the user in the dd/mm/yy format.

Thanks in advance.
Gerry

[Jeff]

Hi Gerry

Your on the right track, except Access is a US product and you have to
follow their format. The format should be month, then day then year. For
example 1 June 2003 is #06/01/2003#.

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
www.asken.com.au

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:cQ*****************@news.indigo.ie...

Hi all
Im in Ireland, so date format is always dd/mm/yy.

It trying use query as a filter for a recordset, containing a date field,

SELECT tblInspections.InspDate FROM tblInspections WHERE
(tblInspections.InspDate Between #01/06/03# and #30/06/03#);

but when I look at the result of this in teh QBE window, I get dates
Between #06/01/03# And #03/06/30#,
and the results of the filter bear this out.

Ive been working with access for some years now, and this problem still gets me confused.
Could someone please exlpain the best approach to handing dates, given that they must always be displayed to the user in the dd/mm/yy format.

Thanks in advance.
Gerry

[Gerry Abbott]

Found the problem,

I had defined the dates a type date, they should have been string to put
into the sql string.
So I just had to get the dates, as dates, then format them as I required,
then convert to strings, and add to sql.
"Gerry Abbott" <pl****@ask.ie> wrote in message
news:cQ*****************@news.indigo.ie...

Hi all
Im in Ireland, so date format is always dd/mm/yy.

It trying use query as a filter for a recordset, containing a date field,

SELECT tblInspections.InspDate FROM tblInspections WHERE
(tblInspections.InspDate Between #01/06/03# and #30/06/03#);

but when I look at the result of this in teh QBE window, I get dates
Between #06/01/03# And #03/06/30#,
and the results of the filter bear this out.

Ive been working with access for some years now, and this problem still gets me confused.
Could someone please exlpain the best approach to handing dates, given that they must always be displayed to the user in the dd/mm/yy format.

Thanks in advance.
Gerry

[Sathya]

Hi Folks

In MS access they are internally convert the supplied date parameter
into number format and then they compare.

please try the following it may solve the purpose

SELECT tblInspections.InspDate FROM tblInspections WHERE
ConvDateYYYYMMDD(tblInspections.InspDate ) >
ConvDateYYYYMMDD('02/01/2004')
and ConvDateYYYYMMDD(tblInspections.InspDate ) <
ConvDateYYYYMMDD('05/01/2004')
ConvDateYYYYMMDD is VBA Function which will Convert Date into
YYYYMMDD format
'Author Sathya
'Verison 1.1.2
Function ConvDateYYYYMMDD(Dte As Date)
' Date Format DD/MM/YYYY for dte

Dim strTemp As String
Dim strDay As String
Dim strDayTemp As String
Dim strMonth As String
Dim strYear As String
Dim strMonthTemp As String

strDayTemp = Datepart("d", Dte)
If Len(strDayTemp) < 2 Then
strDay = "0" + strDayTemp
Else
strDay = strDayTemp
End If

strMonthTemp = Datepart("m", Dte)
If Len(strMonthTemp) < 2 Then
strMonth = "0" & strMonthTemp
Else
strMonth = strMonthTemp
End If

strYear = Datepart("yyyy", Dte)
ConvDateYYYYMMDD = strYear + strMonth + strDay

End Function

"Gerry Abbott" <pl****@ask.ie> wrote in message news:<cQ*****************@news.indigo.ie>...

Hi all
Im in Ireland, so date format is always dd/mm/yy.

It trying use query as a filter for a recordset, containing a date field,

SELECT tblInspections.InspDate FROM tblInspections WHERE
(tblInspections.InspDate Between #01/06/03# and #30/06/03#);

but when I look at the result of this in teh QBE window, I get dates
Between #06/01/03# And #03/06/30#,
and the results of the filter bear this out.

Ive been working with access for some years now, and this problem still gets
me confused.
Could someone please exlpain the best approach to handing dates, given that
they must always be displayed to the user in the dd/mm/yy format.

Thanks in advance.
Gerry