Datediff where date format is yyyymmdd

[DontellTrevell via AccessMonster.com]

HELP!!....I need to calculate the numer of days elapsed between two field.
But, the date format is YYYYMMDD. How can i accomplsh this?

--
Dontell Trevell

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200701/1

[DontellTrevell via AccessMonster.com]

DontellTrevell wrote:

>HELP!!....I need to calculate the numer of days elapsed between two date fields.
But, the date format is YYYYMMDD. How can i accomplsh this?

--
Dontell Trevell

Message posted via http://www.accessmonster.com

[fredg]

On Wed, 31 Jan 2007 23:09:53 GMT, DontellTrevell via AccessMonster.com
wrote:

HELP!!....I need to calculate the numer of days elapsed between two field.
But, the date format is YYYYMMDD. How can i accomplsh this?

What are the 2 fields datatypes?
If both are Date datatypes, then simply subtract one from the other.
The Format of the field is irrelevant.
[DaysDifference] = [Date2] - [Date1]

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

[DontellTrevell via AccessMonster.com]

fredg wrote:

>HELP!!....I need to calculate the numer of days elapsed between two field.
But, the date format is YYYYMMDD. How can i accomplsh this?

What are the 2 fields datatypes?
If both are Date datatypes, then simply subtract one from the other.
The Format of the field is irrelevant.
[DaysDifference] = [Date2] - [Date1]

Thank You very much!!!! I did'nt know it was that simple.....DontellTrevell

--
Dontell Trevell

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200702/1

[DontellTrevell via AccessMonster.com]

Help Again!! It works for most but here is an instance when it does'nt work:

20060929 - 20061004 = 75 instead of 5 days. Why is it calculating 75 days
intead of 5 days?

fredg wrote:

>HELP!!....I need to calculate the numer of days elapsed between two field.
But, the date format is YYYYMMDD. How can i accomplsh this?

What are the 2 fields datatypes?
If both are Date datatypes, then simply subtract one from the other.
The Format of the field is irrelevant.
[DaysDifference] = [Date2] - [Date1]

--
Dontell Trevell

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200702/1

[Rick Brandt]

DontellTrevell via AccessMonster.com wrote:

Help Again!! It works for most but here is an instance when it
does'nt work:

20060929 - 20061004 = 75 instead of 5 days. Why is it calculating 75
days intead of 5 days?

Because you don't have dates, you have numbers. Quoting Fred's original
response...

"If both are Date datatypes, then simply subtract one from the other."

If your data type are not dates then simply subtracting them will not work.
You will need to convert them to dates first...

DateValue(Format([YourField],"0000-00-00"))

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[DontellTrevell via AccessMonster.com]

Where does the formula below go? DateValue(Format([YourField],"0000-00-00"))
Rick Brandt wrote:

>Help Again!! It works for most but here is an instance when it
does'nt work:

20060929 - 20061004 = 75 instead of 5 days. Why is it calculating 75
days intead of 5 days?

Because you don't have dates, you have numbers. Quoting Fred's original
response...

"If both are Date datatypes, then simply subtract one from the other."

If your data type are not dates then simply subtracting them will not work.
You will need to convert them to dates first...

DateValue(Format([YourField],"0000-00-00"))

--
Dontell Trevell

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200702/1

[fredg]

On Thu, 01 Feb 2007 17:18:16 GMT, DontellTrevell via AccessMonster.com
wrote:

Help Again!! It works for most but here is an instance when it does'nt work:

20060929 - 20061004 = 75 instead of 5 days. Why is it calculating 75 days
intead of 5 days?

fredg wrote:

>>HELP!!....I need to calculate the numer of days elapsed between two field.
But, the date format is YYYYMMDD. How can i accomplsh this?

What are the 2 fields datatypes?
If both are Date datatypes, then simply subtract one from the other.
The Format of the field is irrelevant.
[DaysDifference] = [Date2] - [Date1]

Your computer is working correctly.
As numbers, 20061004 - 20060929 = 75

Remember this part of my reply?

>>What are the 2 fields datatypes?
If both are Date datatypes, then simply subtract one from the other.

As a Date datatype, 10/04/2006 is stored as 38994 .
As a Date datatype, 9/29/2006 is stored as 38989.

38994 - 38989 = 5

If the values are a Date datatype, the actual format of the value is
not important. The field can display, as a date, 38994 formatted as
10/04/2006 or 04/10/2006 or 20061004 or October 4, 2006 or even simply
October, but it's value is still 38994.

If the actual datatype of the values are not date datatypes, you first
need to convert them to date datatype, then you can perform the math.

From the Access debug window...
? DateSerial(2006,10,04) - DateSerial(2006,9,29)
5

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

[Rick Brandt]

DontellTrevell via AccessMonster.com wrote:

Where does the formula below go?
DateValue(Format([YourField],"0000-00-00"))

Wherever you are doing your subtraction.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[DontellTrevell via AccessMonster.com]

Wow!!...Let me start over......Here's what I'm tring to do: I have 2 date
fields [StartDate] & [EndDate] from an ODBC table that are TEXT fields. How
can I simply convert from text to serial so I can perform the calculation.
Can I use the query criteria? If so, what do I type in the query criteria?

Rick Brandt wrote:

>Where does the formula below go?
DateValue(Format([YourField],"0000-00-00"))

Wherever you are doing your subtraction.

--
Dontell Trevell

Message posted via http://www.accessmonster.com

[Rick Brandt]

DontellTrevell via AccessMonster.com wrote:

Wow!!...Let me start over......Here's what I'm tring to do: I have 2
date fields [StartDate] & [EndDate] from an ODBC table that are TEXT
fields. How can I simply convert from text to serial so I can
perform the calculation. Can I use the query criteria? If so, what
do I type in the query criteria?

So what you have are Text fields that happen to contain only digits that
happen to represent dates. (even sounds silly doesn't it?)

If these are text then you are going to need to go through multiple
conversions or split the string up and use one conversion. The DateValue()
function needs a date or a string that looks like a date. If we insert
dashes into your string that will give us a good string that looks like a
date, but to insert the dashes with the Format() function we first have to
convert the string to a number and then the Format function will give that
back to us as a String again.

DateValue(Format(CLng([StartDate]),"0000-00-00"))

You might get away with omitting the CLng() function above but Access will
still be coercing the string to a number in the background so it is better
practice to just explicitly do it yourself.

You could also split the string up and feed that to the DateValue() function
like this...

DateValue(Left([StartDate],4) & "-" & Mid([StartDate],5,2) & "-" &
Right([StartDate],2))

While that is longer it involves fewer data type conversions. I have no
idea if you would notice a speed difference. So using the first example
your date difference would be found with...

DateValue(Format(CLng([EndDate]),"0000-00-00")) -
DateValue(Format(CLng([StartDate]),"0000-00-00"))

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com