create calender code

B

balu

is their any code which can create an TABLE / QUERY containing all the days
of the
current year like from 1/1/07 to 31/12/07 and
1/1/08 to 31/12/08 on next year and so on,,
 
J

John Spencer

Query method would require an auxiliary table with one field containing
the numbers 1 to 366 (0r more).

SELECT DateSerial([TheYear],1,NumberField)
FROM tblNumbers
WHERE NumberField <= 365 + ABS(IsDate([TheYear] & "/02/29"))

If you always want the current year

SELECT DateSerial(Year(Date()),1,NumberField)
FROM tblNumbers
WHERE NumberField <= 365 + ABS(IsDate(Year(Date() & "/02/29"))

VBA to build a table and populate it would be a bit more complex

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
S

Stuart McCall

John Spencer said:
Query method would require an auxiliary table with one field containing
the numbers 1 to 366 (0r more).

SELECT DateSerial([TheYear],1,NumberField)
FROM tblNumbers
WHERE NumberField <= 365 + ABS(IsDate([TheYear] & "/02/29"))

If you always want the current year

SELECT DateSerial(Year(Date()),1,NumberField)
FROM tblNumbers
WHERE NumberField <= 365 + ABS(IsDate(Year(Date() & "/02/29"))

VBA to build a table and populate it would be a bit more complex

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

is their any code which can create an TABLE / QUERY containing all the
days of the current year like from 1/1/07 to 31/12/07 and 1/1/08 to
31/12/08 on next year and so on,,
Click to expand...
Click to expand...

Neat answer. I especially liked the ABS(IsDate( thing. Simple & elegant.
 
T

Tony Toews [MVP]

balu said:
is their any code which can create an TABLE / QUERY containing all the days
of the
current year like from 1/1/07 to 31/12/07 and
1/1/08 to 31/12/08 on next year and so on,,
Click to expand...

Why? What is the objective or problem? Maybe we can suggest an
alternative?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
B

balu

dear sir
my problom the need for the complete calender of the year is i want to join
date fields with the other table having date field so that i will get the
each and every date between the two said dates i n detail
table issues
issueid , issues issueddate
in select query it will give the dates of issues where the issues are there
but iwant in such a way that all the dates of the months also needed and the
issues are availble aginest such date hence i jined issues yable with
onlydates table
any other way please advise
 
Top