Wrox Programmer Forums
|
Classic ASP Professional For advanced coder questions in ASP 3. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 13th, 2004, 08:35 AM
Authorized User
 
Join Date: Jun 2003
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default Time attendance database and coding

Hi all,

I am really stuck today and can use all your help on this subject.

I am making kinda like a punch-in card for work which will register your time of arrive and departure. I have a small system working in that the employee has an ID number and then when they log in with the EmployeeID and Password the database records the date and the time of arrival, then when they log out it records the exit time and then works out the amount of hours and minutes worked today.

However the problem arises when I want to call up a complete week or month. Our company doesn't work on a Sunday. For example if I call up the weeks information and it happens to be wednesday. I want it display the next 3 days (Thurs, Fri and Saturday) which will be empty cos they are in the future and the first 4 days (Sun, Mon, tue and wed). What I want to know is how can I extract this data. My TABLE consists of the following fields: EmployeeID, Date, TimeIn, TimeOut, HoursWorked, MinutesWorked.

I have been thinking about using the day indentifier which gives you a numeric for that day(eg. sun=0, mon=1 etc) but what happens when tuesday falls on the first of the month.

Are there any simpler ways to resolve this problem. Any ideas or feedback will be greatly recieved.

With many thanks for all your assistance and time,

Paul
 
Old August 13th, 2004, 11:36 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Paul,

Are you building the query in asp or the database?

Which database & asp language are you using?

Thanks,

Chris

 
Old August 13th, 2004, 11:47 AM
Authorized User
 
Join Date: Jun 2003
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Chris,

I am building the query with regular microsoft ASP language with VBScript. I am using Microsoft Access XP (2002) for the storage of the data.

Paul
 
Old August 13th, 2004, 12:23 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Paul,

This will return you the current week...

Code:
Dim currDate: currDate = Date()
Dim dayOfWeek: dayOfWeek = WeekDay(currDate, vbSunday)
Dim query
query = "SELECT * FROM yourtable WHERE [Date] BETWEEN #" & DateAdd("d", -dayOfWeek + 1, currDate) & "# " & _
        " AND #" & DateAdd("d", 7 - dayOfWeek, currDate) & "#;"
dayOfWeek, currDate) & "#;"
is this what you want or do you need to exclude days in months other than the current one?

Cheers,

Chris

 
Old August 13th, 2004, 04:45 PM
Authorized User
 
Join Date: Jun 2003
Posts: 46
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Chris,

So far so good, what I want to do now is list the days of the week individually Monday through Saturday, displaying the following data for each day: entrytime, ExitTime, HoursWorked, MinutesWorked

I guess I will need to make a loop of some kind just not quite sure how to structure it. Any help would be very much appreciated.

Thanks again for your help.

Paul
 
Old August 13th, 2004, 04:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Paul,

Code should look something like this.
Code:
Set Conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")

rs.Open query, Conn

If not rs.EOF Then
    While not rs.EOF then
        Response.write rs("Date") & " "
        Response.write rs("TimeIn") & " "
        Response.write rs("TimeOut") & " "
        Response.write rs("HoursWorked") & " "
        Response.write rs("MinutesWorked") & " "
    Wend
Else
Response.write "Records not found..."
End If
Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old August 17th, 2007, 02:24 AM
Registered User
 
Join Date: Aug 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:
Dim currDate: currDate = Date()
Dim dayOfWeek: dayOfWeek = WeekDay(currDate, vbSunday)
Dim query
query = "SELECT * FROM yourtable WHERE [Date] BETWEEN #" & DateAdd("d", -dayOfWeek + 1, currDate) & "# " & _
        " AND #" & DateAdd("d", 7 - dayOfWeek, currDate) & "#;"
dayOfWeek, currDate) & "#;"
3 line returning error ,check it

PAUL





Similar Threads
Thread Thread Starter Forum Replies Last Post
Store Procedure For Attendance prasanta2expert SQL Server 2000 4 July 5th, 2012 03:10 AM
create database in mysql through php coding vivekananth PHP Databases 2 January 31st, 2007 01:48 AM
Time attendance database and coding neo_jakey Classic ASP Basics 1 August 14th, 2004 02:20 AM
attendance scanning system rajanikrishna Classic ASP Databases 2 June 8th, 2004 04:30 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.