448,971 Members | 1,631 Online
Need help? Post your question and get tips & solutions from a community of 448,971 IT Pros & Developers. It's quick & easy.

# is there a way to force 2 digits month/day ?

 P: 59 I was wondering if theres a simple way to force 09 motnh insted of only 9... not just adding a zero before since if it will be 10 -> 010 is not good for what I'm trying to do.. oh, maybe you have a better idea for what I'm trying: I'm trying to determine which date is latter... what I'm doing is adding the year month day as a big number like: 2007/9/10 -> 20070910 but if its not two digits, it wont be true "always" eg: 2007931 > 2007101 if anyone have a better idea then mine i'll be happy to hear :)) Sep 10 '07 #1
9 Replies

 Expert 100+ P: 635 I was wondering if theres a simple way to force 09 motnh insted of only 9... not just adding a zero before since if it will be 10 -> 010 is not good for what I'm trying to do.. oh, maybe you have a better idea for what I'm trying: I'm trying to determine which date is latter... what I'm doing is adding the year month day as a big number like: 2007/9/10 -> 20070910 but if its not two digits, it wont be true "always" eg: 2007931 > 2007101 if anyone have a better idea then mine i'll be happy to hear :)) Hi Idealy we need more information where the data is coming from and where you are using it, but would Format(YourDateVariable,"yyymmdd") do it ?? MTB Sep 10 '07 #2

 Expert 2.5K+ P: 3,532 You don't need to do all this to determine which date is later, just compare the two dates! If DateA > DateB Then This code , for instance, compares two dates, DateA and DateB and sends up a message box accordingly. The code can be easily be modified to do whatever you want Expand|Select|Wrap|Line Numbers Private Sub CompareDate_Click() If Me.DateA > Me.DateB Then   MsgBox "DateA Comes after DateB" ElseIf Me.DateB > Me.DateA Then   MsgBox "DateB Comes after DateA" Else: MsgBox "DateA and DateB are the same!" End If End Sub Linq ;0)> Sep 10 '07 #3

 P: 59 doh... : ) now I really feel stupid lol.. though I think I tried that before. anyway, it works great, thanks ! saved me a lot of code space... Sep 10 '07 #4

 P: 59 another question, does it work the same with SQL ? Sep 10 '07 #5

 P: 59 : \ that doesn't really works... it made a lot of problams to my program and I didn't understood why.. I checked on the immediate ?#11/8/2007# > #10/09/2007# and it return true : < Sep 11 '07 #6

 P: 35 What date format are you using? In SQL I think you would need to format the date to have this work the way you expect. Is it the 11th Aug 07 > 10th Sept 07 or 8th Nov 07 > 9th Oct 07? I'm not sure what the function is for Access SQL but in ANSII standard it's Expand|Select|Wrap|Line Numbers to_date(sysdate,'dd/mm/yyyy')   Sep 11 '07 #7

 Expert 2.5K+ P: 2,653 : \ that doesn't really works... it made a lot of problams to my program and I didn't understood why.. I checked on the immediate ?#11/8/2007# > #10/09/2007# and it return true : < That does really work. ;) SQL accepts dates in m/d/y format only. The confusing point here is that Access silently corrects dates which could not be interpreted as m/d/y. e.g. You pass #1/2/7# meaning 1-Feb-07, Access treats it as 2-Jan-07 You pass #13/1/7# meaning 13-Jan-07, Access corrects it and treats as you've been expecting. To make access user even more happy VBA behaviour on this issue is different. As far as I know it depends on regional settings and correction logic is different from that in SQL. e.g #13/1/7# will be treated as 7-Jan-13. Sep 11 '07 #8

 P: 59 I understand what the problam is... MS access treat the date one time as m/d/y and one time as d/m/y but I dont understand how do I force him to treat it as d/m/y only ? Sep 12 '07 #9

 Expert 2.5K+ P: 2,653 I understand what the problam is... MS access treat the date one time as m/d/y and one time as d/m/y but I dont understand how do I force him to treat it as d/m/y only ? It is almost as difficult as to force it to get numbers in roman numerals. Just use dates in m/d/y. "Format" and "DateSerial" function may help you in this issue. Sep 12 '07 #10