Problems with Access/VB date

[Seba Sama]

Hi!

I searched for this one with no results.

I have an Access table with a column Date_of_birth witch has date/time format like dd/mm/yyyy.
I develop a VB6 application that needs to read, write, update data in the mdb.
INSERT:
The user inserts this Date_of_birth in a textbox. I use this for inserting (I will write just the problem-part of the code):

a= Format$(Format$(txt_dateofbirth, "##/##/####"), "dd/mm/yyyy")

datacon.Execute ("" _
   & "INSERT INTO MyTable (Date_of_birth) " _
   & "VALUES (#" & a & "#)")

Then, for updating, I have a form witch ritrieves data into textboxes. A user can modify what is needed and then must press the UPDATE button.

a = Format$(Format$(Format(txt_dateofbirth.Text, "dd.mm.yyyy"), "##/##/####"), "dd/mm/yyyy")
[INDENT]'This is because a user can modify the date like dd.mm.yyyy or let it like dd/mm/yyyy (if this a format like the one in the INSERT part will result in smth like /03/8521)
[/INDENT]
datacon.Execute("" _
   & "UPDATE MyTable "
   & "SET Date_of_birth  = #" & a & "# " _
   & "WHERE Name = '" & ClientName & "'"

All the above works. But not OK. After UPDATE I want to see the results and... it switches the day and the month

insert: 05.10.2000
view: 05/10/2000
OK
update with: 05/10/2000
view: 10/05/2000
update again without modify
view: 05/10/2000
and so on. It keeps switching 05 and 10

BUT: If Inserts 18.05.2000
view: 18/05/2000
update without modify
view: 18/05/2000 THIS IS OK

What goes wrong?

IMPORTANT (maybe):
The computer I work has Win XP and Access both italian versions. I may run the program on english version. I need smth witch has nothing to do with regional options.

Any suggestion is highly appreciated.
Thx!

[Seba Sama]

Small update: from the INSERT step it goes wrong, meaning that inserting "#05/10/2000#" reads "10/05/2000".

Access is converting as it likes....

Any ideas?

PS I'm one step away to change the column as string, not date...

[vb5prgrmr]

Check the format property of the field or you could just change the field to long and store it as a number in the format of yyyymmdd since you are only storing the date portion. This causes you a little more problems as you have to format it going in and parse it coming out but quering against it is much faster than a date field. One other though is try passing it as a string ( '" & a & "'" ) (I think it used to work been so long...)

Good Luck

[Seba Sama]

Thx, I already formated the input data as string. Using " ' " instead of " # " forces access to accept the data as it is.
The only problem is that now I have to handle that string to be in the correct format dd/mm/yyyy. If the user types 05/012/2005 or 05/01/205 is not ok.

Better use EditMask Control.

Thx for helping!