|
 |
access thread: adding hours to a date time field
Message #1 by "Martyn Luck" <martyn.luck@p...> on Wed, 22 May 2002 11:29:11
|
|
I have a field in the database which is a date/time field, in a query i
need to show a new column called RealSLA, this field is a calculation of a
field call Opendate plus a number of hours, in the design view of the
query the new column is built like this RealSLA:[Opendate]+2. The two is
the number of hours that needs to be added onto the opendate datetime
value, however this calculation above adds 2 days onto the calculation,
i've tried using things like 0.5 but this also give a strange decimal in
the calculation.
heres and example
OpenDate RealSLA
22/05/2002 11:15:23 22/05/2002 13:15:23
using RealSLA:[OpenDate]+2
22/05/2002 11:15:23 24/05/2002 11:15:23
using RealSLA:[Opendate]+0.5
22/05/2002 11:15:23 3971.596452
I've also tried RealSLA:[Opendate]+ 00/00/0000 02:00:00
HELP !
Message #2 by braxis@b... on Wed, 22 May 2002 11:30:55 +0100 (BST)
|
|
Martyn
You need to use the DateAdd function:
DateAdd("h",2,[Opendate])
Brian
> from: Martyn Luck <martyn.luck@p...>
> date: Wed, 22 May 2002 12:29:11
> to: access@p...
> subject: Re: [access] adding hours to a date time field
>
> I have a field in the database which is a date/time field, in a query i
> need to show a new column called RealSLA, this field is a calculation of a
> field call Opendate plus a number of hours, in the design view of the
> query the new column is built like this RealSLA:[Opendate] 2. The two is
> the number of hours that needs to be added onto the opendate datetime
> value, however this calculation above adds 2 days onto the calculation,
> i've tried using things like 0.5 but this also give a strange decimal in
> the calculation.
>
> heres and example
>
> OpenDate RealSLA
> 22/05/2002 11:15:23 22/05/2002 13:15:23
>
> using RealSLA:[OpenDate] 2
>
> 22/05/2002 11:15:23 24/05/2002 11:15:23
>
> using RealSLA:[Opendate] 0.5
>
> 22/05/2002 11:15:23 3971.596452
>
> I've also tried RealSLA:[Opendate] 00/00/0000 02:00:00
>
> HELP !
Message #3 by "Foote, Chris" <Chris.Foote@u...> on Wed, 22 May 2002 11:31:34 +0100
|
|
Hi!
I understand that when using dates in VBA you need to "bracket" then with
hashes ie #22/06/02#
HTH - Spike
> -----Original Message-----
> From: Martyn Luck [mailto:martyn.luck@p...]
> Sent: Wednesday, May 22, 2002 12:29 PM
> To: Access
> Subject: [access] adding hours to a date time field
>
>
> I have a field in the database which is a date/time field, in
> a query i
> need to show a new column called RealSLA, this field is a
> calculation of a
> field call Opendate plus a number of hours, in the design view of the
> query the new column is built like this RealSLA:[Opendate]+2.
> The two is
> the number of hours that needs to be added onto the opendate datetime
> value, however this calculation above adds 2 days onto the
> calculation,
> i've tried using things like 0.5 but this also give a strange
> decimal in
> the calculation.
>
> heres and example
>
> OpenDate RealSLA
> 22/05/2002 11:15:23 22/05/2002 13:15:23
>
> using RealSLA:[OpenDate]+2
>
> 22/05/2002 11:15:23 24/05/2002 11:15:23
>
> using RealSLA:[Opendate]+0.5
>
> 22/05/2002 11:15:23 3971.596452
>
> I've also tried RealSLA:[Opendate]+ 00/00/0000 02:00:00
>
> HELP !
Message #4 by joe.dunn@c... on Wed, 22 May 2002 11:39:39 +0000
|
|
Use the DATEADD function - this can add or subtract values that can be
days, hours, years, etc.
You need:
RealSLA:DateAdd("d",2, [Opendate])
There are also useful functions like:
DateDiff
DatePart
DateSerial
all of which may be found in Access Online Help
*************************************************************************
This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.
The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965 - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL Telephone 0161-832-8686 Internet http://www.cis.co.uk E-mail
cis@c...
CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.
CIS is a member of the General Insurance Standards Council
CIS & the CIS logo (R) Co-operative Insurance Society Limited
********************************************************************************
Message #5 by joe.dunn@c... on Wed, 22 May 2002 11:41:04 +0000
|
|
whoops!
I suggested
RealSLA:DateAdd("d",2, [Opendate])
but you need
RealSLA:DateAdd("h",2, [Opendate])
*************************************************************************
This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.
The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965 - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL Telephone 0161-832-8686 Internet http://www.cis.co.uk E-mail
cis@c...
CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.
CIS is a member of the General Insurance Standards Council
CIS & the CIS logo (R) Co-operative Insurance Society Limited
********************************************************************************
Message #6 by "Martyn Luck" <martyn.luck@p...> on Wed, 22 May 2002 12:03:41
|
|
Thanx that worked great just one more quick question, although I said that
the realSLA is 2 hours this depends on a field call P so if P=1 then its
30 mins, if P=2 then its 2 hours, if P=3 than its 8 hours and if P=4 then
its 24 hours, how can I build this into the query so RealSLA is calculated
differently depending on the value of P.
Help again !
> Martyn
You need to use the DateAdd function:
DateAdd("h",2,[Opendate])
Brian
> from: Martyn Luck <martyn.luck@p...>
> date: Wed, 22 May 2002 12:29:11
> to: access@p...
> subject: Re: [access] adding hours to a date time field
>
> I have a field in the database which is a date/time field, in a query i
> need to show a new column called RealSLA, this field is a calculation of
a
> field call Opendate plus a number of hours, in the design view of the
> query the new column is built like this RealSLA:[Opendate] 2. The two
is
> the number of hours that needs to be added onto the opendate datetime
> value, however this calculation above adds 2 days onto the calculation,
> i've tried using things like 0.5 but this also give a strange decimal in
> the calculation.
>
> heres and example
>
> OpenDate RealSLA
> 22/05/2002 11:15:23 22/05/2002 13:15:23
>
> using RealSLA:[OpenDate] 2
>
> 22/05/2002 11:15:23 24/05/2002 11:15:23
>
> using RealSLA:[Opendate] 0.5
>
> 22/05/2002 11:15:23 3971.596452
>
> I've also tried RealSLA:[Opendate] 00/00/0000 02:00:00
>
> HELP !
Message #7 by joe.dunn@c... on Wed, 22 May 2002 12:23:11 +0000
|
|
although I said that the realSLA is 2 hours this depends on a field call P
so if P=1 then its 30 mins, if P=2 then its 2 hours, if P=3 than its 8
hours and if P=4 then
its 24 hours, how can I build this into the query so RealSLA is calculated
differently depending on the value of P.
RealSLA: IIF(P = 1, DateAdd("n", 30, [OpenDate]), IIF(P = 2, DateAdd("h",
2, [OpenDate]), IIF(P = 3, DateAdd("h", 8, [OpenDate]), IIF(P = 4, DateAdd
("h", 24, [OpenDate]), [OpenDate]))))
n = code for minutes, not m which stands for months
I guess (but I have not tried) that IIF(P = 1, DateAdd("h", 0.5,
[OpenDate]), ... may also work.
However you MUST make provision for having P = anything other than 1 to 4
I would actually recommend that you do this as a function rather than a
nested IIF statement - it would be clearer.
Public Function DERIVE_SLA(pCode as Integer, pDate as Date) as Date
' accepts: pCODE value of field P
' pDATE date to be added to
' returns: a date to which something has been added
Select Case pCode
Case 1
DERIVE_SLA = DateAdd("h", 0.5, pDate) ' or DateAdd("n", 30,
pDate)
Case 2
DERIVE_SLA = DateAdd("h", 2, pDate)
Case 3
DERIVE_SLA = DateAdd("h", 8, pDate)
Case 4
DERIVE_SLA = DateAdd("h", 24, pDate)
Case Else
DERIVE_SLA = pDate ' in case pCODE is not 1 to 4
End Select
End Function
Joe Dunn
*************************************************************************
This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.
The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965 - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL Telephone 0161-832-8686 Internet http://www.cis.co.uk E-mail
cis@c...
CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.
CIS is a member of the General Insurance Standards Council
CIS & the CIS logo (R) Co-operative Insurance Society Limited
********************************************************************************
Message #8 by braxis@b... on Wed, 22 May 2002 12:32:57 +0100 (BST)
|
|
Martyn
I'd create a VBA function in a code module:
Public Function CalcRealSLA(dteOpenDate as date,intPriority as integer)as date
Select Case intPriority
Case 1
CalcRealSLA=DateAdd("mi",30,dteOpenDate )
Case 2
CalcRealSLA=DateAdd("h",2,dteOpenDate )
Case 3
CalcRealSLA=DateAdd("h",8,dteOpenDate )
Case 4
CalcRealSLA=DateAdd("h",24,dteOpenDate )
Case Else
CalcRealSLA=dteOpenDate
End Select
End Function
You can then call this function from your query like this:
RealSLA:CalcRealSLA ( [TableName]![OpenDate] , [TableName]![P] )
> from: Martyn Luck <martyn.luck@p...>
> date: Wed, 22 May 2002 13:03:41
> to: access@p...
> subject: Re: [access] Re: adding hours to a date time field
>
> Thanx that worked great just one more quick question, although I said that
> the realSLA is 2 hours this depends on a field call P so if P=1 then its
> 30 mins, if P=2 then its 2 hours, if P=3 than its 8 hours and if P=4 then
> its 24 hours, how can I build this into the query so RealSLA is calculated
> differently depending on the value of P.
>
> Help again !
>
> > Martyn
>
> You need to use the DateAdd function:
>
> DateAdd("h",2,[Opendate])
>
> Brian
>
> > from: Martyn Luck <martyn.luck@p...>
> > date: Wed, 22 May 2002 12:29:11
> > to: access@p...
> > subject: Re: [access] adding hours to a date time field
> >
> > I have a field in the database which is a date/time field, in a query i
> > need to show a new column called RealSLA, this field is a calculation of
> a
> > field call Opendate plus a number of hours, in the design view of the
> > query the new column is built like this RealSLA:[Opendate] 2. The two
> is
> > the number of hours that needs to be added onto the opendate datetime
> > value, however this calculation above adds 2 days onto the calculation,
> > i've tried using things like 0.5 but this also give a strange decimal in
> > the calculation.
> >
> > heres and example
> >
> > OpenDate RealSLA
> > 22/05/2002 11:15:23 22/05/2002 13:15:23
> >
> > using RealSLA:[OpenDate] 2
> >
> > 22/05/2002 11:15:23 24/05/2002 11:15:23
> >
> > using RealSLA:[Opendate] 0.5
> >
> > 22/05/2002 11:15:23 3971.596452
> >
> > I've also tried RealSLA:[Opendate] 00/00/0000 02:00:00
> >
> > HELP !
>
Message #9 by braxis@b... on Wed, 22 May 2002 12:45:06 +0100 (BST)
|
|
Apologies - as Joe mentioned, the Access code for minutes is "n" not "mi"!
> from: braxis@b...
> date: Wed, 22 May 2002 12:32:57
> to: access@p...
> subject: Re: [access] Re: adding hours to a date time field
>
> Martyn
>
> I'd create a VBA function in a code module:
>
> Public Function CalcRealSLA(dteOpenDate as date,intPriority as integer)as date
>
> Select Case intPriority
>
> Case 1
> CalcRealSLA=DateAdd("mi",30,dteOpenDate )
> Case 2
> CalcRealSLA=DateAdd("h",2,dteOpenDate )
> Case 3
> CalcRealSLA=DateAdd("h",8,dteOpenDate )
> Case 4
> CalcRealSLA=DateAdd("h",24,dteOpenDate )
> Case Else
> CalcRealSLA=dteOpenDate
> End Select
>
> End Function
>
> You can then call this function from your query like this:
>
> RealSLA:CalcRealSLA ( [TableName]![OpenDate] , [TableName]![P] )
>
>
> > from: Martyn Luck <martyn.luck@p...>
> > date: Wed, 22 May 2002 13:03:41
> > to: access@p...
> > subject: Re: [access] Re: adding hours to a date time field
> >
> > Thanx that worked great just one more quick question, although I said that
> > the realSLA is 2 hours this depends on a field call P so if P=1 then its
> > 30 mins, if P=2 then its 2 hours, if P=3 than its 8 hours and if P=4 then
> > its 24 hours, how can I build this into the query so RealSLA is calculated
> > differently depending on the value of P.
> >
> > Help again !
> >
> > > Martyn
> >
> > You need to use the DateAdd function:
> >
> > DateAdd("h",2,[Opendate])
> >
> > Brian
> >
> > > from: Martyn Luck <martyn.luck@p...>
> > > date: Wed, 22 May 2002 12:29:11
> > > to: access@p...
> > > subject: Re: [access] adding hours to a date time field
> > >
> > > I have a field in the database which is a date/time field, in a query i
> > > need to show a new column called RealSLA, this field is a calculation of
> > a
> > > field call Opendate plus a number of hours, in the design view of the
> > > query the new column is built like this RealSLA:[Opendate] 2. The two
> > is
> > > the number of hours that needs to be added onto the opendate datetime
> > > value, however this calculation above adds 2 days onto the calculation,
> > > i've tried using things like 0.5 but this also give a strange decimal in
> > > the calculation.
> > >
> > > heres and example
> > >
> > > OpenDate RealSLA
> > > 22/05/2002 11:15:23 22/05/2002 13:15:23
> > >
> > > using RealSLA:[OpenDate] 2
> > >
> > > 22/05/2002 11:15:23 24/05/2002 11:15:23
> > >
> > > using RealSLA:[Opendate] 0.5
> > >
> > > 22/05/2002 11:15:23 3971.596452
> > >
> > > I've also tried RealSLA:[Opendate] 00/00/0000 02:00:00
> > >
> > > HELP !
> >
>
>
Message #10 by "Martyn Luck" <martyn.luck@p...> on Wed, 22 May 2002 13:26:22 +0100
|
|
Hope you don't mind me contacting you off line but I prefer your example as
ultimately the query will be driven from an asp page, however when I use you
example:
RealSLA:
IIf([Priority]=1,DateAdd("n",30,[OpenDate]),IIf([Priority]=2,DateAdd("h",2,[
OpenDate]),IIf([Priority]=3,DateAdd("h",8,[OpenDate]),IIf([Priority]=4,[Date
Add]("h",24,[OpenDate]),[OpenDate]))))
I get the error Undefined function "[DateAdd]" in expression
Any ideas
Regards
Martyn Luck
The PCMS Group plc,
PCMS House,
Torwood Close,
Westwood Business Park,
Coventry,
CV4 8HX
United Kingdom
Tel: +44 (0)24 7669 4455
Fax: +44 (0)24 7642 1390
Mobile: 07764 305663
Email Address - martyn.luck@p...
Corporate Website - www.pcmsgroup.com
The PCMS Group promoting culture. ~Integrity~Respect~Commitment~Continuous
Improvement.
The information contained in the e-mail is intended only for the person or
entity to which it is addressed and may contain confidential and/or
privileged material. If you are not the intended recipient of this e-mail,
the use of this information or any disclosure, copying or distribution is
prohibited and may be unlawful.
If you have received this in error, please contact the sender and delete the
material from any computer.
The views expressed in this e-mail may not necessarily be the views of The
PCMS Group plc and should not be taken as authority to carryout any
instruction contained.
-----Original Message-----
From: joe.dunn@c... [mailto:joe.dunn@c...]
Sent: 22 May 2002 13:23
To: Access
Subject: [access] Re: adding hours to a date time field
although I said that the realSLA is 2 hours this depends on a field call P
so if P=1 then its 30 mins, if P=2 then its 2 hours, if P=3 than its 8
hours and if P=4 then
its 24 hours, how can I build this into the query so RealSLA is calculated
differently depending on the value of P.
RealSLA: IIF(P = 1, DateAdd("n", 30, [OpenDate]), IIF(P = 2, DateAdd("h",
2, [OpenDate]), IIF(P = 3, DateAdd("h", 8, [OpenDate]), IIF(P = 4, DateAdd
("h", 24, [OpenDate]), [OpenDate]))))
n = code for minutes, not m which stands for months
I guess (but I have not tried) that IIF(P = 1, DateAdd("h", 0.5,
[OpenDate]), ... may also work.
However you MUST make provision for having P = anything other than 1 to 4
I would actually recommend that you do this as a function rather than a
nested IIF statement - it would be clearer.
Public Function DERIVE_SLA(pCode as Integer, pDate as Date) as Date
' accepts: pCODE value of field P
' pDATE date to be added to
' returns: a date to which something has been added
Select Case pCode
Case 1
DERIVE_SLA = DateAdd("h", 0.5, pDate) ' or DateAdd("n", 30,
pDate)
Case 2
DERIVE_SLA = DateAdd("h", 2, pDate)
Case 3
DERIVE_SLA = DateAdd("h", 8, pDate)
Case 4
DERIVE_SLA = DateAdd("h", 24, pDate)
Case Else
DERIVE_SLA = pDate ' in case pCODE is not 1 to 4
End Select
End Function
Joe Dunn
*************************************************************************
This e-mail may contain confidential information or be privileged. It is
intended to be read and used only by the named recipient(s). If you are not
the intended recipient(s) please notify us immediately so that we can make
arrangements for its return: you should not disclose the contents of this
e-mail to any other person, or take any copies. Unless stated otherwise by
an authorised individual, nothing contained in this e-mail is intended to
create binding legal obligations between us and opinions expressed are those
of the individual author.
The CIS marketing group, which is regulated for Investment Business by the
Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R -
for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965 -
for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number
3390839 - for ISAs and investment products bearing the CIS name
Registered offices: Miller Street, Manchester M60 0AL Telephone
0161-832-8686 Internet http://www.cis.co.uk E-mail cis@c...
CIS Deposit and Instant Access Savings Accounts are held with The
Co-operative Bank p.l.c., registered in England and Wales number 990937,
P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS
Policyholder Services Limited as agent of the Bank.
CIS is a member of the General Insurance Standards Council
CIS & the CIS logo (R) Co-operative Insurance Society Limited
****************************************************************************
****
Message #11 by joe.dunn@c... on Wed, 22 May 2002 14:12:30 +0000
|
|
Hope you don't mind me contacting you off line but I prefer your example as
ultimately the query will be driven from an asp page, however when I use
you
example:
RealSLA:
IIf([Priority]=1,DateAdd("n",30,[OpenDate]),IIf([Priority]=2,DateAdd("h",2,
[
OpenDate]),IIf([Priority]=3,DateAdd("h",8,[OpenDate]),IIf
([Priority]=4,[Date
Add]("h",24,[OpenDate]),[OpenDate]))))
I get the error Undefined function "[DateAdd]" in expression
Sorry, for some daft reason I have put in some square brackets (in red
below) - just remove them!
RealSLA:
IIf([Priority]=1,DateAdd("n",30,[OpenDate]),IIf([Priority]=2,DateAdd("h",2,
[
OpenDate]),IIf([Priority]=3,DateAdd("h",8,[OpenDate]),IIf([Priority]=4,[
Date
Add]("h",24,[OpenDate]),[OpenDate]))))
Joe Dunn
*************************************************************************
This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.
The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965 - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL Telephone 0161-832-8686 Internet http://www.cis.co.uk E-mail
cis@c...
CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.
CIS is a member of the General Insurance Standards Council
CIS & the CIS logo (R) Co-operative Insurance Society Limited
********************************************************************************
Message #12 by "Leo Scott" <leoscott@c...> on Wed, 22 May 2002 01:18:36 -0700
|
|
Create another table with a P column to link and the time value you want
added in another column. Then just add the two together in your query.
This way if you want to change the values added or create new values to add
you just update a record in a table.
|-----Original Message-----
|From: Martyn Luck [mailto:martyn.luck@p...]
|Sent: Wednesday, May 22, 2002 5:26 AM
|To: Access
|Subject: [access] Re: adding hours to a date time field
|Importance: High
|
|
|Hope you don't mind me contacting you off line but I prefer your example as
|ultimately the query will be driven from an asp page, however when
|I use you
|example:
|
|RealSLA:
|IIf([Priority]=1,DateAdd("n",30,[OpenDate]),IIf([Priority]=2,DateAd
|d("h",2,[
|OpenDate]),IIf([Priority]=3,DateAdd("h",8,[OpenDate]),IIf([Priority
|]=4,[Date
|Add]("h",24,[OpenDate]),[OpenDate]))))
|
|I get the error Undefined function "[DateAdd]" in expression
|
|Any ideas
|
|Regards
|
|Martyn Luck
|
|
|The PCMS Group plc,
|PCMS House,
|Torwood Close,
|Westwood Business Park,
|Coventry,
|CV4 8HX
|United Kingdom
|
|Tel: +44 (0)24 7669 4455
|Fax: +44 (0)24 7642 1390
|Mobile: 07764 305663
|Email Address - martyn.luck@p...
|Corporate Website - www.pcmsgroup.com
|
|The PCMS Group promoting culture. ~Integrity~Respect~Commitment~Continuous
|Improvement.
|
|The information contained in the e-mail is intended only for the person or
|entity to which it is addressed and may contain confidential and/or
|privileged material. If you are not the intended recipient of this e-mail,
|the use of this information or any disclosure, copying or distribution is
|prohibited and may be unlawful.
|
|If you have received this in error, please contact the sender and
|delete the
|material from any computer.
|
|The views expressed in this e-mail may not necessarily be the views of The
|PCMS Group plc and should not be taken as authority to carryout any
|instruction contained.
|
|
|-----Original Message-----
|From: joe.dunn@c... [mailto:joe.dunn@c...]
|Sent: 22 May 2002 13:23
|To: Access
|Subject: [access] Re: adding hours to a date time field
|
|
|although I said that the realSLA is 2 hours this depends on a field call P
|so if P=1 then its 30 mins, if P=2 then its 2 hours, if P=3 than its 8
|hours and if P=4 then
|its 24 hours, how can I build this into the query so RealSLA is calculated
|differently depending on the value of P.
|
|RealSLA: IIF(P = 1, DateAdd("n", 30, [OpenDate]), IIF(P = 2, DateAdd("h",
|2, [OpenDate]), IIF(P = 3, DateAdd("h", 8, [OpenDate]), IIF(P = 4, DateAdd
|("h", 24, [OpenDate]), [OpenDate]))))
|
|
|
|n = code for minutes, not m which stands for months
|
|I guess (but I have not tried) that IIF(P = 1, DateAdd("h", 0.5,
|[OpenDate]), ... may also work.
|
|However you MUST make provision for having P = anything other than 1 to 4
|
|
|
|I would actually recommend that you do this as a function rather than a
|nested IIF statement - it would be clearer.
|
|
|
|Public Function DERIVE_SLA(pCode as Integer, pDate as Date) as Date
|
|' accepts: pCODE value of field P
|
|' pDATE date to be added to
|
|' returns: a date to which something has been added
|
|Select Case pCode
| Case 1
| DERIVE_SLA = DateAdd("h", 0.5, pDate) ' or DateAdd("n", 30,
|pDate)
| Case 2
| DERIVE_SLA = DateAdd("h", 2, pDate)
| Case 3
| DERIVE_SLA = DateAdd("h", 8, pDate)
| Case 4
| DERIVE_SLA = DateAdd("h", 24, pDate)
| Case Else
| DERIVE_SLA = pDate ' in case pCODE is not 1 to 4
|End Select
|
|End Function
|
|
|
|Joe Dunn
|
|
|*************************************************************************
|
|This e-mail may contain confidential information or be privileged. It is
|intended to be read and used only by the named recipient(s). If you are not
|the intended recipient(s) please notify us immediately so that we can make
|arrangements for its return: you should not disclose the contents of this
|e-mail to any other person, or take any copies. Unless stated otherwise by
|an authorised individual, nothing contained in this e-mail is intended to
|create binding legal obligations between us and opinions expressed
|are those
|of the individual author.
|
|The CIS marketing group, which is regulated for Investment Business by the
|Financial Services Authority, includes:
|Co-operative Insurance Society Limited Registered in England number 3615R -
|for life assurance and pensions
|CIS Unit Managers Limited Registered in England and Wales number 2369965 -
|for unit trusts and PEPs
|CIS Policyholder Services Limited Registered in England and Wales number
|3390839 - for ISAs and investment products bearing the CIS name
|Registered offices: Miller Street, Manchester M60 0AL Telephone
|0161-832-8686 Internet http://www.cis.co.uk E-mail cis@c...
|
|CIS Deposit and Instant Access Savings Accounts are held with The
|Co-operative Bank p.l.c., registered in England and Wales number 990937,
|P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS
|Policyholder Services Limited as agent of the Bank.
|
|CIS is a member of the General Insurance Standards Council
|
|CIS & the CIS logo (R) Co-operative Insurance Society Limited
|
|*******************************************************************
|*********
|****
|
|
|
Message #13 by "Wesley Kendrick" <wez.k@n...> on Wed, 22 May 2002 23:01:41 +0100
|
|
Hi Martyn, I think you need to add the number of hours minutes and seconds,
ie 2 days would be 48:00:00
0.5 hours would be 00:30:00
regards, wesley Kendrick
----- Original Message -----
From: "Martyn Luck" <martyn.luck@p...>
To: "Access" <access@p...>
Sent: Wednesday, May 22, 2002 11:29 AM
Subject: [access] adding hours to a date time field
> I have a field in the database which is a date/time field, in a query i
> need to show a new column called RealSLA, this field is a calculation of a
> field call Opendate plus a number of hours, in the design view of the
> query the new column is built like this RealSLA:[Opendate]+2. The two is
> the number of hours that needs to be added onto the opendate datetime
> value, however this calculation above adds 2 days onto the calculation,
> i've tried using things like 0.5 but this also give a strange decimal in
> the calculation.
>
> heres and example
>
> OpenDate RealSLA
> 22/05/2002 11:15:23 22/05/2002 13:15:23
>
> using RealSLA:[OpenDate]+2
>
> 22/05/2002 11:15:23 24/05/2002 11:15:23
>
> using RealSLA:[Opendate]+0.5
>
> 22/05/2002 11:15:23 3971.596452
>
> I've also tried RealSLA:[Opendate]+ 00/00/0000 02:00:00
>
> HELP !
>
|
|
|
View Cart
