EOMONTH

  • Thread starter Thread starter Ranjit kurian
  • Start date Start date
R

Ranjit kurian

I would like to have a two tables one contain the first of every month
(starts from the month i type in popup box) and table two with EOMonths
(starts from the month i type in popup box), both the table will be replaced
only monthly once, and these table are used in other queries for a
calculations,

Examples shown below, there is a continuation for first six month(Jul-08
till Dec-08) after i will take -6thmonth(Jan-08) and -12th
month(Jan-07)(Dec-06)

Table:1
Heading: 1,2,3,4,5,6,7,8,9(nine columns)
First row:1-Dec-08 1-Nov-08 1-Oct-08 1-Sep-08 1-Aug-08 1-Jul-08
1-Jan-08 1-Jan-07 1-Dec-06

Table:2
Heading: 1,2,3,4,5,6,7,8,9(nine columns)
First row:
31-Dec-08 30-Nov-08 31-Oct-08 30-Sep-08 31-Aug-08 31-Jul-08 30-Jun-08 31-Dec-07 31-Dec-06
 
I'm not sure why you would want to have 2 tables, with these date values.
What confuses me even more is why you would want them in columns, rather than
in a single column. Personally, I would do this with a query, not a table.

1. Start out with a table (tbl_Numbers). Put one field (lngNumbers,
Long-integer) in the table, and insert the numbers 0 - 20 or so as records in
the table.

2. then, Create a query that looks something like:

SELECT DateSerial(Year(date()), cint([Enter month #]) + lngNumbers, 1) as BOM
FROM tbl_Numbers
WHERE lngNumbers < 15

This query would give you 14 months of dates (with the first day of the
month). To get the EOM dates, you would use something like:

SELECT Dateserial(Year(Date), cint([Enter month #]) + lngNumbers, 0) as EOM
FROM tbl_Numbers
WHERE lngNumbers > 0 and lngNumbers < 15)

I limited lngNumbers to > 0 because using 0 would give you the end of the
month preceeding the month number you enter.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Back
Top