UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Calculate A Date From Dot Number, Access 2016    
 
   
whitechair
post Jul 12 2019, 02:48 PM
Post#1



Posts: 491
Joined: 26-June 08



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.

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
theDBguy
post Jul 12 2019, 03:26 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,000
Joined: 19-June 07
From: SunnySandyEggo


Hi Jeff. Here's how I might pull out the month from the number:
CODE
?int((([number]/100)-int([number]/100))*10)

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
whitechair
post Jul 12 2019, 04:05 PM
Post#3



Posts: 491
Joined: 26-June 08



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!! wink.gif

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
RJD
post Jul 12 2019, 06:29 PM
Post#4


UtterAccess VIP
Posts: 9,911
Joined: 25-October 10
From: Gulf South USA


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

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post Jul 12 2019, 09:04 PM
Post#5


UtterAccess VIP
Posts: 9,911
Joined: 25-October 10
From: Gulf South USA


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
Attached File(s)
Attached File  DateFromDOTNumber_Rev1.zip ( 20.88K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
gemmathehusky
post Jul 15 2019, 07:07 AM
Post#6


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


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.




--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
whitechair
post Jul 15 2019, 09:10 AM
Post#7



Posts: 491
Joined: 26-June 08



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.

--------------------
Jeff Moseler
Access 2007
Go to the top of the page
 
RJD
post Jul 15 2019, 10:22 AM
Post#8


UtterAccess VIP
Posts: 9,911
Joined: 25-October 10
From: Gulf South USA


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
Attached File(s)
Attached File  DateFromDOTNumber_Rev2.zip ( 18.15K )Number of downloads: 0
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th August 2019 - 01:42 AM