Format question

[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??

 

[Fredg]

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.

 

[Lisa]

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.

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??

.

 

[Fredg]

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.

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.

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??

.