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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MitziUK - 18 Apr 2005 14:09 GMT

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?

Reply to this Message

Ed Warren - 18 Apr 2005 14:27 GMT

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
[quoted text clipped - 5 lines]
>
> How cann I do this please?

Reply to this Message

MitziUK - 18 Apr 2005 14:43 GMT

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.
[quoted text clipped - 16 lines]
> >
> > How cann I do this please?

Reply to this Message

MitziUK - 18 Apr 2005 15:11 GMT

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?
>
[quoted text clipped - 22 lines]
> > >
> > > How cann I do this please?

Reply to this Message

Ed Warren - 18 Apr 2005 17:07 GMT

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
[quoted text clipped - 31 lines]
>> > >
>> > > How cann I do this please?

Reply to this Message

MitziUK - 18 Apr 2005 20:16 GMT

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.
[quoted text clipped - 38 lines]
> >> > >
> >> > > How cann I do this please?

Reply to this Message

Ed Warren - 18 Apr 2005 21:34 GMT

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.
>
[quoted text clipped - 56 lines]
>> >> > >
>> >> > > How cann I do this please?

Reply to this Message

peg5566 - 30 Sep 2005 22:36 GMT

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.
[quoted text clipped - 16 lines]
> >
> > How cann I do this please?

Reply to this Message

Ed Warren - 30 Sep 2005 23:44 GMT

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.
[quoted text clipped - 24 lines]
>> >
>> > How cann I do this please?

Reply to this Message