Access and system dates problem

[RichatPlym]

Hi, I've found lots of excellent help from this site and everyone seems very friendly so i'd like to post a question. I'm fairly new to access and have created a database to produce reports on a date basis. I want to report quarterly and ideally have code within the SQL for the query to use a system date to do this. i.e hard coding the date would mean editing the code each year.

So far i have come up with this (mostly with help from here)..

SELECT tblX.ID, tblX.StartDate, tblX et
FROM tbl
WHERE (((tblX.StartDate) Between DateSerial(Year(Date()),Month(1),1) And DateSerial(Year(Date()),Month(4),0)))

Theory being this will produce a report for dates between Jan and Apr. However it appears not to work. Hardcoding is fine but I'm puzzled as in the SQL statement the date format needs reversing to how it appears in the table definition. (bit like american and english formats). i.e 15th jan in tblX is 15/01/2004 but in SQL needs coding as #1/15/2004# to obtain results

Hopefully that all makes sense, is this an access issue with date formats? Worse case then i will have to hard code the dates but it'd be nice to have it automated

Many thanks in advance for your hel
Rich

 

[Guest]

Hi Rich,

Try the following:

SELECT tblX.ID, tblX.StartDate, tblX etc
FROM tblX
WHERE (((tblX.StartDate) Between DateSerial(Year(Date
()),1,1) And DateSerial(Year(Date()),4,30)));

Rgds,
Glenn

-----Original Message-----
Hi, I've found lots of excellent help from this site and

everyone seems very friendly so i'd like to post a
question. I'm fairly new to access and have created a
database to produce reports on a date basis. I want to
report quarterly and ideally have code within the SQL for
the query to use a system date to do this. i.e hard coding
the date would mean editing the code each year.

So far i have come up with this (mostly with help from here)...

SELECT tblX.ID, tblX.StartDate, tblX etc
FROM tblX
WHERE (((tblX.StartDate) Between DateSerial(Year(Date

()),Month(1),1) And DateSerial(Year(Date()),Month(4),0)));

Theory being this will produce a report for dates between

Jan and Apr. However it appears not to work. Hardcoding is
fine but I'm puzzled as in the SQL statement the date
format needs reversing to how it appears in the table
definition. (bit like american and english formats). i.e
15th jan in tblX is 15/01/2004 but in SQL needs coding as
#1/15/2004# to obtain results.

Hopefully that all makes sense, is this an access issue

with date formats? Worse case then i will have to hard
code the dates but it'd be nice to have it automated.

 

[Guest]

Rich,

Sorry didn't answer all your question. Date literals in
sql have to be input in the American format, but will
still return the UK equivalent.
Take a look at the Where Clause in VBA Help.

Glenn

 

[Van T. Dinh]

You got the DateSerial functions incorrect.

Try:
.....
WHERE (((tblX.StartDate)
Between DateSerial(Year(Date()),1,1)
And DateSerial(Year(Date()),4,0)));

There are so many different formats for dates and JET has to adopt one
format for consistency. Since Microsoft is an US company, you can't expect
any other format.

Note that what is stored in the Table is NOT what you see. Internally, a
DateTime value is stored similar to a Double number where the integral part
represents the number of days since 30/Dec/1899 and the fractional part
represents time since mid-night as fraction of a day, i.e. 0.25 is 06:00,
0.5 is mid-day. Thus, what you see ("15/01/2004") is simply another format
/ visual representation of what is stored in the Table.

For completeness:

?Format(#01/15/2004#, "0")
38001

i.e.

* 15/Jan/2004 is 38001 days since 30/Dec/1899

* the binary representation of 38001.0 is stored in the Field Value for
15/Jan/2004.

--
HTH
Van T. Dinh
MVP (Access)

Hi, I've found lots of excellent help from this site and everyone seems

very friendly so i'd like to post a question. I'm fairly new to access and
have created a database to produce reports on a date basis. I want to report
quarterly and ideally have code within the SQL for the query to use a system
date to do this. i.e hard coding the date would mean editing the code each
year.

So far i have come up with this (mostly with help from here)...

SELECT tblX.ID, tblX.StartDate, tblX etc
FROM tblX
WHERE (((tblX.StartDate) Between DateSerial(Year(Date()),Month(1),1) And DateSerial(Year(Date()),Month(4),0)));

Theory being this will produce a report for dates between Jan and Apr.

However it appears not to work. Hardcoding is fine but I'm puzzled as in the
SQL statement the date format needs reversing to how it appears in the table
definition. (bit like american and english formats). i.e 15th jan in tblX is
15/01/2004 but in SQL needs coding as #1/15/2004# to obtain results.

Hopefully that all makes sense, is this an access issue with date formats?

Worse case then i will have to hard code the dates but it'd be nice to have
it automated.

 

[Richatplym]

That's great thanks! Has sorted it and thank you for the comprehensive explanation. Have a good weekend everyone
Kind regard
Ric