Date problem
-
I have made this database which use ms-access and the data are enter through vb6. On the form the date entered were display as they are entered but on the access table is has changed for example 1 jan 2008, 25 jan, 2008 were display like 01-jan-08 and 25-jan-08 respectively which is okay, but 4 july 2008 happen to be 07-apr-08 and 12 oct 2008 becomes 10 dec 2008. Can any one tell me how some dates gets change in the .mdb file while some doesnot change, and even they are change in the ms-access database file when display them on the form they look still okay as enter. Would be grateful is anyone throw light on this. Thank you. SZ
-
I have made this database which use ms-access and the data are enter through vb6. On the form the date entered were display as they are entered but on the access table is has changed for example 1 jan 2008, 25 jan, 2008 were display like 01-jan-08 and 25-jan-08 respectively which is okay, but 4 july 2008 happen to be 07-apr-08 and 12 oct 2008 becomes 10 dec 2008. Can any one tell me how some dates gets change in the .mdb file while some doesnot change, and even they are change in the ms-access database file when display them on the form they look still okay as enter. Would be grateful is anyone throw light on this. Thank you. SZ
It is a long time since I used Access and VB6 but as I recall the dates in the mdb file are always in American format (mm-dd-yyyy). What I don't understand is how 28 Jan 08 remains untouched! Possibly the problem is that 28 Jan 08 does not have an American equivalent so is forced to what happens to be the correct format. You should set something up to specifically format your dates as you want to see them rather than depend on the built in stuff.
-
I have made this database which use ms-access and the data are enter through vb6. On the form the date entered were display as they are entered but on the access table is has changed for example 1 jan 2008, 25 jan, 2008 were display like 01-jan-08 and 25-jan-08 respectively which is okay, but 4 july 2008 happen to be 07-apr-08 and 12 oct 2008 becomes 10 dec 2008. Can any one tell me how some dates gets change in the .mdb file while some doesnot change, and even they are change in the ms-access database file when display them on the form they look still okay as enter. Would be grateful is anyone throw light on this. Thank you. SZ
Internally, the dates are not stored in any format. They are a serial number that represents a date/time. The problem appears to be that whatever code is entering dates into the database is using methods that are no culture-tolerant nor consistant acrossed the code. The common mistake that causes this is using string concatentation to build SQL queries using dates:
' The BAD way... Dim query As String query = "INSERT INTO table VALUES (#" & somedatevariable & "#)"You should be using parameterized queries instead. Use the OleDbParameter class to handle dates and times for you. You can find a good example of this here[^].
A guide to posting questions on CodeProject[^]
Dave Kreskowiak -
I have made this database which use ms-access and the data are enter through vb6. On the form the date entered were display as they are entered but on the access table is has changed for example 1 jan 2008, 25 jan, 2008 were display like 01-jan-08 and 25-jan-08 respectively which is okay, but 4 july 2008 happen to be 07-apr-08 and 12 oct 2008 becomes 10 dec 2008. Can any one tell me how some dates gets change in the .mdb file while some doesnot change, and even they are change in the ms-access database file when display them on the form they look still okay as enter. Would be grateful is anyone throw light on this. Thank you. SZ
Hi Dear, Check your System's Date and Time Settings and make it DD-MM-YYYY, similarly at the time of save date into the access table use the format function like format(dtpicker1.value,"dd-mm-yyyy"). May this helps you...
-
I have made this database which use ms-access and the data are enter through vb6. On the form the date entered were display as they are entered but on the access table is has changed for example 1 jan 2008, 25 jan, 2008 were display like 01-jan-08 and 25-jan-08 respectively which is okay, but 4 july 2008 happen to be 07-apr-08 and 12 oct 2008 becomes 10 dec 2008. Can any one tell me how some dates gets change in the .mdb file while some doesnot change, and even they are change in the ms-access database file when display them on the form they look still okay as enter. Would be grateful is anyone throw light on this. Thank you. SZ
Access interprets a date string in American format first. When that fails, it takes a look at the date settings of your computer, and tries again with those settings (or the other way round - I am not sure). Hence, on a non-American computer the outcome is sometimes correct, sometimes wrong. Parameterized queries - as suggested above - are the solution.
-
Access interprets a date string in American format first. When that fails, it takes a look at the date settings of your computer, and tries again with those settings (or the other way round - I am not sure). Hence, on a non-American computer the outcome is sometimes correct, sometimes wrong. Parameterized queries - as suggested above - are the solution.