Visual capture of dates in ASP Page

[Tim Williams]

Can you give an idea of how the vacation days are stored?

tim


"J P Singh" <noemail@asIhatespam> wrote in message
news:#KvHDgPUDHA.3024@tk2msftngp13.phx.gbl...

> Hi All
>
> I have been given a slightly tough task and was wonder if someone can

offer

> help
>
> We have a table in the database which stores dates booked as holidays by

our

> staff. The structure of the table is
>
> UserId
> Date From
> Date To
> No Of Days
>
> I need to plot it in such a way that it makes it easier for managers to

see

> who is off on holiday by looking at grid which shoes in colour when a user
> has booked a days off.
>
> So it may look like below where the columns in the html table represent

days

> of a particular month and the rows represent the employee. For each day

that

> he is off I want to put a cross in the corresponding cell or change it to

a

> different colour.
>
> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
> .......
> 77889 x x x x x

x

> 89999 x x x
> x x
> 55665 x x x x x
>
>
> The above graph is much more slicker than looking at a simple display like
> we have now
>
> User Id Date From Date To No of Days
>
> 77889 10/7/2003 13/7/2003 3
> 77889 16/7/2003 18/7/2003 3
>
> 89999 2/7/2003 4/7/2003 3
> 89999 20/7/2003 21/7/2003 2
>
> 55665 6/7/2003 8/7/2003 3
> 55665 14/7/2003 15/7/2003 2
>
> Any help is greatly appreciated. Perhaphs a rough outline of the code

would

> be very helpful.
>
> Thanks in advance for all your help
>
>
>
>
>

[Bob Barrows]

Tammy,
I'm sorry. I know you're trying to help but ... this is just plain
wrong-headed. This would be great advice if the OP was using a spreadsheet.
In a database, it is asking for problems. You've just advised him to violate
one of the most basic normalization rules for databases: do not use
repeating data elements.

J. P., while it may appear as if this design will solve your immediate
problem, using it will create more problems down the road. What problems,
you ask? Well, consider that currently, the greatest number of holidays is
16, so you create a table with 16 day columns. It works great for two
months, until management decides to add 4 more holidays (it could happen!
<grin>). So now you have to modify your table structure, AND the user
interface, AND the code that maintains the data. Not a good situation ...

The lesson: don't store data in table or field names. Data should be stored
in table rows.

See my other reply for my recommentation.

Bob Barrows

Tammy B. wrote:

> it might be easier to start at the beginning all over again
> rather than:

>> UserId
>> Date From
>> Date To
>> No Of Days

>
> Try table like:
>
> UserID Month Day1 Day2 Day1 Day3 Day4 Day5 Day6 Day7 Day8 Day9 Day10
> Day11 Day12 Day13 Day14 Day15 Day16
> 555 July X X
> X X X XX
>
>
> this would be easy to spit out to asp, and easy to update
>
>
> "J P Singh" <noemail@asIhatespam> wrote in message
> news:%23KvHDgPUDHA.3024@tk2msftngp13.phx.gbl...

>> Hi All
>>
>> I have been given a slightly tough task and was wonder if someone
>> can offer help
>>
>> We have a table in the database which stores dates booked as
>> holidays by our staff. The structure of the table is
>>
>> UserId
>> Date From
>> Date To
>> No Of Days
>>
>> I need to plot it in such a way that it makes it easier for managers
>> to see who is off on holiday by looking at grid which shoes in
>> colour when a user has booked a days off.
>>
>> So it may look like below where the columns in the html table
>> represent days of a particular month and the rows represent the
>> employee. For each day that he is off I want to put a cross in the
>> corresponding cell or change it to a different colour.
>>
>> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
>> .......
>> 77889 x x x x
>> x x 89999 x x x
>> x x
>> 55665 x x x x x
>>
>>
>> The above graph is much more slicker than looking at a simple
>> display like we have now
>>
>> User Id Date From Date To No of Days
>>
>> 77889 10/7/2003 13/7/2003 3
>> 77889 16/7/2003 18/7/2003 3
>>
>> 89999 2/7/2003 4/7/2003 3
>> 89999 20/7/2003 21/7/2003 2
>>
>> 55665 6/7/2003 8/7/2003 3
>> 55665 14/7/2003 15/7/2003 2
>>
>> Any help is greatly appreciated. Perhaphs a rough outline of the
>> code would be very helpful.
>>
>> Thanks in advance for all your help

[J P Singh]

Hi Bob

Thanks for your reply. The number of days are being stored for a reason.

The FDate is the first day of the holiday and Tdate is the last date of the
holiday. Subtracting Tdate from Fdate will be fine who works monday to
friday but we have people who work in shifts and part time and job share. So
there are far too many rules to code and the application will become too
restrictive.

Also if a user was working shift of say 3 on 3 off. Simply subtracting the
two dates won't five you correct figures.

You are correct on the other point about data structure. It would be
completly incorrect to store the data the way Tammy has suggested(sorry
Tammy)

BTW I have done well with the stuff and come up with the following code
which actually works. I just need to fine tune it

daysofmonth=cdate(month(sdate)+1&"/1/" & year(sdate))
daysofmonth=cdate(year(sdate) & "/" & month(sdate)+1 & "/1")
daysofmonth=day(daysofmonth-1)

end if

response.write request.form("month")


strUserId = session("UserId")
strDept = request.querystring("Dept")
strDept = "Information Systems"
set rs = Server.createobject ("adodb.recordset")
'strSQL = "select * from holidaysview where Department = '" &
strDept & "' and fdate between #" & sdate & "# and #" & edate & "# order by
userid, fdate"
strSQL = "select * from holidaysview where fdate between #" & sdate
& "# and #" & edate & "# order by userid, fdate"

response.write strSql

rs.CursorType = 2
rs.LockType = 3
rs.Open strSQL, conn

if not rs.eof then

response.write "<table ><TR><TD></TD>"
for count=1 to daysofmonth
response.write "<TD>" & count & "</TD>"
next

response.write "</TR><TR><TD>" & rs("Lastname") & " " & rs("firstname") &
"</TD>"
startday=1
i = 0

Do
tcount = day(rs("fdate")) -1
for counter=startday to tcount
response.write "<TD></TD>"
i = i+1
next

'response.write "total blank cells written = " & i & "<br>"

'response.write "no of days " & rs("nodays")

for counter=1 to rs("nodays")
response.write "<TD>X</TD>"
i = i +1
next
startday=i
tempid=rs("userid")
rs.movenext

if not(rs.eof) then
if tempid<>rs("userid") then response.write "</TR><TR><TD>" &
rs("Lastname")& " " & rs("Firstname") & "</TD>": startday=1 :i = 1
end if
loop until rs.eof

response.write "</TR></table>"
"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:uprdi7dUDHA.3220@tk2msftngp13.phx.gbl...

> J P Singh wrote:

> > Hi All
> >
> > I have been given a slightly tough task and was wonder if someone can
> > offer help
> >
> > We have a table in the database which stores dates booked as holidays
> > by our staff. The structure of the table is
> >
> > UserId
> > Date From
> > Date To
> > No Of Days

>
> Why are you storing BOTH Date To and No Of Days? Because either figure

could

> be calculated by knowing the other, you should store one or the other, not
> both. I suggest storing the Date To.
>
> More below:
>

> >
> > I need to plot it in such a way that it makes it easier for managers
> > to see who is off on holiday by looking at grid which shoes in colour
> > when a user has booked a days off.
> >
> > So it may look like below where the columns in the html table
> > represent days of a particular month and the rows represent the
> > employee. For each day that he is off I want to put a cross in the
> > corresponding cell or change it to a different colour.
> >
> > 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
> > .......
> > 77889 x x x x
> > x x 89999 x x x
> > x x
> > 55665 x x x x x
> >
> >
> > The above graph is much more slicker than looking at a simple display
> > like we have now
> >
> > User Id Date From Date To No of Days
> >
> > 77889 10/7/2003 13/7/2003 3
> > 77889 16/7/2003 18/7/2003 3
> >
> > 89999 2/7/2003 4/7/2003 3
> > 89999 20/7/2003 21/7/2003 2
> >
> > 55665 6/7/2003 8/7/2003 3
> > 55665 14/7/2003 15/7/2003 2
> >
> > Any help is greatly appreciated. Perhaphs a rough outline of the code
> > would be very helpful.
> >

>
> OK, the grid will display a fixed number of dates, correct? The user will
> supply those dates? Perhaps pick a particular month to display? This would
> be easier. Prompt the user for a month and year. Or write the page

assuming

> the user will always want to see the current month and year. Up to you.
> Either way, you start with a month and year, allowing you to generate
> variables containing a from date and to date.
>
> Create a stored procedure or saved query (depending on what database you

are

> using) that accepts one parameter: the end date. I am going to assume SQL
> Server. so the procedure will look like this:
>
> create procedure GetHolidays (
> @end datetime) AS
> Select UserID,DateFrom,DateTo From table
> WHERE DateTo <=@end)
> ORDER BY UserID, DateFrom
>
> The vbscript will look like this (untested air code):
>
> dim iMth, iYr, dStart,dEnd,arDates(),arData,cn,rs
> dim iDays, iStartDay, iEndDay
> dim lCurUser, lNewUser
> dim i, j, dHolStart, dHolEnd
> const cUser = 0
> const cFrom = 1
> const cTo = 2
> const cHoliday = "yellow"
> const cWorkday = "white"
>
> iMth=request.form("mth")
> iYr=request.form("yr")
> 'validate them, then
> dStart=dateserial(iYr,iMth,1)
> dEnd=dateadd("m",1,dStart)
> dEnd= dateadd("d",-1,dEnd)
> iDays = Day(dEnd)
> Redim arDates(iDays)
> 'this is effectively a one-based array - we will ignore the
> 'zero element in later code
>
> 'instantiate and open your connection using cn, then
> set rs=server.createobject("adodb.recordset")
> cn.GetHolidays dEnd, rs
> if not rs.eof then arData=rs.Getrows
> rs.close
> set rs=nothing
> cn.close
> set cn=nothing
> if not isArray(arData) then
> response.write "No records returned"
> else
> 'pass arDates and dStart to a sub that creates your
> 'table heading
> CreateHeading arDates, dStart
> lCurUser = arData(cUser,0)
> for i = 1 to iDays
> arDates(i) = cWorkday
> next
> for i = 0 to ubound(arData,2)
> lNewUser = arData(cUser,0)
> if lCurUser <> lNewUser then
> 'pass arDates and lCurUser to a sub that creates the
> 'table row for the user
> CreateUserRow arDates,lCurUser
> lCurUser = lNewUser
> for i = 1 to iDays
> arDates(i) = cWorkday
> next
> else
> dHolStart = CDate(arData(cFrom,i))
> iStartDay = Day(dHolStart)
> dHolEnd= CDate(arData(cTo,i))
> iEndDay= Day(dHolEnd)
> if dHolEnd <= iStartDay then iStartDay =1
> For j = iStartDay to iEndDay
> arDates(j) = cHoliday
> next
> end if
> next
> response.write "</table>"
> erase arData
> erase arDates
> end if
>
> Sub CreateHeading(pAr,pStart)
> dim i
> response.write "<TABLE><TR><TH colspan="
> response.write ubound(pAr) + 1
> response.write " align = center>Holidays for "
> response.write monthname(pDate) & " " & year(pDate)
> response.write "</TH></TR><TR><TH>"
> response.write "User ID" & "</TH>"
> for i = 1 to ubound(pAr)
> response.write "<TH>" & i & "</TH>"
> next
> response.write "</TR>"
> end sub
>
> sub CreateUserRow(pAr, pUser)
> dim i
> response.write "<tr><th>" & pUser & "</th>"
> for i = 1 to ubound(pAr)
> response.write "<td style = ""bgcolor: "
> response.write pAr(i)
> response.write """>&nbsp;&nbsp;</td>"
> next
> response.write "</tr>"
> end sub
>
>
> HTH,
> Bob Barrows
>
>
>

[DaWoE]

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:u7R5odeUDHA.2200@TK2MSFTNGP11.phx.gbl...

> J P Singh wrote:

> > Hi Bob
> >
> > BTW I have done well with the stuff and come up with the following
> > code which actually works. I just need to fine tune it
> >

> OK - glad to hear it. Especially considering that the code I suggested had

a

> major flaw in it (which occurred to me about 10 min. after posting it). A
> case of virtual beer to the first person to spot it. I'm not talking about

a

> simple typo. It's a significant flaw that will prevent the code from
> working.
>
> Have fun,
> Bob Barrows
>

You didn't open your connection to your db.

DaWoE

[Bob Barrows]

DaWoE wrote:

> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> news:u7R5odeUDHA.2200@TK2MSFTNGP11.phx.gbl...

>> J P Singh wrote:

>>> Hi Bob
>>>
>>> BTW I have done well with the stuff and come up with the following
>>> code which actually works. I just need to fine tune it
>>>

>> OK - glad to hear it. Especially considering that the code I
>> suggested had a major flaw in it (which occurred to me about 10 min.
>> after posting it). A case of virtual beer to the first person to
>> spot it. I'm not talking about a simple typo. It's a significant
>> flaw that will prevent the code from working.
>>
>> Have fun,
>> Bob Barrows
>>

> You didn't open your connection to your db.
>

No, but I told him to do it in the comment. Nice try though. You qualify for
a consolation prize: a six-pack of virtual beer!

Bob

[Bob Barrows]

Bob Barrows wrote:

> J P Singh wrote:

>> Hi Bob
>>
>> BTW I have done well with the stuff and come up with the following
>> code which actually works. I just need to fine tune it
>>

> OK - glad to hear it. Especially considering that the code I
> suggested had a major flaw in it (which occurred to me about 10 min.
> after posting it). A case of virtual beer to the first person to spot
> it. I'm not talking about a simple typo. It's a significant flaw that
> will prevent the code from working.
>

Hint (scroll down to read it):
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
hint below
Look at the code used to "clear" the arDates array ...

[John Beschler]

Is it because ArData is never declared as an array?

My initial thought was the Erase statement, but according
to the VBCript reference, that is valid.

>-----Original Message-----
>Bob Barrows wrote:

>> J P Singh wrote:

>>> Hi Bob
>>>
>>> BTW I have done well with the stuff and come up with

the following

>>> code which actually works. I just need to fine tune it
>>>

>> OK - glad to hear it. Especially considering that the

code I

>> suggested had a major flaw in it (which occurred to me

about 10 min.

>> after posting it). A case of virtual beer to the first

person to spot

>> it. I'm not talking about a simple typo. It's a

significant flaw that

>> will prevent the code from working.
>>

>Hint (scroll down to read it):
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>hint below
>Look at the code used to "clear" the arDates array ...
>
>
>.
>

[Bob Barrows]

Sorry, only one consolation prize per contestant. Two wrong guesses still
equals one 6-pack of virtual beer (sorry, supplies are limited <grin>)

However, the sponsors have seen fit to increase the grand prize to TWO cases
of virtual beer! Plus, you get to choose the brand. Wine lovers can choose
to accept a case of the virtual wine of their choice instead!

John Beschler wrote:

> Is it because ArData is never declared as an array?

GetRows creates an array behind-the-scenes. A Variant variable can accept
any data type, so there is no problem assigning the result of GetRows to the
arData variable.

Did you misread the hint? Why focus on arData? (hint, hint)

Bob
PS. Hope you are all having fun with this.

[John Beschler]

OK. I got where you used the same index for two nested
loops. However, I've fixed that and the code still doesn't
work correctly.

First, it only picks up the last holiday record for a user
(if a user has more than one holiday record)
Second, it ignores the last user in the result set.

>-----Original Message-----
>Sorry, only one consolation prize per contestant. Two

wrong guesses still

>equals one 6-pack of virtual beer (sorry, supplies are

limited <grin>)

>
>However, the sponsors have seen fit to increase the grand

prize to TWO cases

>of virtual beer! Plus, you get to choose the brand. Wine

lovers can choose

>to accept a case of the virtual wine of their choice

instead!

>
>John Beschler wrote:

>> Is it because ArData is never declared as an array?

>
>GetRows creates an array behind-the-scenes. A Variant

variable can accept

>any data type, so there is no problem assigning the

result of GetRows to the

>arData variable.
>
>Did you misread the hint? Why focus on arData? (hint,

hint)

>
>Bob
>PS. Hope you are all having fun with this.
>
>
>.
>

[Bob Barrows]

Ding, ding , ding!
We have a winner! Yes, I forgot to change the counter used on the second
loop used to reset the arDates contents. You can claim your prize at the
exit. :-)

Hmmm - now I have to check out these other errors ....

Bob

John Beschler wrote:

> OK. I got where you used the same index for two nested
> loops. However, I've fixed that and the code still doesn't
> work correctly.
>
> First, it only picks up the last holiday record for a user
> (if a user has more than one holiday record)
> Second, it ignores the last user in the result set.
>
>
>

>> -----Original Message-----
>> Sorry, only one consolation prize per contestant. Two wrong guesses
>> still equals one 6-pack of virtual beer (sorry, supplies are limited
>> <grin>)
>>
>> However, the sponsors have seen fit to increase the grand prize to
>> TWO cases of virtual beer! Plus, you get to choose the brand. Wine
>> lovers can choose to accept a case of the virtual wine of their
>> choice instead!
>>
>> John Beschler wrote:

>>> Is it because ArData is never declared as an array?

>>
>> GetRows creates an array behind-the-scenes. A Variant variable can
>> accept any data type, so there is no problem assigning the result of
>> GetRows to the arData variable.
>>
>> Did you misread the hint? Why focus on arData? (hint, hint)
>>
>> Bob
>> PS. Hope you are all having fun with this.
>>
>>
>> .

[Bob Barrows]

OK, I've cleaned up the errors and tested it. here's what it looks like now:

<%@ Language=vbScript %>
<%
dim iMth, iYr, dStart,dEnd,arDates(),arData,cn,rs
dim iDays, iStartDay, iEndDay
dim lCurUser, lNewUser
dim i, j, dHolStart, dHolEnd,k
const cUser = 0
const cFrom = 1
const cTo = 2
const cHoliday = "yellow"
const cWorkday = "white"

iMth=request.form("mth")
if len(iMth) = 0 then iMth = 7
iYr=request.form("yr")
if len(iYr) = 0 then iYr = 2003

'validate them, then
dStart=dateserial(iYr,iMth,1)
dEnd=dateadd("m",1,dStart)
dEnd= dateadd("d",-1,dEnd)
iDays = Day(dEnd)
Redim arDates(iDays)
'this is effectively a one-based array - we will ignore the
'zero element in later code

set cn=server.CreateObject("adodb.connection")
Cn.open "Provider=SQLOLEDB;Initial Catalog=" & _
"Pubs;Data Source=censored" & _
";UID=censored;Password=censored"

set rs=server.createobject("adodb.recordset")
cn.GetHolidays dEnd, rs
if not rs.eof then
arData=rs.Getrows
end if
rs.close
set rs=nothing
cn.close
set cn=nothing
if not isArray(arData) then
response.write "No records returned"
else
'pass arDates and dStart to a sub that creates your
'table heading
CreateHeading arDates, dStart
lCurUser = arData(cUser,0)
for i = 1 to iDays
arDates(i) = cWorkday
next
for i = 0 to ubound(arData,2)
lNewUser = arData(cUser,i)
if lCurUser <> lNewUser then
'pass arDates and lCurUser to a sub that creates the
'table row for the user
CreateUserRow arDates,lCurUser
lCurUser = lNewUser
for j = 1 to iDays
arDates(j) = cWorkday
next
end if
dHolStart = CDate(arData(cFrom,i))
iStartDay = Day(dHolStart)
dHolEnd= CDate(arData(cTo,i))
iEndDay= Day(dHolEnd)
if dHolEnd <= iStartDay then iStartDay =1
For j = iStartDay to iEndDay
arDates(j) = cHoliday
next
next
CreateUserRow arDates,lCurUser
response.write "</table>"
erase arData
erase arDates
end if

Sub CreateHeading(pAr,pStart)
dim i
response.write "<TABLE border=1 style=""border-collapse:collapse"">" & _
"<TR><TH colspan="
response.write ubound(pAr) + 1
response.write " align = center>Holidays for "
response.write monthname(month(pStart)) & " " & year(pStart)
response.write "</TH></TR><TR><TH>"
response.write "User ID" & "</TH>"
for i = 1 to ubound(pAr)
response.write "<TH width=15>" & i & "</TH>"
next
response.write "</TR>"
end sub

sub CreateUserRow(pAr, pUser)
dim i
response.write "<tr><th>" & pUser & "</th>"
for i = 1 to ubound(pAr)
response.write "<td bgcolor= "
response.write pAr(i)
response.write ">&nbsp;&nbsp;</td>"
next
response.write "</tr>"
end sub

%>

HTH,
Bob Barrows

[DaWoE]

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:eo8P0efUDHA.1052@TK2MSFTNGP09.phx.gbl...

> DaWoE wrote:

> > "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> > news:u7R5odeUDHA.2200@TK2MSFTNGP11.phx.gbl...

> >> J P Singh wrote:
> >>> Hi Bob
> >>>
> >>> BTW I have done well with the stuff and come up with the following
> >>> code which actually works. I just need to fine tune it
> >>>
> >> OK - glad to hear it. Especially considering that the code I
> >> suggested had a major flaw in it (which occurred to me about 10 min.
> >> after posting it). A case of virtual beer to the first person to
> >> spot it. I'm not talking about a simple typo. It's a significant
> >> flaw that will prevent the code from working.
> >>
> >> Have fun,
> >> Bob Barrows
> >>

> > You didn't open your connection to your db.
> >

> No, but I told him to do it in the comment. Nice try though. You qualify

for

> a consolation prize: a six-pack of virtual beer!
>
> Bob
>

Thx...it tasted virtually great!

[John Beschler]

>-----Original Message-----
>
>"Bob Barrows" <reb_01501@yahoo.com> wrote in message
>news:eo8P0efUDHA.1052@TK2MSFTNGP09.phx.gbl...

>> DaWoE wrote:

>> > "Bob Barrows" <reb_01501@yahoo.com> wrote in message
>> > news:u7R5odeUDHA.2200@TK2MSFTNGP11.phx.gbl...
>> >> J P Singh wrote:
>> >>> Hi Bob
>> >>>
>> >>> BTW I have done well with the stuff and come up

with the following

>> >>> code which actually works. I just need to fine tune

it

>> >>>
>> >> OK - glad to hear it. Especially considering that

the code I

>> >> suggested had a major flaw in it (which occurred to

me about 10 min.

>> >> after posting it). A case of virtual beer to the

first person to

>> >> spot it. I'm not talking about a simple typo. It's a

significant

>> >> flaw that will prevent the code from working.
>> >>
>> >> Have fun,
>> >> Bob Barrows
>> >>
>> > You didn't open your connection to your db.
>> >

>> No, but I told him to do it in the comment. Nice try

though. You qualify

>for

>> a consolation prize: a six-pack of virtual beer!
>>
>> Bob
>>

>Thx...it tasted virtually great!
>
>
>.
>

Shouldn't that be: "It virtually tasted great!"? :)

[J P Singh]

Hi Bob I have tried the code by changing it for my database but it doesn't
seems to give me correct result.

The results it gives me is as below

Holidays for July 2003
User ID 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
25 26 27 28 29 30 31
219104
219232
219104
218313


The data in my query in access is

EmployeeNumber Lastname Firstname UserID FDate TDate Status Nodays
Department
218313 Carter Ian 218313 01-Jul-03 04-Jul-03 3 4 Information Systems
218313 Carter Ian 218313 21-Jul-03 25-Jul-03 3 5 Information Systems
219104 Singh Jaspinder 219104 01-Jul-03 01-Jul-03 3 1 Information
Systems
219104 Singh Jaspinder 219104 05-Jul-03 07-Jul-03 3 3 Information
Systems
219104 Singh Jaspinder 219104 10-Jul-03 12-Jul-03 3 3 Information
Systems
219104 Singh Jaspinder 219104 15-Jul-03 16-Jul-03 3 1 Information
Systems
219104 Singh Jaspinder 219104 20-Jul-03 22-Jul-03 3 3 Information
Systems
219232 Whitebread James 219232 15-Jul-03 19-Jul-03 3 5 Information
Systems
219232 Whitebread James 219232 22-Jul-03 25-Jul-03 3 4 Information
Systems
219232 Whitebread James 219232 27-Jul-03 27-Jul-03 3 1 Information
Systems
219232 Whitebread James 219232 29-Jul-03 29-Jul-03 3 1 Information
Systems


An surely according to the data the above graph is not correct.

issues

1. First of all it has two rows for the same employee ie. 219104 which is
wrong

2. The data plotted is completely incorrect.

Maybe be I have missed something but I have pasted the modified code below.
Can you please help

Thanks - Jas

<%@ Language=vbScript %>
<!--#include file="conn.asp"-->
<!--#include file="header.asp"-->

<%

dim iMth, iYr, dStart,dEnd,arDates(),arData,cn,rs
dim iDays, iStartDay, iEndDay
dim lCurUser, lNewUser
dim i, j, dHolStart, dHolEnd,k
const cUser = 0
const cFrom = 1
const cTo = 2
const cHoliday = "yellow"
const cWorkday = "white"

iMth=request.form("mth")
if len(iMth) = 0 then iMth = 7
iYr=request.form("yr")
if len(iYr) = 0 then iYr = 2003

'validate them, then
dStart=dateserial(iYr,iMth,1)
dEnd=dateadd("m",1,dStart)
dEnd= dateadd("d",-1,dEnd)
iDays = Day(dEnd)
Redim arDates(iDays)
'this is effectively a one-based array - we will ignore the
'zero element in later code
set rs=server.createobject("adodb.recordset")

'strSQL = "select * from holidaysview where fdate between #" & sdate & "#
and #" & edate & "# order by userid, fdate"

strSQL = "select * from holidayrequesrs order by userid, fdate"



rs.CursorType = 2
rs.LockType = 3
'rs.Open strSQL, conn


conn.holidaysview dEnd, rs
if not rs.eof then
arData=rs.Getrows
end if
rs.close
set rs=nothing
conn.close
set conn=nothing
if not isArray(arData) then
response.write "No records returned"
else
'pass arDates and dStart to a sub that creates your
'table heading
CreateHeading arDates, dStart
lCurUser = arData(cUser,0)
for i = 1 to iDays
arDates(i) = cWorkday
next
for i = 0 to ubound(arData,2)
lNewUser = arData(cUser,i)
if lCurUser <> lNewUser then
'pass arDates and lCurUser to a sub that creates the
'table row for the user
CreateUserRow arDates,lCurUser
lCurUser = lNewUser
for j = 1 to iDays
arDates(j) = cWorkday
next
end if
dHolStart = CDate(arData(FDate,i))
iStartDay = Day(dHolStart)
dHolEnd= CDate(arData(Tdate,i))
iEndDay= Day(dHolEnd)
if dHolEnd <= iStartDay then iStartDay =1
For j = iStartDay to iEndDay
arDates(j) = cHoliday
next
next
CreateUserRow arDates,lCurUser
response.write "</table>"
erase arData
erase arDates
end if

Sub CreateHeading(pAr,pStart)
dim i
response.write "<TABLE border=1 style=""border-collapse:collapse"">" & _
"<TR><TH colspan="
response.write ubound(pAr) + 1
response.write " align = center>Holidays for "
response.write monthname(month(pStart)) & " " & year(pStart)
response.write "</TH></TR><TR><TH>"
response.write "User ID" & "</TH>"
for i = 1 to ubound(pAr)
response.write "<TH width=15>" & i & "</TH>"
next
response.write "</TR>"
end sub

sub CreateUserRow(pAr, pUser)
dim i
response.write "<tr><th>" & pUser & "</th>"
for i = 1 to ubound(pAr)
response.write "<td bgcolor= "
response.write pAr(i)
response.write ">&nbsp;&nbsp;</td>"
next
response.write "</tr>"
end sub

%>

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:ukuMjyiUDHA.2456@TK2MSFTNGP09.phx.gbl...

> OK, I've cleaned up the errors and tested it. here's what it looks like

now:

>
> <%@ Language=vbScript %>
> <%
> dim iMth, iYr, dStart,dEnd,arDates(),arData,cn,rs
> dim iDays, iStartDay, iEndDay
> dim lCurUser, lNewUser
> dim i, j, dHolStart, dHolEnd,k
> const cUser = 0
> const cFrom = 1
> const cTo = 2
> const cHoliday = "yellow"
> const cWorkday = "white"
>
> iMth=request.form("mth")
> if len(iMth) = 0 then iMth = 7
> iYr=request.form("yr")
> if len(iYr) = 0 then iYr = 2003
>
> 'validate them, then
> dStart=dateserial(iYr,iMth,1)
> dEnd=dateadd("m",1,dStart)
> dEnd= dateadd("d",-1,dEnd)
> iDays = Day(dEnd)
> Redim arDates(iDays)
> 'this is effectively a one-based array - we will ignore the
> 'zero element in later code
>
> set cn=server.CreateObject("adodb.connection")
> Cn.open "Provider=SQLOLEDB;Initial Catalog=" & _
> "Pubs;Data Source=censored" & _
> ";UID=censored;Password=censored"
>
> set rs=server.createobject("adodb.recordset")
> cn.GetHolidays dEnd, rs
> if not rs.eof then
> arData=rs.Getrows
> end if
> rs.close
> set rs=nothing
> cn.close
> set cn=nothing
> if not isArray(arData) then
> response.write "No records returned"
> else
> 'pass arDates and dStart to a sub that creates your
> 'table heading
> CreateHeading arDates, dStart
> lCurUser = arData(cUser,0)
> for i = 1 to iDays
> arDates(i) = cWorkday
> next
> for i = 0 to ubound(arData,2)
> lNewUser = arData(cUser,i)
> if lCurUser <> lNewUser then
> 'pass arDates and lCurUser to a sub that creates the
> 'table row for the user
> CreateUserRow arDates,lCurUser
> lCurUser = lNewUser
> for j = 1 to iDays
> arDates(j) = cWorkday
> next
> end if
> dHolStart = CDate(arData(cFrom,i))
> iStartDay = Day(dHolStart)
> dHolEnd= CDate(arData(cTo,i))
> iEndDay= Day(dHolEnd)
> if dHolEnd <= iStartDay then iStartDay =1
> For j = iStartDay to iEndDay
> arDates(j) = cHoliday
> next
> next
> CreateUserRow arDates,lCurUser
> response.write "</table>"
> erase arData
> erase arDates
> end if
>
> Sub CreateHeading(pAr,pStart)
> dim i
> response.write "<TABLE border=1 style=""border-collapse:collapse"">" & _
> "<TR><TH colspan="
> response.write ubound(pAr) + 1
> response.write " align = center>Holidays for "
> response.write monthname(month(pStart)) & " " & year(pStart)
> response.write "</TH></TR><TR><TH>"
> response.write "User ID" & "</TH>"
> for i = 1 to ubound(pAr)
> response.write "<TH width=15>" & i & "</TH>"
> next
> response.write "</TR>"
> end sub
>
> sub CreateUserRow(pAr, pUser)
> dim i
> response.write "<tr><th>" & pUser & "</th>"
> for i = 1 to ubound(pAr)
> response.write "<td bgcolor= "
> response.write pAr(i)
> response.write ">&nbsp;&nbsp;</td>"
> next
> response.write "</tr>"
> end sub
>
> %>
>
> HTH,
> Bob Barrows
>
>

[Bob Barrows]

Let me try it. I'll get back to you.

Bob
J P Singh wrote:

> Hi Bob I have tried the code by changing it for my database but it
> doesn't seems to give me correct result.
>
> The results it gives me is as below
>
> Holidays for July 2003
> User ID 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
> 22 23 24 25 26 27 28 29 30 31
> 219104
> 219232
> 219104
> 218313
>
>
> The data in my query in access is
>
> EmployeeNumber Lastname Firstname UserID FDate TDate Status
> Nodays Department
> 218313 Carter Ian 218313 01-Jul-03 04-Jul-03 3 4 Information
> Systems 218313 Carter Ian 218313 21-Jul-03 25-Jul-03 3 5
> Information Systems 219104 Singh Jaspinder 219104 01-Jul-03
> 01-Jul-03 3 1 Information
> Systems
> 219104 Singh Jaspinder 219104 05-Jul-03 07-Jul-03 3 3
> Information Systems
> 219104 Singh Jaspinder 219104 10-Jul-03 12-Jul-03 3 3
> Information Systems
> 219104 Singh Jaspinder 219104 15-Jul-03 16-Jul-03 3 1
> Information Systems
> 219104 Singh Jaspinder 219104 20-Jul-03 22-Jul-03 3 3
> Information Systems
> 219232 Whitebread James 219232 15-Jul-03 19-Jul-03 3 5
> Information Systems
> 219232 Whitebread James 219232 22-Jul-03 25-Jul-03 3 4
> Information Systems
> 219232 Whitebread James 219232 27-Jul-03 27-Jul-03 3 1
> Information Systems
> 219232 Whitebread James 219232 29-Jul-03 29-Jul-03 3 1
> Information Systems
>
>
> An surely according to the data the above graph is not correct.
>
> issues
>
> 1. First of all it has two rows for the same employee ie. 219104
> which is wrong
>
> 2. The data plotted is completely incorrect.
>
> Maybe be I have missed something but I have pasted the modified code
> below. Can you please help
>
> Thanks - Jas
>
> <%@ Language=vbScript %>
> <!--#include file="conn.asp"-->
> <!--#include file="header.asp"-->
>
> <%
>
> dim iMth, iYr, dStart,dEnd,arDates(),arData,cn,rs
> dim iDays, iStartDay, iEndDay
> dim lCurUser, lNewUser
> dim i, j, dHolStart, dHolEnd,k
> const cUser = 0
> const cFrom = 1
> const cTo = 2
> const cHoliday = "yellow"
> const cWorkday = "white"
>
> iMth=request.form("mth")
> if len(iMth) = 0 then iMth = 7
> iYr=request.form("yr")
> if len(iYr) = 0 then iYr = 2003
>
> 'validate them, then
> dStart=dateserial(iYr,iMth,1)
> dEnd=dateadd("m",1,dStart)
> dEnd= dateadd("d",-1,dEnd)
> iDays = Day(dEnd)
> Redim arDates(iDays)
> 'this is effectively a one-based array - we will ignore the
> 'zero element in later code
> set rs=server.createobject("adodb.recordset")
>
> 'strSQL = "select * from holidaysview where fdate between #" & sdate
> & "# and #" & edate & "# order by userid, fdate"
>
> strSQL = "select * from holidayrequesrs order by userid, fdate"
>
>
>
> rs.CursorType = 2
> rs.LockType = 3
> 'rs.Open strSQL, conn
>
>
> conn.holidaysview dEnd, rs
> if not rs.eof then
> arData=rs.Getrows
> end if
> rs.close
> set rs=nothing
> conn.close
> set conn=nothing
> if not isArray(arData) then
> response.write "No records returned"
> else
> 'pass arDates and dStart to a sub that creates your
> 'table heading
> CreateHeading arDates, dStart
> lCurUser = arData(cUser,0)
> for i = 1 to iDays
> arDates(i) = cWorkday
> next
> for i = 0 to ubound(arData,2)
> lNewUser = arData(cUser,i)
> if lCurUser <> lNewUser then
> 'pass arDates and lCurUser to a sub that creates the
> 'table row for the user
> CreateUserRow arDates,lCurUser
> lCurUser = lNewUser
> for j = 1 to iDays
> arDates(j) = cWorkday
> next
> end if
> dHolStart = CDate(arData(FDate,i))
> iStartDay = Day(dHolStart)
> dHolEnd= CDate(arData(Tdate,i))
> iEndDay= Day(dHolEnd)
> if dHolEnd <= iStartDay then iStartDay =1
> For j = iStartDay to iEndDay
> arDates(j) = cHoliday
> next
> next
> CreateUserRow arDates,lCurUser
> response.write "</table>"
> erase arData
> erase arDates
> end if
>
> Sub CreateHeading(pAr,pStart)
> dim i
> response.write "<TABLE border=1 style=""border-collapse:collapse"">"
> & _ "<TR><TH colspan="
> response.write ubound(pAr) + 1
> response.write " align = center>Holidays for "
> response.write monthname(month(pStart)) & " " & year(pStart)
> response.write "</TH></TR><TR><TH>"
> response.write "User ID" & "</TH>"
> for i = 1 to ubound(pAr)
> response.write "<TH width=15>" & i & "</TH>"
> next
> response.write "</TR>"
> end sub
>
> sub CreateUserRow(pAr, pUser)
> dim i
> response.write "<tr><th>" & pUser & "</th>"
> for i = 1 to ubound(pAr)
> response.write "<td bgcolor= "
> response.write pAr(i)
> response.write ">&nbsp;&nbsp;</td>"
> next
> response.write "</tr>"
> end sub
>
> %>
>
> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> news:ukuMjyiUDHA.2456@TK2MSFTNGP09.phx.gbl...

>> OK, I've cleaned up the errors and tested it. here's what it looks
>> like now:
>>
>> <%@ Language=vbScript %>
>> <%
>> dim iMth, iYr, dStart,dEnd,arDates(),arData,cn,rs
>> dim iDays, iStartDay, iEndDay
>> dim lCurUser, lNewUser
>> dim i, j, dHolStart, dHolEnd,k
>> const cUser = 0
>> const cFrom = 1
>> const cTo = 2
>> const cHoliday = "yellow"
>> const cWorkday = "white"
>>
>> iMth=request.form("mth")
>> if len(iMth) = 0 then iMth = 7
>> iYr=request.form("yr")
>> if len(iYr) = 0 then iYr = 2003
>>
>> 'validate them, then
>> dStart=dateserial(iYr,iMth,1)
>> dEnd=dateadd("m",1,dStart)
>> dEnd= dateadd("d",-1,dEnd)
>> iDays = Day(dEnd)
>> Redim arDates(iDays)
>> 'this is effectively a one-based array - we will ignore the
>> 'zero element in later code
>>
>> set cn=server.CreateObject("adodb.connection")
>> Cn.open "Provider=SQLOLEDB;Initial Catalog=" & _
>> "Pubs;Data Source=censored" & _
>> ";UID=censored;Password=censored"
>>
>> set rs=server.createobject("adodb.recordset")
>> cn.GetHolidays dEnd, rs
>> if not rs.eof then
>> arData=rs.Getrows
>> end if
>> rs.close
>> set rs=nothing
>> cn.close
>> set cn=nothing
>> if not isArray(arData) then
>> response.write "No records returned"
>> else
>> 'pass arDates and dStart to a sub that creates your
>> 'table heading
>> CreateHeading arDates, dStart
>> lCurUser = arData(cUser,0)
>> for i = 1 to iDays
>> arDates(i) = cWorkday
>> next
>> for i = 0 to ubound(arData,2)
>> lNewUser = arData(cUser,i)
>> if lCurUser <> lNewUser then
>> 'pass arDates and lCurUser to a sub that creates the
>> 'table row for the user
>> CreateUserRow arDates,lCurUser
>> lCurUser = lNewUser
>> for j = 1 to iDays
>> arDates(j) = cWorkday
>> next
>> end if
>> dHolStart = CDate(arData(cFrom,i))
>> iStartDay = Day(dHolStart)
>> dHolEnd= CDate(arData(cTo,i))
>> iEndDay= Day(dHolEnd)
>> if dHolEnd <= iStartDay then iStartDay =1
>> For j = iStartDay to iEndDay
>> arDates(j) = cHoliday
>> next
>> next
>> CreateUserRow arDates,lCurUser
>> response.write "</table>"
>> erase arData
>> erase arDates
>> end if
>>
>> Sub CreateHeading(pAr,pStart)
>> dim i
>> response.write "<TABLE border=1 style=""border-collapse:collapse"">"
>> & _ "<TR><TH colspan="
>> response.write ubound(pAr) + 1
>> response.write " align = center>Holidays for "
>> response.write monthname(month(pStart)) & " " & year(pStart)
>> response.write "</TH></TR><TR><TH>"
>> response.write "User ID" & "</TH>"
>> for i = 1 to ubound(pAr)
>> response.write "<TH width=15>" & i & "</TH>"
>> next
>> response.write "</TR>"
>> end sub
>>
>> sub CreateUserRow(pAr, pUser)
>> dim i
>> response.write "<tr><th>" & pUser & "</th>"
>> for i = 1 to ubound(pAr)
>> response.write "<td bgcolor= "
>> response.write pAr(i)
>> response.write ">&nbsp;&nbsp;</td>"
>> next
>> response.write "</tr>"
>> end sub
>>
>> %>
>>
>> HTH,
>> Bob Barrows

[Bob Barrows]

First question: whay are there two columns containing the same data
(EmployeeNumber and UserID)?

Bob
J P Singh wrote:

> Hi Bob I have tried the code by changing it for my database but it
> doesn't seems to give me correct result.
>
> The results it gives me is as below
>
> Holidays for July 2003
> User ID 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
> 22 23 24 25 26 27 28 29 30 31
> 219104
> 219232
> 219104
> 218313
>
>
> The data in my query in access is
>
> EmployeeNumber Lastname Firstname UserID FDate TDate Status
> Nodays Department
> 218313 Carter Ian 218313 01-Jul-03 04-Jul-03 3 4 Information
> Systems 218313 Carter Ian 218313 21-Jul-03 25-Jul-03 3 5
> Information Systems 219104 Singh Jaspinder 219104 01-Jul-03
> 01-Jul-03 3 1 Information
> Systems
> 219104 Singh Jaspinder 219104 05-Jul-03 07-Jul-03 3 3
> Information Systems
> 219104 Singh Jaspinder 219104 10-Jul-03 12-Jul-03 3 3
> Information Systems
> 219104 Singh Jaspinder 219104 15-Jul-03 16-Jul-03 3 1
> Information Systems
> 219104 Singh Jaspinder 219104 20-Jul-03 22-Jul-03 3 3
> Information Systems
> 219232 Whitebread James 219232 15-Jul-03 19-Jul-03 3 5
> Information Systems
> 219232 Whitebread James 219232 22-Jul-03 25-Jul-03 3 4
> Information Systems
> 219232 Whitebread James 219232 27-Jul-03 27-Jul-03 3 1
> Information Systems
> 219232 Whitebread James 219232 29-Jul-03 29-Jul-03 3 1
> Information Systems
>
>
> An surely according to the data the above graph is not correct.
>
> issues
>
> 1. First of all it has two rows for the same employee ie. 219104
> which is wrong
>
> 2. The data plotted is completely incorrect.
>
> Maybe be I have missed something but I have pasted the modified code
> below. Can you please help
>
> Thanks - Jas
>
> <%@ Language=vbScript %>
> <!--#include file="conn.asp"-->
> <!--#include file="header.asp"-->
>
> <%
>
> dim iMth, iYr, dStart,dEnd,arDates(),arData,cn,rs
> dim iDays, iStartDay, iEndDay
> dim lCurUser, lNewUser
> dim i, j, dHolStart, dHolEnd,k
> const cUser = 0
> const cFrom = 1
> const cTo = 2
> const cHoliday = "yellow"
> const cWorkday = "white"
>
> iMth=request.form("mth")
> if len(iMth) = 0 then iMth = 7
> iYr=request.form("yr")
> if len(iYr) = 0 then iYr = 2003
>
> 'validate them, then
> dStart=dateserial(iYr,iMth,1)
> dEnd=dateadd("m",1,dStart)
> dEnd= dateadd("d",-1,dEnd)
> iDays = Day(dEnd)
> Redim arDates(iDays)
> 'this is effectively a one-based array - we will ignore the
> 'zero element in later code
> set rs=server.createobject("adodb.recordset")
>
> 'strSQL = "select * from holidaysview where fdate between #" & sdate
> & "# and #" & edate & "# order by userid, fdate"
>
> strSQL = "select * from holidayrequesrs order by userid, fdate"
>
>
>
> rs.CursorType = 2
> rs.LockType = 3
> 'rs.Open strSQL, conn
>
>
> conn.holidaysview dEnd, rs
> if not rs.eof then
> arData=rs.Getrows
> end if
> rs.close
> set rs=nothing
> conn.close
> set conn=nothing
> if not isArray(arData) then
> response.write "No records returned"
> else
> 'pass arDates and dStart to a sub that creates your
> 'table heading
> CreateHeading arDates, dStart
> lCurUser = arData(cUser,0)
> for i = 1 to iDays
> arDates(i) = cWorkday
> next
> for i = 0 to ubound(arData,2)
> lNewUser = arData(cUser,i)
> if lCurUser <> lNewUser then
> 'pass arDates and lCurUser to a sub that creates the
> 'table row for the user
> CreateUserRow arDates,lCurUser
> lCurUser = lNewUser
> for j = 1 to iDays
> arDates(j) = cWorkday
> next
> end if
> dHolStart = CDate(arData(FDate,i))
> iStartDay = Day(dHolStart)
> dHolEnd= CDate(arData(Tdate,i))
> iEndDay= Day(dHolEnd)
> if dHolEnd <= iStartDay then iStartDay =1
> For j = iStartDay to iEndDay
> arDates(j) = cHoliday
> next
> next
> CreateUserRow arDates,lCurUser
> response.write "</table>"
> erase arData
> erase arDates
> end if
>
> Sub CreateHeading(pAr,pStart)
> dim i
> response.write "<TABLE border=1 style=""border-collapse:collapse"">"
> & _ "<TR><TH colspan="
> response.write ubound(pAr) + 1
> response.write " align = center>Holidays for "
> response.write monthname(month(pStart)) & " " & year(pStart)
> response.write "</TH></TR><TR><TH>"
> response.write "User ID" & "</TH>"
> for i = 1 to ubound(pAr)
> response.write "<TH width=15>" & i & "</TH>"
> next
> response.write "</TR>"
> end sub
>
> sub CreateUserRow(pAr, pUser)
> dim i
> response.write "<tr><th>" & pUser & "</th>"
> for i = 1 to ubound(pAr)
> response.write "<td bgcolor= "
> response.write pAr(i)
> response.write ">&nbsp;&nbsp;</td>"
> next
> response.write "</tr>"
> end sub
>
> %>
>
> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> news:ukuMjyiUDHA.2456@TK2MSFTNGP09.phx.gbl...

>> OK, I've cleaned up the errors and tested it. here's what it looks
>> like now:
>>
>> <%@ Language=vbScript %>
>> <%
>> dim iMth, iYr, dStart,dEnd,arDates(),arData,cn,rs
>> dim iDays, iStartDay, iEndDay
>> dim lCurUser, lNewUser
>> dim i, j, dHolStart, dHolEnd,k
>> const cUser = 0
>> const cFrom = 1
>> const cTo = 2
>> const cHoliday = "yellow"
>> const cWorkday = "white"
>>
>> iMth=request.form("mth")
>> if len(iMth) = 0 then iMth = 7
>> iYr=request.form("yr")
>> if len(iYr) = 0 then iYr = 2003
>>
>> 'validate them, then
>> dStart=dateserial(iYr,iMth,1)
>> dEnd=dateadd("m",1,dStart)
>> dEnd= dateadd("d",-1,dEnd)
>> iDays = Day(dEnd)
>> Redim arDates(iDays)
>> 'this is effectively a one-based array - we will ignore the
>> 'zero element in later code
>>
>> set cn=server.CreateObject("adodb.connection")
>> Cn.open "Provider=SQLOLEDB;Initial Catalog=" & _
>> "Pubs;Data Source=censored" & _
>> ";UID=censored;Password=censored"
>>
>> set rs=server.createobject("adodb.recordset")
>> cn.GetHolidays dEnd, rs
>> if not rs.eof then
>> arData=rs.Getrows
>> end if
>> rs.close
>> set rs=nothing
>> cn.close
>> set cn=nothing
>> if not isArray(arData) then
>> response.write "No records returned"
>> else
>> 'pass arDates and dStart to a sub that creates your
>> 'table heading
>> CreateHeading arDates, dStart
>> lCurUser = arData(cUser,0)
>> for i = 1 to iDays
>> arDates(i) = cWorkday
>> next
>> for i = 0 to ubound(arData,2)
>> lNewUser = arData(cUser,i)
>> if lCurUser <> lNewUser then
>> 'pass arDates and lCurUser to a sub that creates the
>> 'table row for the user
>> CreateUserRow arDates,lCurUser
>> lCurUser = lNewUser
>> for j = 1 to iDays
>> arDates(j) = cWorkday
>> next
>> end if
>> dHolStart = CDate(arData(cFrom,i))
>> iStartDay = Day(dHolStart)
>> dHolEnd= CDate(arData(cTo,i))
>> iEndDay= Day(dHolEnd)
>> if dHolEnd <= iStartDay then iStartDay =1
>> For j = iStartDay to iEndDay
>> arDates(j) = cHoliday
>> next
>> next
>> CreateUserRow arDates,lCurUser
>> response.write "</table>"
>> erase arData
>> erase arDates
>> end if
>>
>> Sub CreateHeading(pAr,pStart)
>> dim i
>> response.write "<TABLE border=1 style=""border-collapse:collapse"">"
>> & _ "<TR><TH colspan="
>> response.write ubound(pAr) + 1
>> response.write " align = center>Holidays for "
>> response.write monthname(month(pStart)) & " " & year(pStart)
>> response.write "</TH></TR><TR><TH>"
>> response.write "User ID" & "</TH>"
>> for i = 1 to ubound(pAr)
>> response.write "<TH width=15>" & i & "</TH>"
>> next
>> response.write "</TR>"
>> end sub
>>
>> sub CreateUserRow(pAr, pUser)
>> dim i
>> response.write "<tr><th>" & pUser & "</th>"
>> for i = 1 to ubound(pAr)
>> response.write "<td bgcolor= "
>> response.write pAr(i)
>> response.write ">&nbsp;&nbsp;</td>"
>> next
>> response.write "</tr>"
>> end sub
>>
>> %>
>>
>> HTH,
>> Bob Barrows

[J P Singh]

Hi Bob

I have tried but it still doesn't work.

Sorry to bug you mate but could you zip the file and the access database you
created so I can test it locally here.

If you prefer to send it to my email can you please send it
distributor14**1236@softhome.net

if you remove 14**1236 from my email address please

Thanks a lot for your help

Jas

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:#0JeswrUDHA.2224@TK2MSFTNGP10.phx.gbl...

> Bob Barrows wrote:

> > OK, I created a table called Holidays in Access, populated it with
> > your supplied data, and created a saved parameter query called
> > qGetHoldays using the following SQL (I've modified this from my last
> > message due to the flaw that the original query would return all
> > records for the specified month plus all previous months):
> >
> > SELECT Holidays.UserID, Holidays.FDate, Holidays.TDate
> > FROM Holidays
> > WHERE Holidays.FDate Between [p1] and [p2] OR
> > Holidays.TDate Between [p1] and [p2]
> >

> Actually, now that I think about it, this SQL should work just as well:
>
> SELECT Holidays.UserID, Holidays.FDate, Holidays.TDate
> FROM Holidays
> WHERE Holidays.TDate Between [p1] and [p2]
>
> Bob Barrows
>
>

[Bob Barrows]

Bob Barrows wrote:

> Bob Barrows wrote:

>> OK, I created a table called Holidays in Access, populated it with
>> your supplied data, and created a saved parameter query called
>> qGetHoldays using the following SQL (I've modified this from my last
>> message due to the flaw that the original query would return all
>> records for the specified month plus all previous months):
>>
>> SELECT Holidays.UserID, Holidays.FDate, Holidays.TDate
>> FROM Holidays
>> WHERE Holidays.FDate Between [p1] and [p2] OR
>> Holidays.TDate Between [p1] and [p2]
>>

> Actually, now that I think about it, this SQL should work just as
> well:
>
> SELECT Holidays.UserID, Holidays.FDate, Holidays.TDate
> FROM Holidays
> WHERE Holidays.TDate Between [p1] and [p2]
>

No, it won't: you need to check both From and To dates.
Bob

[Bob Barrows]

Bob Barrows wrote:
<snip>

> dHolStart = CDate(arData(FDate,i))

>> iStartDay = Day(dHolStart)
>> dHolEnd= CDate(arData(Tdate,i))
>> iEndDay= Day(dHolEnd)
>> if dHolEnd <= iStartDay then iStartDay =1

This mod needs to be made:
if month(dHolStart)<>iMth then iStartDay =1
if month(dHolEnd)<>iMth then iEndDay =day(dEnd)

Bob Barrows

[J P Singh]

Hi Bob

Thanks for sending the code through and it does work. Fantastic.

Just wondering if you could help me refine it a bit

What I want to do is to plot the correct days off booked by the employee.

So I might have booked say

Start Date 24/07/2003 to 31/07/2003

I would like to plot it but leave out all the weekend

so in the above scenario the days plotted should be

24/25/28/29/20/31 July

and 26 and 27th Of July should be left out as they are weekend

Regards

Jas
"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:#PjXtzBVDHA.1816@TK2MSFTNGP09.phx.gbl...

> Bob Barrows wrote:
> <snip>

> > dHolStart = CDate(arData(FDate,i))

> >> iStartDay = Day(dHolStart)
> >> dHolEnd= CDate(arData(Tdate,i))
> >> iEndDay= Day(dHolEnd)
> >> if dHolEnd <= iStartDay then iStartDay =1

>
> This mod needs to be made:
> if month(dHolStart)<>iMth then iStartDay =1
> if month(dHolEnd)<>iMth then iEndDay =day(dEnd)
>
> Bob Barrows
>
>