Date comparison not functioning

[deanvilar]

hello there, my project is done...just noticed that my dates are not comparing properly ... it's working only if the year of returned date is less than the year of takeout date ... help me pleaaaasee =)

note: i divided the dates into 3 parts
e.g.:
takeoutday, takeoutmonth and takeoutyear
returnedday, returnedmonth and returnedyear

Private Sub txtReturnedYear_AfterUpdate()
Dim tDate As String
Dim rDate As String

tDate = Format(Me.cmbTakeOutDay & "/" & Me.cmbTakeOutMonth & "/" & Me.txtTakeOutYear, "dd/mm/yyyy")
rDate = Format(Me.cmbReturnedDay & "/" & Me.cmbReturnedMonth & "/" & Me.txtReturnedYear, "dd/mm/yyyy")

If CDate(rDate) < CDate(tDate) Then
    MsgBox "Please change return date!"
    Me.cmbReturnedDay.Value = ""
    Me.cmbReturnedMonth.Value = ""
    Me.txtReturnedYear.Value = ""
    
    Me.cmbReturnedDay.SetFocus
Else
    Me.txtReturnedYear.SetFocus
End If

Me.cmdClearReturnedDates.Enabled = True

End Sub

[TheSmileyCoder]

Step through your code and check that the values assigned to tdate and rdate are the values you expect them to be.

[deanvilar]

sir Smiley, yes ... its actually same value ...
for example if takeoutdate = 2/3/2013 and returneddate = 1/5/2012 ... its ok... but if takeoutdate = 2/3/2013 and returneddate = 1/3/2013 it doesnt alarm me to change returned date =(

[TheSmileyCoder]

If you make an example database containing just the form, the relevant controls, and that piece of code, I will be happy to spend some time looking it. You can upload it as a zipped file.

[deanvilar]

sure sir smiley, please find attached.

[TheSmileyCoder]

Nice.
I should be able to look at it this evening.

[deanvilar]

thank you sir smiley in advance ....

[TheSmileyCoder]

Okay I have taken a look at your form. I like the clean style gui you have setup, it seems professional.

I can see from your profile that you have a VB6 background, and when compared with what I see in form, I am guessing that you have run into a mistake alot of developers do when they first start using access. They make unbound forms and load/write date through the use of lots of code. Access comes with a built in system that will do all of that for you, if you use bound forms. The normal form wizard will help you do bound forms and they are so much easier to work with. Bound forms take care of recordset navigation, update, filters, sorts and much more. Furthermore the code you have is difficult to maintain since if you add a field to the table, you have a lot of code you now need to update. I strongly urge you to look into bound forms.


Back to the original problem. You are storing a date as a string, and manipulating it as a string, before converting it to date.
If you try typing this line of code into the above example at line 7:

Debug.Print Format(CDate(rDate), "dd/mm/yyyy") & " || " & rDate

And look at the resulting output in the immediate pane, you get the following for the date january 6th 2013:

01/06/2013 || 06/01/2013

Basically what happened is that the string you formatted as dd/mm/yyyy got read by access as mm/dd/yyyy, and after all how should Access know which date format you used? Access only sees a string, and tries to convert that to a date, the best it can. When you have a split date like that, with information stored in various fields, you should use the DateSerial function instead, to convert it to a date, since you can specify which part is the year, month and day.

Best of luck with your project.

[deanvilar]

thank you for the compliment Sir Smiley, yes I was a VB 6.0 programmer 8 years ago ... and got stuck in SAP which made me forget all those logical stuff in my mind ... anyways ... yep i tried using bound, its just i need more time to figure out how it really works in access, i cant manipulate them normally as what i really wanted that's why i used vba coding to do this ... a little bit of reminiscing and bump on my head i remembered some ways how to perform some operations ...

thanks for the suggestion sir smiley I will try using DateSerial function ... and ill let u know sir .. thank you again.

[NeoPa]

DeanVilar:
note: i divided the dates into 3 parts

As soon as I read that I thought it would be the root of your problems. Have a look at Literal DateTimes and Their Delimiters (#) and see if it makes better sense to work with dates rather than strings. It really does make more sense.

I believe that's what Smiley is saying in a different way.

[deanvilar]

thank you sir NeoPa .... am going to check that link ...thanks