Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Macros _ Calculate A Date From Dot Number

Posted by: whitechair Jul 12 2019, 02:48 PM

The DOT has a process to determine when a company needs to file a certain form. They use the DOT number to determine the following:
If the DOT is an even number, you need to file this form on even numbered years
If the it's Odd, you file on Odd numbered years

The second to last digit determines what month to file.
1= Jan
2=Feb
3=Mar
4=April
5=May
6=June
7=July
8=August
9=September
0=October

So a DOT number of 123456 would need to file on May 1st, 2020

112233 would file on March 1st, 2021 (because Mar 1st 2019 is in the past)

115599 would need to file on Sept 1st, 2019

My questions is, how would you write a formula in a query or VBA to calculate this date? What I've done so far is so complicated that I can't even keep track of it. And then I keep having to rewrite it so that when the current date transfers to 2020, the formulas will continue to work.

Thanks in advance for any info you can send me.

Posted by: theDBguy Jul 12 2019, 03:26 PM

Hi Jeff. Here's how I might pull out the month from the number:

CODE
?int((([number]/100)-int([number]/100))*10)

Posted by: whitechair Jul 12 2019, 04:05 PM

The month seems to be easier to me. I would just do something like:

vRight = RIGHT([DOT],2)
vMonth= LEFT(vRight,1)
if vMonth = 0 then vMonth = 10

Now that I have the right month and day, I was going to get it into a date format so I can calculate days until due.

It's getting the year that's the real challenge. Especially since you have to consider if the file date is before the current date. Then it would be two years from now. If it's after the current date, then it would be this year.

My mind is exploding just trying to explain it!! Posted by: RJD Jul 12 2019, 06:29 PM

NOTE: See the next post for a revised approach. Although this approach works for an odd year (such as 2019), it does not, it turns out, for an even year. So I made a slight alteration in the next post.

Hi: Assuming that the DOT number is always 6 characters, a function to get the next file date should be ...

CODE
Public Function GetFileDate(D As String) As Date

Dim FM As String, FY As String
FM = IIf(Mid(D, 5, 1) = "0", "10", Mid(D, 5, 1))
FY = IIf(Right(D, 6) Mod 2 > 0, 0, 1)

GetFileDate = DateSerial(Year(Date) + FY, FM, 1) <<< this is where I altered the procedure below - and removed the attachment here.
If GetFileDate < Date Then GetFileDate = DateAdd("yyyy", 2, GetFileDate)

End Function

This is used in a query like this ...

CODE
SELECT DOT, GetFileDate([DOT]) AS FileDate
FROM tblMyRecords;

HTH
Joe

Posted by: RJD Jul 12 2019, 09:04 PM

Hi: Sorry about that misstep. See if this is better, and works for 2020 as well (odd and even years) ...

CODE
Public Function GetFileDate(D As String) As Date

Dim FM As String, FY As String
FM = IIf(Mid(D, 5, 1) = "0", "10", Mid(D, 5, 1))
FY = IIf(Right(D, 6) Mod 2 > 0, 0, 1)

GetFileDate = DateSerial(Year(Date) + FY + IIf(Year(Date) Mod 2 > 0, 0, 1), FM, 1)
If GetFileDate < Date Then GetFileDate = DateAdd("yyyy", 2, GetFileDate)

End Function

Test this thoroughly and see if it still needs some work...

HTH
Joe

Posted by: gemmathehusky Jul 15 2019, 07:07 AM

I assume the dot number HAS to be 6 characters long?

What do you do about Nov/Dec. Can you represent them?

Anyway, the point I was getting it, is that it isn't really a "number" it's a 6-character string - so I would store it as a string.
Then you could represent Nov/DEC as A/B.

Posted by: whitechair Jul 15 2019, 09:10 AM

This looks great and I think I should be able to interpret it for what I need.

Notes:
1) The DOT isn't necessarily 3 digits long. It could be as little 4 and as long as 7. (I should have made that clear in my post.)
2) Nov and Dec do not need to be represented. All filings should be done before Oct.

Posted by: RJD Jul 15 2019, 10:22 AM

Hi Jeff: Okay, I adjusted the code to deal with the last two digits, regardless of the length of the DOT code...

CODE
Public Function GetFileDate(D As String) As Date

Dim FM As String, FY As String
FM = IIf(Left(Right(D, 2), 1) = "0", "10", Left(Right(D, 2), 1))
FY = IIf(Right(D, 1) Mod 2 > 0, 0, 1)

GetFileDate = DateSerial(Year(Date) + FY + IIf(Year(Date) Mod 2 > 0, 0, 1), FM, 1)
If GetFileDate < Date Then GetFileDate = DateAdd("yyyy", 2, GetFileDate)

End Function

Take a look at the revised demo attached and see if this does what you need.

I also converted the file to A2003, since you are using A2007 and this was created in A2010 - just in case you cannot read the A2010 file.

HTH
Joe