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