create calender code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,,
 
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
'====================================================
 
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.
 
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/
 
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
 
Back
Top