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

[zivon]

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 :))

[MikeTheBike]

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

[missinglinq]

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

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)>

[zivon]

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...

[zivon]

another question, does it work the same with SQL ?

[zivon]

: \
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 : <

[istya]

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

to_date(sysdate,'dd/mm/yyyy')
 

[FishVal]

: \
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.

[zivon]

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 ?

[FishVal]

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.