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!

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

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

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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.