Calculating no of weeks in access and flag recor if no of weeks is

[Guest]

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?

 

[Ed Warren]

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

 

[Guest]

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"

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

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?

 

[Guest]

Ed - The query works thanks, but is there any way of getting the form (based

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"

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

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?

 

[Ed Warren]

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.

Ed - The query works thanks, but is there any way of getting the form
(based

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"

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

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?

 

[Guest]

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.

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.

Ed - The query works thanks, but is there any way of getting the form
(based

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"

:

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

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?

 

[Ed Warren]

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

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.

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.

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

:

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"

:

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

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?

 

[Guest]

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

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

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?

 

[Ed Warren]

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

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

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

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?