Beetle,

I got a new set of dates to handle with 2 digit years. Based on current
date, the DateDiff string you gave me doesn't seem to handle the dates I have
below. Everything comes up with minus, I think because all of them are past
the current date. How do I handle these kinds of dates?

04/04/10
11/19/09
01/21/10
12/17/09
02/22/10
09/10/09
11/30/09

Where are these date values coming from? Are they formatted values
from an actual Date/Time field, or are they just string values from a Text
field?

If the latter, the DateDiff function won't be able to interpret them correctly
unless the are surrounded with the proper date delimiters, like;

DateDiff("d", Date(), #04/04/10#)

--
_________

Sean Bailey


"Keypad" wrote:

> Beetle,
>
> I got a new set of dates to handle with 2 digit years. Based on current
> date, the DateDiff string you gave me doesn't seem to handle the dates I have
> below. Everything comes up with minus, I think because all of them are past
> the current date. How do I handle these kinds of dates?
>
> 04/04/10
> 11/19/09
> 01/21/10
> 12/17/09
> 02/22/10
> 09/10/09
> 11/30/09
>

It's also possible that you need to switch the position of
the two date values within your DateDiff function if it is
returning negative numbers.

--
_________

Sean Bailey


"Keypad" wrote:

> Beetle,
>
> I got a new set of dates to handle with 2 digit years. Based on current
> date, the DateDiff string you gave me doesn't seem to handle the dates I have
> below. Everything comes up with minus, I think because all of them are past
> the current date. How do I handle these kinds of dates?
>
> 04/04/10
> 11/19/09
> 01/21/10
> 12/17/09
> 02/22/10
> 09/10/09
> 11/30/09
>

Beetle,

I changed the DateDiff string a little and looks like I'm getting the
correct values now. The string I changed it too is:

DaysRemaining: DateDiff("d",Date(),[Void_Date])

These are the values I get, so let me know if they look correct to you:

Date: Days Remaining
01/19/10 228
11/19/09 167
01/21/10 230
05/05/10 334
11/13/09 161

Beetle, thanks again for all your help. Your the best :-)

KP



"Beetle" wrote:

> It's also possible that you need to switch the position of
> the two date values within your DateDiff function if it is
> returning negative numbers.
>
> --
> _________
>
> Sean Bailey
>
>
> "Keypad" wrote:
>
> > Beetle,
> >
> > I got a new set of dates to handle with 2 digit years. Based on current
> > date, the DateDiff string you gave me doesn't seem to handle the dates I have
> > below. Everything comes up with minus, I think because all of them are past
> > the current date. How do I handle these kinds of dates?
> >
> > 04/04/10
> > 11/19/09
> > 01/21/10
> > 12/17/09
> > 02/22/10
> > 09/10/09
> > 11/30/09
> >

Beetle,

Forgot to mention the dates I submitted earlier are string values in a text
box. Can you explain again how DateDiff is supposed to work. Maybe I'm not
interpreting things correctly. I still don't know if what I'm getting is
correct!

KP

"Beetle" wrote:

> It's also possible that you need to switch the position of
> the two date values within your DateDiff function if it is
> returning negative numbers.
>
> --
> _________
>
> Sean Bailey
>
>
> "Keypad" wrote:
>
> > Beetle,
> >
> > I got a new set of dates to handle with 2 digit years. Based on current
> > date, the DateDiff string you gave me doesn't seem to handle the dates I have
> > below. Everything comes up with minus, I think because all of them are past
> > the current date. How do I handle these kinds of dates?
> >
> > 04/04/10
> > 11/19/09
> > 01/21/10
> > 12/17/09
> > 02/22/10
> > 09/10/09
> > 11/30/09
> >

Beetle,

I learned something interesting about the DateDiff function on MSDN in the
Visual Basic 6.0 Resource Center under:

MSDN Library/Development Tools and Languages/Visual Studio 6.0/Visual Basic
6.0/Product Documentation/Reference/Language Reference/Functions/D/DateDiff
Function

Here's part of what it say's:

(If date1 refers to a later point in time than date2, the DateDiff function
returns a negative number.

If date1 or date2 is a date literal, the specified year becomes a permanent
part of that date. However, if date1 or date2 is enclosed in double quotation
marks (" "), and you omit the year, the current year is inserted in your code
each time the date1 or date2 expression is evaluated. This makes it possible
to write code that can be used in different years.)

So, based on the explanation above the first part would explain why I get
the negative numbers. The second part looks like what to do when dealing
with expiration dates of different years. Trouble is, does anyone know how
to code something like what's being described in the second part. I never
figured this would be so complicated when I intially took on this project. I
feel like I'm in deep, deep s*** now. There must be some way to solve this.
I'm sure I'm not the first to ever face this challenge. The answers out
there as they say in X Files, the bigger question is WHERE!

Thanks for listening Beetle :-)

KP

"Beetle" wrote:

> It's also possible that you need to switch the position of
> the two date values within your DateDiff function if it is
> returning negative numbers.
>
> --
> _________
>
> Sean Bailey
>
>
> "Keypad" wrote:
>
> > Beetle,
> >
> > I got a new set of dates to handle with 2 digit years. Based on current
> > date, the DateDiff string you gave me doesn't seem to handle the dates I have
> > below. Everything comes up with minus, I think because all of them are past
> > the current date. How do I handle these kinds of dates?
> >
> > 04/04/10
> > 11/19/09
> > 01/21/10
> > 12/17/09
> > 02/22/10
> > 09/10/09
> > 11/30/09
> >

On Fri, 5 Jun 2009 18:17:01 -0700, Keypad <(E-Mail Removed)>
wrote:

>If date1 or date2 is a date literal, the specified year becomes a permanent
>part of that date. However, if date1 or date2 is enclosed in double quotation
>marks (" "), and you omit the year, the current year is inserted in your code
>each time the date1 or date2 expression is evaluated. This makes it possible
>to write code that can be used in different years.)
>
>So, based on the explanation above the first part would explain why I get
>the negative numbers. The second part looks like what to do when dealing
>with expiration dates of different years. Trouble is, does anyone know how
>to code something like what's being described in the second part.

Ummmmm...

DateDiff("d", "6/4", [datefield])

will get the number of days between 6/4/2009 until the value in datefield, if
it's run during 2009. If it's run in 2010, it will get the days between
6/4/2010 and the value in datefield.

Is that what you mean by "the second part"???
--

John W. Vinson [MVP]

John,

That's a good question. Guess I would have to test it out and see what the
results look like. First off though, were I to use the approach you
submitted as an interpretation of what MSDN say's about DateDiff I foresee
the need to have to use other functions to slice and dice the dates to get
the ##/## or #/# part of the date before I could have anything useful. The
second task would be how to use it in a query.

Honestly John, this stuff is way over my head. I have switched dates around,
used ABS(), everything I can think of and stuff others have suggested. Just
when I think I have it all solved a new problem creeps into the picture. You
folks are fantastic though because you don't give up. That's why I haven't
thrown in the towel myself. I'll keep at it John thanks to you and all the
others who have contributed.

KP

"John W. Vinson" wrote:

> On Fri, 5 Jun 2009 18:17:01 -0700, Keypad <(E-Mail Removed)>
> wrote:
>
> >If date1 or date2 is a date literal, the specified year becomes a permanent
> >part of that date. However, if date1 or date2 is enclosed in double quotation
> >marks (" "), and you omit the year, the current year is inserted in your code
> >each time the date1 or date2 expression is evaluated. This makes it possible
> >to write code that can be used in different years.)
> >
> >So, based on the explanation above the first part would explain why I get
> >the negative numbers. The second part looks like what to do when dealing
> >with expiration dates of different years. Trouble is, does anyone know how
> >to code something like what's being described in the second part.
>
> Ummmmm...
>
> DateDiff("d", "6/4", [datefield])
>
> will get the number of days between 6/4/2009 until the value in datefield, if
> it's run during 2009. If it's run in 2010, it will get the days between
> 6/4/2010 and the value in datefield.
>
> Is that what you mean by "the second part"???
> --
>
> John W. Vinson [MVP]
>

On Fri, 5 Jun 2009 18:56:01 -0700, Keypad <(E-Mail Removed)>
wrote:

>John,
>
>That's a good question. Guess I would have to test it out and see what the
>results look like. First off though, were I to use the approach you
>submitted as an interpretation of what MSDN say's about DateDiff I foresee
>the need to have to use other functions to slice and dice the dates to get
>the ##/## or #/# part of the date before I could have anything useful. The
>second task would be how to use it in a query.

Seriously:

You are making this a LOT HARDER than it actually is.

If you have a due date and you want to see how many days it is in the future,
you don't need to slice and dice ANYTHING.

Maybe take a day off, get some rest, and come back and reread this thread. It
is *simpler than you are making it*.
--

John W. Vinson [MVP]

John,

You are so right. I need to step away from this for a day or so, clear my
head. Lots of turmoil in my life these days which explains why I'm awake
again in the middle of the night. Thanks my friend!

KP

"John W. Vinson" wrote:


> On Fri, 5 Jun 2009 18:56:01 -0700, Keypad <(E-Mail Removed)>
> wrote:
>
> >John,
> >
> >That's a good question. Guess I would have to test it out and see what the
> >results look like. First off though, were I to use the approach you
> >submitted as an interpretation of what MSDN say's about DateDiff I foresee
> >the need to have to use other functions to slice and dice the dates to get
> >the ##/## or #/# part of the date before I could have anything useful. The
> >second task would be how to use it in a query.
>
> Seriously:
>
> You are making this a LOT HARDER than it actually is.
>
> If you have a due date and you want to see how many days it is in the future,
> you don't need to slice and dice ANYTHING.
>
> Maybe take a day off, get some rest, and come back and reread this thread. It
> is *simpler than you are making it*.
> --
>
> John W. Vinson [MVP]
>