Format question

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

I have 2 fields on a report that appear as follows: they
are considered number fields.

Comment Date Resolution Date
20031031 20031105

What I need to be able to do is calculate the difference
between the two to come up with the number of days a
resolution takes, in this case it would be 5, what I am
getting is 74, any ideas on how to get this to calculate
correctly??
 
Lisa,
If you subtract the 'Number' 20031031 from 20031105 the result is supposed
to be 74.
You may know that those numbers are supposed to be a date representation,
but Access doesn't.
In Access, 11/05/2003 (as a Date datatype) is represented as the number
37930,
while 10/31/2003 is represented as 37925, a difference of 5.

Why are you using a number datatype to represent a date, rather than a date
datatype?
If you stored it as Date you would simply use:
DaysDifference = [ResolutionDate] - [CommentDate]
i.e. DaysDifference = 37930 - 37925
(DaysDifference = 5)

Change the field datatypes to Date/Time.
Set the Format property of the fields to
yyyymmdd
The field will display 20031031 but the date will be stored as 37925.
 
Fred,

I am using a number because, the SQL Database I am
connected to was originally set up as a number and they
are not at a point they can change it. Do you know an easy
way I can make the change after importing so that I can
get what I need??

-----Original Message-----
Lisa,
If you subtract the 'Number' 20031031 from 20031105 the result is supposed
to be 74.
You may know that those numbers are supposed to be a date representation,
but Access doesn't.
In Access, 11/05/2003 (as a Date datatype) is represented as the number
37930,
while 10/31/2003 is represented as 37925, a difference of 5.

Why are you using a number datatype to represent a date, rather than a date
datatype?
If you stored it as Date you would simply use:
DaysDifference = [ResolutionDate] - [CommentDate]
i.e. DaysDifference = 37930 - 37925
(DaysDifference = 5)

Change the field datatypes to Date/Time.
Set the Format property of the fields to
yyyymmdd
The field will display 20031031 but the date will be stored as 37925.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Lisa said:
I have 2 fields on a report that appear as follows: they
are considered number fields.

Comment Date Resolution Date
20031031 20031105

What I need to be able to do is calculate the difference
between the two to come up with the number of days a
resolution takes, in this case it would be 5, what I am
getting is 74, any ideas on how to get this to calculate
correctly??
Click to expand...


.
Click to expand...
 
Lisa,
IF the field is ALWAYS an 8 digit number (I.E. 20030105 for Jan 5, 2003) in
the format of
yyyymmdd then you can use a query as Report record source, and
use the DateSerial() function to convert the number to a valid date.

In a query:
NewResolutionDate:
DateSerial(Left([ResolutonDate],4),Mid([ResolutionDate],5,2),Right([Resoluti
onDate],2))
NewCommentDate:
DateSerial(Left([CommentDate],4),Mid([CommentDate],5,2),Right([CommentDate],
2))

Then in the report use an Unbound control.
Set it's control source to:
= [NewResolutionDate] - [NewCommentDate]

Come to think about it, you don't even need a query to change the value to a
date.
As control source of an unbound control in your report:
=DateSerial(Left[ResolutionDate],4), etc.)) - DateSerial([CommentDate],4),
etc.))

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Lisa said:
Fred,

I am using a number because, the SQL Database I am
connected to was originally set up as a number and they
are not at a point they can change it. Do you know an easy
way I can make the change after importing so that I can
get what I need??

-----Original Message-----
Lisa,
If you subtract the 'Number' 20031031 from 20031105 the result is supposed
to be 74.
You may know that those numbers are supposed to be a date representation,
but Access doesn't.
In Access, 11/05/2003 (as a Date datatype) is represented as the number
37930,
while 10/31/2003 is represented as 37925, a difference of 5.

Why are you using a number datatype to represent a date, rather than a date
datatype?
If you stored it as Date you would simply use:
DaysDifference = [ResolutionDate] - [CommentDate]
i.e. DaysDifference = 37930 - 37925
(DaysDifference = 5)

Change the field datatypes to Date/Time.
Set the Format property of the fields to
yyyymmdd
The field will display 20031031 but the date will be stored as 37925.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Lisa said:
I have 2 fields on a report that appear as follows: they
are considered number fields.

Comment Date Resolution Date
20031031 20031105

What I need to be able to do is calculate the difference
between the two to come up with the number of days a
resolution takes, in this case it would be 5, what I am
getting is 74, any ideas on how to get this to calculate
correctly??
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top