I'm stumped by this code

[ramblinwreck]

I have code that is activated by a command button. One of the first things the code does is check the date that a user has just input and in the specific code below, it makes sure the date is not a date in the future.

When this code executes however, the if then below is determined to be "true" whereas it should have determined it to be false.

The user enters a specific date of 6/09/2007, the DATE function tells the program that today's date is 6/10/2007 and then this code executes:

Continue:
If txtDate.Text > Date Then 'can't enter a date that's in the future.
    MsgBox "you have entered a date in the future. Check your date."
    txtDate.Text = Date
    txtDate.SetFocus
    Calendar1.Value = Date
    Exit Sub
End If

As I said above, when the if statement is evaluated, it deems that 6/9/2007 is greater than 6/10/2007 and the wrong lines of code are subsequently executed.

Hoping you can help me solve this problem.

 

[boblarson]

Change this:

If txtDate.Text > Date Then 'can't enter a date that's in the future.

To this:

If CDate(txtDate.Text) > Date Then 'can't enter a date that's in the future.

 

[ramblinwreck]

hmmm. Mind boggling. I changed the code and it works. Before I posted orig post however, I checked in the immediate window the following:

?txtDate.text and got 6/9/2007

then I tried
?cdate(txtDate.text) and got 6/9/2007

and then I tried
?Date and got 6/10/2007.

But I changed the code as you recommended and it works. any idea why it's so quirky?

thanks.

 

[boblarson]

If you are going to be comparing something then you need to make sure that they are the same datatypes and the CDate ensures that Access sees the text box value as a date instead of text.

 

[Guus2005]

dates are allways tricky. it all depends on the user's regional settings. That's why i allways convert them to integers:

If cint(me.text1) > cint(date) then 'Date in the future.

I also store them as integers in the database. Time is stored separately.

HTH

 

[Bigmo2u]

I have a similar problem that I am trying to solve. I want after update to check to see that the date in the DN_date field is not more 21 days older than the Recvd_date.

If CInt(Me.DN_Date) > CInt(Me.Claim_Received + 21) Then MsgBox "This claim is over 30 days old. A DN should not be put on this claim!!!"

Any help would be greatly appreciated.....

I have searched high and low on this site for a similar problem and this is the only one I could find....

 

[KeithG]

if datediff('d',cdate(me.DN_Date),cdate(me.Claim_Received))>=21 then
......

 

[GrexP]

The test in the text box has a data type of String. The Date key word returns a data type of Date. You need to convert the String in the text box to Date and then do your comparison. You should also make sure that it is a valid date in the text box.

txtDate.SetFocus
If Not IsDate(txtDate.Text) Then
    MsgBox "You did not enter a valid date. Please try again.", vbInformation
    txtDate.SelStart = 0
    txtDateSelLength  = len(txtDate.Text)
    Exit sub
End If

If DateValue(txtDate.Text) > Date Then 'can't enter a date that's in the future.
    MsgBox "you have entered a date in the future. Check your date."
    txtDate.Text = Date
    txtDate.SetFocus
    Calendar1.Value = Date
    Exit Sub
End If

 

[Bigmo2u]

GrexP - what if the DN_Date box can be left blank or they later go back delete the date because it was the wrong Claim? will the user get an error message that the data is missing?

KeithG - if datediff('d',cdate(me.DN_Date),cdate(me.Claim_Rece ived))>=21 then
the 'd' is causing a compile error. what is the 'd' for???

 

[GrexP]

GrexP - what if the DN_Date box can be left blank or they later go back delete the date because it was the wrong Claim? will the user get an error message that the data is missing?

KeithG - if datediff('d',cdate(me.DN_Date),cdate(me.Claim_Rece ived))>=21 then
the 'd' is causing a compile error. what is the 'd' for???

txtDate.SetFocus
If Len(Trim(txtDate.Text)) > 0  Then ' only if it is populated

    If Not IsDate(txtDate.Text) Then
        MsgBox "You did not enter a valid date. Please try again.", vbInformation
        txtDate.SelStart = 0
        txtDateSelLength  = len(txtDate.Text)
        Exit sub
    End If
    
    If DateValue(txtDate.Text) > Date Then 'can't enter a date that's in the             future.
        MsgBox "you have entered a date in the future. Check your date."
        txtDate.Text = Date
        txtDate.SetFocus
        Calendar1.Value = Date
        Exit Sub
    End If
End If

And try double quotes around the d on the DateDiff call.

 

[Bigmo2u]

Thanks GrexP, I will give it try...... I will post back if I have problems still.

You guys are great....