By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,473 Members | 3,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,473 IT Pros & Developers. It's quick & easy.

dating issues

P: 7
ok im faced with two tasks with my database.

firstly i need to etract a weekday (field) from a date (field) - so that when a date is entered, it looks at the date and calculates the day. ive done this before but long since lost the code to generate a weekday from it.

Secondly i have a table in my database that records the attendance of students to dance classes, i want the system to flag four in a row "non attendances" to any class in order to alert an operator of the system,so that they can then action a procedure(external to the database).

Any ideas?
Oct 17 '07 #1
Share this Question
Share on Google+
14 Replies


ADezii
Expert 5K+
P: 8,597
ok im faced with two tasks with my database.

firstly i need to etract a weekday (field) from a date (field) - so that when a date is entered, it looks at the date and calculates the day. ive done this before but long since lost the code to generate a weekday from it.

Secondly i have a table in my database that records the attendance of students to dance classes, i want the system to flag four in a row "non attendances" to any class in order to alert an operator of the system,so that they can then action a procedure(external to the database).

Any ideas?
Assuming the 1st Day of the Week is set to Sunday:
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalculateWeekday(dteDate As Date) As String
  2. Select Case Weekday(dteDate)
  3.   Case 1
  4.     fCalculateWeekday = "Sunday"
  5.   Case 2
  6.     fCalculateWeekday = "Monday"
  7.   Case 3
  8.     fCalculateWeekday = "Tuesday"
  9.   Case 4
  10.     fCalculateWeekday = "Wednesday"
  11.   Case 5
  12.     fCalculateWeekday = "Thursday"
  13.   Case 6
  14.     fCalculateWeekday = "Friday"
  15.   Case 7
  16.     fCalculateWeekday = "Saturday"
  17. End Select
  18. End Function
Expand|Select|Wrap|Line Numbers
  1. ? fCalculateWeekday(#10/17/2007#)
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Wednesday
Oct 17 '07 #2

FishVal
Expert 2.5K+
P: 2,653
Or even simpler.

Expand|Select|Wrap|Line Numbers
  1. ? Format(#10/17/2007#, "dddd")
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Wednesday
[/quote]
Oct 17 '07 #3

ADezii
Expert 5K+
P: 8,597
Or even simpler.

Expand|Select|Wrap|Line Numbers
  1. ? Format(#10/17/2007#, "dddd")
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Wednesday
Much, much simpler and definately more practical. I'll blame it on memory lapse.
Oct 17 '07 #4

P: 7
excellent! however i was probbaly not entirely clear in whati was asking. the date needs to be entered into a field (from a text box) and fromt his generate the weekday into a seperate field (and display in another text box or a label) on a form,but store the inputted and generated info to the table that the fields refer to.

I havent used access in about a year,which is why im seeming like a total dummy, but once ive got things working i suppose ill be back on track.
Oct 17 '07 #5

FishVal
Expert 2.5K+
P: 2,653
excellent! however i was probbaly not entirely clear in whati was asking. the date needs to be entered into a field (from a text box) and fromt his generate the weekday into a seperate field (and display in another text box or a label) on a form,but store the inputted and generated info to the table that the fields refer to.

I havent used access in about a year,which is why im seeming like a total dummy, but once ive got things working i suppose ill be back on track.
You don't need to store weekday in separate table field. Though you've not used Access for a year the rule not to store duplicate information is still relevant.
You may include weekday calculated field in query - your form RowSource:

something like
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl.*, Format(tbl.dteYourDateField, "dddd") AS txtWeekDay FROM tbl;
  2.  
and set weekday form control ControlSource property to "txtWeekDay"

or

set weekday form control ControlSource property to something like
Expand|Select|Wrap|Line Numbers
  1. =Format([dteYourDateField], "dddd")
  2.  
or

populate weekday unbound textbox via VBA code

Storing weekday in separate table field is not an option.
Oct 17 '07 #6

P: 7
cool!

Now im faced with another interesting paradigm of problems peripheral to the original dating issues. Ideally,i need to design a query will select records from a table (attendance to classes) by a date rangeand / or by class type. What would be the best way for the operator of the query (eventually a report in ideal situations), to generate attendance data from a date range?

I know its a bit "vague" and can provide table information if necessary.
Oct 27 '07 #7

P: 7
forget the last post. i managed to sort that out this morning.

instead,the last challenge im faced with is thus:

I need to design a querythat will automatically generate a report (or at least collate data) that looks at multiple records for a boolean value that is consistant over a date range (ie four weeks). The theory for this is that the table in question records the attendance of classes, and that a non attendance by a person four weeks running will therefore be flagged by the system and will generate a report for this.

its a bit beyond me but im confident once i understand how to implement it ill be well away. any ideas folks?
Oct 28 '07 #8

FishVal
Expert 2.5K+
P: 2,653
Please include the MetaData for all relevant datasets. Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Oct 28 '07 #9

P: 7
Fields for the table are as follows:

member name (text) (fk)
venue (text) (fk)
ClassDay (Text)
Clasdate (Date & time)
Class (text) (fk)
Time slot (text) (fk)
Attendance (boolean)
Oct 29 '07 #10

FishVal
Expert 2.5K+
P: 2,653
Fields for the table are as follows:

member name (text) (fk)
venue (text) (fk)
ClassDay (Text)
Clasdate (Date & time)
Class (text) (fk)
Time slot (text) (fk)
Attendance (boolean)
I need some more explanations of how do you store data in the table.
  • Do you still store date and weekday in separate fields ?
  • What is [Time slot] field for?
  • What is [Attendance] field for? Does it mean a record is made when a member is absent?
Oct 29 '07 #11

P: 7
Attendance marks the presence of a student to a particular class

Time slot indicates the time of day in which the class takes place (ie 1pm-2pm)

Weekday is stored in a seperate table and accessed using a lookup / row source (to stop the idiots using the final product from getting it wrong)
Oct 29 '07 #12

FishVal
Expert 2.5K+
P: 2,653
The following query will give you most resent attendance date for each member.
(Suppose [Clasdate] field is for that.)

Expand|Select|Wrap|Line Numbers
  1. SELECT tblYourTable.[member name], Max(tblYourTable.[Clasdate]) AS dteMostResentMemberAttendance
  2. FROM tblYourTable WHERE  tblYourTable.[Attendance]
  3. GROUP BY tblYourTable.[member name];
  4.  
Save this query, e.g. [qry1] and build another query based on [qry1].

Expand|Select|Wrap|Line Numbers
  1. SELECT qry1.* FROM qry1 WHERE DateDiff("ww", qry1.dteMostResentMemberAttendance, Date()) > 4;
  2.  
Oct 29 '07 #13

P: 7
Finally i am faced with another interesting conundrum.

In my main members table there are two classifications for students, kids and adults. I need a function that when someone turns over 16, they are assigned to the adult category. I apreciatethis may be done by using the date of brith field to calculate age - but i have no idea how to go about it.

Any ideas, boffins?
Jan 8 '08 #14

FishVal
Expert 2.5K+
P: 2,653
Hi, there.

The following expression will return true for a person whos age is 16 years and older.

Date() >= DateAdd("yyyy",16, [dteBirthDay])

I would strongly discourage you from storing this as separate field in the table unless you want to run update query daily.

Regards,
Fish
Jan 8 '08 #15

Post your reply

Sign in to post your reply or Sign up for a free account.