If Date Greater than - 8

[ZOR]

Whats the best way to compare the pc date to a hard coded reference date to drive an If/Else case. Many thanks

 

[ArtieChoke]

try:

if datediff("d",date,<your date here>) > xxx then ...

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[mmilan]

I would expect

if now > refdate then...

to work as well, so long as refdate is a datetime variable.

mmilan

 

[ClulessChris]

I find diffdate to be a bit unpredictable for this sort of action and tend to convert to val then compare, e.g.

If Val(Date) <> Val(otherdate) Then
'Do Stuff
End

This normaly works for me. Please let me know how it goes. Thanks.

Everybody body is somebodys Nutter.

 

[mmilan]

I think the important issue here is data type - you need to be damned sure that you're looking at a date variable. For example, in CluelessChris's post, were a string to be used instead you would get the wrong value for the test.

mmilan

 

[strongm]

>unpredictable

In what sense?

 

[ClulessChris]

Strongm,
I've just found some strange results using datediff.
mmilan,
I would suggest that the data types are tested first e.g.:

IF isDate(date) = True AND isDate(otherDate) = True Then
    If Val(Date) <> Val(otherdate) Then
'        do Stuff
    end If
end If

Everybody body is somebodys Nutter.

 

[strongm]

I've never found anything odd - as long as you remember how it is that datediff works (it counts the number of inclusive instances of a transition time between the two times you provide it)

For example, it uses midnight as the transition time betwen two days, so:

datediff("d","21 June 23:59","22 June 00:00")

counts one instance of midnight, and thus returns 1. Given that there is only 1 minute between the two date-times, this might seem an odd result, but is perfectly reasonable given the rules datediff works under (after all, it has to have something as the transition point).

 

[ClulessChris]

strongm,
Many thanks for your explanation of datediff, I concede that my issue with the datediff function may well be rooted in my missunderstanding of how it works. But my method of checking the value also works

Any how a star for your explanation.

Everybody body is somebodys Nutter.

 

[Hypetia]

Also note that as long as you are manipulating legitimate Date variables, it is not necessary to call the DateDiff function. You can also perform simple addition and subtraction operation on these dates. Subtracting two dates gives the exact difference in days (which may not be a fraction); similarly adding a number x in a date variable causes the date to advance by x days, where x is a real number (integer or fraction, +ve or -ve).

See these example.
___
[tt]
Private Sub Form_Load()
Dim D1 As Date, D2 As Date

D1 = "1/1/0"
D2 = Date 'today
MsgBox D2 - D1 'number of days elapsed since 1/1/2000


Dim Diff As Single
D1 = "21 June 23:59"
D2 = "22 June 00:00"
Diff = D2 - D1 'difference in days

MsgBox Diff 'difference in days, not too meaningful in this case
MsgBox Diff * 24 'difference in hours, still not meaningful
MsgBox Diff * 24 * 60 'difference in minutes
MsgBox Diff * 24 * 60 * 60 'difference in seconds

D2 = D2 + 1.5 'add 1.5 days to D2
'now d2='22 June 0000' + 1.5 days = 23 June 1200hrs
MsgBox D2

'this will add just 5 seconds to the above date
D2 = D2 + 5 / 60 / 60 / 24 '5 seconds, converted to days
MsgBox D2 'now d2=23 June 12:00:05 PM

End
End Sub[/tt]

 

[mmilan]

Hypetia - that was kind of my original point...

CluelessChris:

Using IsDate does not tell you that you are dealing with a date variable - only that it is possible to convert whatever it is that you happen to be looking at into a date variable, should you wish to do so.

If you were to use my simple test with a string representation of a date, even though IsDate() would return true, the comparison would fail in a type mismatch...

As Hypetia explained much better than I, Date variables can be viewed as merely a number, which means that tests like Date1>Date2 can be used, so long as both Date1 and Date2 are Date variables, and not merely strings that look like dates.

I'm going to shut up now, because I suspect that I am probably clouding the issue for you. Your DateDiff approach is perfectly valid as well - but I think my own approach would run faster (though perhaps not appreciably so...)

mmilan.

 

[ZOR]

Many thanks everyone, sorry for delay in getting back. Hypetias example was very useful, opted for 1st bit. Thanks Strongm for the detail on datediff. Thanks mmilan for your input, which raises the next question I have, but before thanks ArtieChoke, yet to try it out, and of course CluLessChris who got a bit of info at the same time. Stars for all I think.
Now the next bit, which mmilan brought in my mind, how do I know what date/time settings the final pc has, ie the date could be 12/1/04 or 1/12/04. Is there a way to determine how the pc is set. Thanks again

 

[bmdb]

ZOR, try here for information on retrieving system date settings.

http://vbnet.mvps.org/code/locale/localeenumdates.htm

 

[ZOR]

Thanks bmdb, quite a useful reference. I think I might go down the road of putting a date picker control (hidden), I might be wrong but I think that would always give me a common output regardless of how a user sets his time format in Control Panel.
You get a star for the information, thanks again. If I'm wrong about the datepicker maybe someone can shout. Thanks

 

[ZOR]

Got it thanks
Found this reference which makes me independant of pc time settings. (Hope)

The Date function is similar to Now but does not return the time portion. The formatting is also done automatically, but not by the Control Panel. This function always returns a 10-character string (mm-dd-yyyy). In other words, these two lines of code are equivalent:

MsgBox "The current date is " & Format(Now, "mm-dd-yyyy")
MsgBox "The current date is " & Date

Thanks

 

[ZOR]

Still in the mud.
D1="01/06/03"
D2=Format(Now, "dd,mm,yyyy")

Is there a way to get a value difference from these two items. I cannot set D2 as a date.
Above left out Dim's.
So near but so far, thanks

 

[strongm]

Um...the Date function follows Control Panel short date settings on all my PCs...so not independant, I'm afraid.

The following is one method of getting the current date format strings for the user's regional locale. How useful it is in this particular scenario...well, I leave that to you to decide

Option Explicit

Private Const LOCALE_USER_DEFAULT = &H400

Private Const LOCALE_SSHORTDATE As Long = &H1F
Private Const LOCALE_SLONGDATE  As Long = &H20

Private Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long

Private Sub Command1_Click()
    Dim strFormat As String
    Dim result As Long
    
    result = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, strFormat, Len(strFormat))
    strFormat = Space(result)
    result = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, strFormat, Len(strFormat))
    MsgBox Left(strFormat, result - 1)
    
    strFormat = ""
    result = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SLONGDATE, strFormat, Len(strFormat))
    strFormat = Space(result)
    result = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SLONGDATE, strFormat, Len(strFormat))
    MsgBox Left(strFormat, result - 1)

End Sub

 

[ZOR]

Thanks very much strongm, I can use that nicely. Basically I am trying to do a rush expiry date bit in an application. Comparing to a hard coded date, compared to the system date. With what you sent over, I can set up 6/7 alternative date formats to get compared, depending on how there local date setting is made. Looks like now I can stop the procedure falling over if they play with date formats. Well thats it in principle. Many thanks again, dont think, but I will try to add a star.

 

[Hypetia]

I think, (may be I am wrong) you are getting confused on the issue that given varying date formats, how would you hardcode a date in code.

For example, the short date in US format is m/d/yyyy; whereas the same in UK format is dd/mm/yyyy. The order of M and D in the above format creates ambiguity.

It means that the following code generates different dates depending upon the current regional settings.
___
[tt]
Dim D As Date
D = "1/2/3"
MsgBox Format(D, "mmm d, yyyy")[/tt]
___

With US English format, this results in 'Jan 2, 2003' and with UK English format, this results in 'Feb 1, 2003' (Try this).
In each case, the date "1/2/3" is interpreted according to the current short date format.

The question is: how to hardcode a date so that it is always interpreted as a single date, independent of the current short date format?

There are two solutions.

1. Date variables are very flexible. You can assign them dates in a variety of string formats as long as they look like dates and are valid dates. For example all of the following assingments are valid.
___
[tt]
Dim D As Date
D = "1/2/3"
D = "1-2-3"
D = "1,2,3"
D = "Feb 1, 3"
D = "February 1"
D = "February 1, 03"
D = "February 1, 2003"[/tt]
'...
___

Note that in last four formats, there is no confusion in interpreting the actual date, except "February 1", which is interpreted as "Feb 1, 2004", as the year part is missing and current year is used as default.

The first three date formats are ambigous and depending upon the current date setting, they may be interpreted differently as explained above.

To avoid this ambiguity, never assign string values to date variables. Always use the number signs (#) to enclose date literals.

When a date is represented using # signs, it is automatically converted to US date format and interpreted accordingly.
___
[tt]
Dim D As Date
D = #2/1/2003#[/tt]
___

Note that this representation of date literals is always locale independant. Whatever date format you are using, these dates will always be interpreted in EN US format (m/d/yyyy) and thus, making the hardcoding of a date possible.

In the serveral date assignments shown above, if you replace the (") character with (#) character, the date literal will automatically get converted into EN-US format; i.e., #2/1/2003#

2. The other method for hardcoding a date is to use the DateSerial fucntion. This function accepts 3 arguments; year, month and day and returns a Date value which can be assigned to a Date variable. This function also does not confuse with the locale settings.
___
[tt]
Dim D As Date
D = DateSerial(3, 2, 1) 'always Feb 2, 2003[/tt]
___

Hope this makes some sense.

 

[mmilan]

Heh heh heh -

Strongm - he came, he saw, he APIed.

mmilan