If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating no of weeks in access and flag recor if no of weeks is
I have set up a form which has a 'date on hire' and 'date off hire' field. I
want to be able to say to the form, look at this record (hire no) if the date of hire is blank, then calculate the no of weeks between now and the date on hire and flag is 5 weeks or more. How cann I do this please? |
#2
|
|||
|
|||
in a query: (table1 -- should be replaced with your table number, and you
will need to add the [hire no] field. SELECT Table1.[Date On Hire], Table1.[Date Off Hire], IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0) AS WeeksOffHire FROM Table1 WHERE (((IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0))=5)); Ed Warren "MitziUK" wrote in message ... I have set up a form which has a 'date on hire' and 'date off hire' field. I want to be able to say to the form, look at this record (hire no) if the date of hire is blank, then calculate the no of weeks between now and the date on hire and flag is 5 weeks or more. How cann I do this please? |
#3
|
|||
|
|||
How does this flag the form thou?
I want the user to be able to see at a glance (whether from a colour change (or anything else) that say's "hey I've been on hire now for 5 weeks - do something with me" "Ed Warren" wrote: in a query: (table1 -- should be replaced with your table number, and you will need to add the [hire no] field. SELECT Table1.[Date On Hire], Table1.[Date Off Hire], IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0) AS WeeksOffHire FROM Table1 WHERE (((IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0))=5)); Ed Warren "MitziUK" wrote in message ... I have set up a form which has a 'date on hire' and 'date off hire' field. I want to be able to say to the form, look at this record (hire no) if the date of hire is blank, then calculate the no of weeks between now and the date on hire and flag is 5 weeks or more. How cann I do this please? |
#4
|
|||
|
|||
Ed - The query works thanks, but is there any way of getting the form (based
on the Hire No given in the query) to flag in some way - ie change colour "MitziUK" wrote: How does this flag the form thou? I want the user to be able to see at a glance (whether from a colour change (or anything else) that say's "hey I've been on hire now for 5 weeks - do something with me" "Ed Warren" wrote: in a query: (table1 -- should be replaced with your table number, and you will need to add the [hire no] field. SELECT Table1.[Date On Hire], Table1.[Date Off Hire], IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0) AS WeeksOffHire FROM Table1 WHERE (((IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0))=5)); Ed Warren "MitziUK" wrote in message ... I have set up a form which has a 'date on hire' and 'date off hire' field. I want to be able to say to the form, look at this record (hire no) if the date of hire is blank, then calculate the no of weeks between now and the date on hire and flag is 5 weeks or more. How cann I do this please? |
#5
|
|||
|
|||
Depends on the version of access:
version 2002 or 2003 you can set a conditional formatting. version 97, 2000 you can have to do this with code, someone else will have to help here Ed Warren. "MitziUK" wrote in message ... Ed - The query works thanks, but is there any way of getting the form (based on the Hire No given in the query) to flag in some way - ie change colour "MitziUK" wrote: How does this flag the form thou? I want the user to be able to see at a glance (whether from a colour change (or anything else) that say's "hey I've been on hire now for 5 weeks - do something with me" "Ed Warren" wrote: in a query: (table1 -- should be replaced with your table number, and you will need to add the [hire no] field. SELECT Table1.[Date On Hire], Table1.[Date Off Hire], IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0) AS WeeksOffHire FROM Table1 WHERE (((IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0))=5)); Ed Warren "MitziUK" wrote in message ... I have set up a form which has a 'date on hire' and 'date off hire' field. I want to be able to say to the form, look at this record (hire no) if the date of hire is blank, then calculate the no of weeks between now and the date on hire and flag is 5 weeks or more. How cann I do this please? |
#6
|
|||
|
|||
Well I guess I've hit a brick wall. LOL.
the database was originally created in Access 2003 and converted to Access 97 via the Tools, Convert database option. and then saved onto a network drive for other users to gain access to the database (as they only have Access 97 on their systems). So if anyone out there can come uo with some 'Code' or point me in the right direction I'd be very very grateful. "Ed Warren" wrote: Depends on the version of access: version 2002 or 2003 you can set a conditional formatting. version 97, 2000 you can have to do this with code, someone else will have to help here Ed Warren. "MitziUK" wrote in message ... Ed - The query works thanks, but is there any way of getting the form (based on the Hire No given in the query) to flag in some way - ie change colour "MitziUK" wrote: How does this flag the form thou? I want the user to be able to see at a glance (whether from a colour change (or anything else) that say's "hey I've been on hire now for 5 weeks - do something with me" "Ed Warren" wrote: in a query: (table1 -- should be replaced with your table number, and you will need to add the [hire no] field. SELECT Table1.[Date On Hire], Table1.[Date Off Hire], IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0) AS WeeksOffHire FROM Table1 WHERE (((IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0))=5)); Ed Warren "MitziUK" wrote in message ... I have set up a form which has a 'date on hire' and 'date off hire' field. I want to be able to say to the form, look at this record (hire no) if the date of hire is blank, then calculate the no of weeks between now and the date on hire and flag is 5 weeks or more. How cann I do this please? |
#7
|
|||
|
|||
You could put the following in the 'oncurrent event':
Private Sub Form_Current() Dim response As Integer If Me.WeeksOffHire = 5 Then response = MsgBox("Hey Dilbert you been Off Hire for over 5 weeks", vbOKOnly) or some more appropriate message End If End Sub or: add a text field to the form 'header' (txtOverFive) set the Data to: ="Hey Dilbert you been Off hire for over 5 weeks") or some more appropriate message set visible to false (don't see it until event fired) set background color to: red Private Sub Form_Current() Dim response As Integer If Me.WeeksOffHire = 5 Then me.txtOverFive.Visible = true else me.txtOverFive.Visible = false End If Ed Warren dim response as integer "MitziUK" wrote in message ... Well I guess I've hit a brick wall. LOL. the database was originally created in Access 2003 and converted to Access 97 via the Tools, Convert database option. and then saved onto a network drive for other users to gain access to the database (as they only have Access 97 on their systems). So if anyone out there can come uo with some 'Code' or point me in the right direction I'd be very very grateful. "Ed Warren" wrote: Depends on the version of access: version 2002 or 2003 you can set a conditional formatting. version 97, 2000 you can have to do this with code, someone else will have to help here Ed Warren. "MitziUK" wrote in message ... Ed - The query works thanks, but is there any way of getting the form (based on the Hire No given in the query) to flag in some way - ie change colour "MitziUK" wrote: How does this flag the form thou? I want the user to be able to see at a glance (whether from a colour change (or anything else) that say's "hey I've been on hire now for 5 weeks - do something with me" "Ed Warren" wrote: in a query: (table1 -- should be replaced with your table number, and you will need to add the [hire no] field. SELECT Table1.[Date On Hire], Table1.[Date Off Hire], IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0) AS WeeksOffHire FROM Table1 WHERE (((IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0))=5)); Ed Warren "MitziUK" wrote in message ... I have set up a form which has a 'date on hire' and 'date off hire' field. I want to be able to say to the form, look at this record (hire no) if the date of hire is blank, then calculate the no of weeks between now and the date on hire and flag is 5 weeks or more. How cann I do this please? |
#8
|
|||
|
|||
I wanted to get the week number based on one date in the table. I need the
week number to be able to graph info by week. Please help... Peg5566 "Ed Warren" wrote: in a query: (table1 -- should be replaced with your table number, and you will need to add the [hire no] field. SELECT Table1.[Date On Hire], Table1.[Date Off Hire], IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0) AS WeeksOffHire FROM Table1 WHERE (((IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0))=5)); Ed Warren "MitziUK" wrote in message ... I have set up a form which has a 'date on hire' and 'date off hire' field. I want to be able to say to the form, look at this record (hire no) if the date of hire is blank, then calculate the no of weeks between now and the date on hire and flag is 5 weeks or more. How cann I do this please? |
#9
|
|||
|
|||
My apoligies, I answered the question you asked, not the one you wanted the
answer to ; "want to be able to say to the form, look at this record (hire no) if the date of hire is blank, then calculate the no of weeks between now and the date on hire and flag if 5 weeks or more". I have not a clue how to calculate the week number based on a blank date. Maybe you need to start over and ask the question you want the answered. Note Pivot chart very neatly groups by years,quarter,months,weeks, days you may want to look there. Ed Warren "peg5566" wrote in message ... I wanted to get the week number based on one date in the table. I need the week number to be able to graph info by week. Please help... Peg5566 "Ed Warren" wrote: in a query: (table1 -- should be replaced with your table number, and you will need to add the [hire no] field. SELECT Table1.[Date On Hire], Table1.[Date Off Hire], IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0) AS WeeksOffHire FROM Table1 WHERE (((IIf(nz([date off hire],0)=0,DateDiff("d",[date on hire],Now())/7,0))=5)); Ed Warren "MitziUK" wrote in message ... I have set up a form which has a 'date on hire' and 'date off hire' field. I want to be able to say to the form, look at this record (hire no) if the date of hire is blank, then calculate the no of weeks between now and the date on hire and flag is 5 weeks or more. How cann I do this please? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can't Open Data Source (Query) where criteria is defined by functi | l_stocky | Mailmerge | 7 | January 22nd, 2005 05:09 AM |
check writing code | funkmaster22 | Using Forms | 2 | August 11th, 2004 09:35 PM |
Setting a red flag on a record in Access | LadyDi | General Discussion | 1 | July 27th, 2004 07:24 PM |
Access data base - read only | MHW | General Discussion | 14 | July 13th, 2004 09:15 AM |