By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 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
Share this Question
Share on Google+
9 Replies


Expert 100+
P: 634
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

missinglinq
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
  1. Private Sub CompareDate_Click()
  2. If Me.DateA > Me.DateB Then
  3.   MsgBox "DateA Comes after DateB"
  4. ElseIf Me.DateB > Me.DateA Then
  5.   MsgBox "DateB Comes after DateA"
  6. Else: MsgBox "DateA and DateB are the same!"
  7. End If
  8. 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
  1. to_date(sysdate,'dd/mm/yyyy')
  2.  
Sep 11 '07 #7

FishVal
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

FishVal
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

Post your reply

Sign in to post your reply or Sign up for a free account.