Help with extracting "date"--need today!!!

[tweetyksc]

Here's what I've got:
--a code for the "lot" of a product
Ex: 0501-0061.xxxxxxxx (date is 1st 4 char)
--the products mature in 12 months. So in a report for the example above I need to show 5/02...
What is the best way to do this?
I've done a Left function to extract as 0501...but how do I convert to date?
I'm thinking I need to extract (05) and (01) separately and then try to convert...
Any help would be appreciated!!

 

[David R]

You just need to show it on the report as 05/02? Use something similar to this:

NextDate: Format(DateAdd("yyyy",1,CDate(Left([LotField],2) & "/01/" & Mid([LotField],3,2))),"mm/yy")

You can either put this in a query or just use the calculation directly in a field on your report.

HTH,
David R


[This message has been edited by David R (edited 02-28-2002).]

 

[tweetyksc]

Thanks...I figured it out a slightly different way.
Here's what I did (probably lengthy, but works):
--LotDate--(to convert 4#s to text with"/":
Left([WhseJobInfo]![DDIJobID],2) & "/" & Mid([WhseJobInfo]![DDIJobID],3,2)
--InvDate--to display lot date plus one yr)
DateAdd("d",365,[LotDate])

Thanks for your input.


[This message has been edited by tweetyksc (edited 02-28-2002).]

 

[David R]

I did something similar initially. Be sure you test your LotDate's outside of 2002. When I did, I discovered that having only 05/02 was actually interpreting it as #05/02/2002#. You have to explicitly state all three parts of the date to avoid data errors.
Also consider using DateAdd("yyyy",1... to avoid a one-in-a-hundred-thousand leap year miscalculation. Otherwise your method looks perfectly sound.

David R

 

[tweetyksc]

Not exactly sound

It's not calculating correctly...most are showing the month correct for the next year but the year is mostly 03. This is probably what you meant by the "01" which I misinterpreted to be the YEAR...
I'm going to go with what you have and see how it turns out.
Thanks!
It's little things like this that seem so easy initially that have you tearing your hair out!

 

[tweetyksc]

It worked! Thank you, thank you!

:0

 

[David R]

Well, like I said, "other than that"...which basically turns it into my suggestion.

Glad you got it working,
David R