Help with MS Access date string conversion?

Question

I have a string date in Access which I'm trying to change to a proper date to be able to sort by it. The current format is ddmmyy eg. 311209 (this is named Expr1 as I had to remove the preceeding 9 from the number I inherited) but when I try to change it using Cdate it comes up with some very strange numbers eg 260609 is coming up as 28 August 2695, this may be the way the settings are (ie possibly using American dates) but I can't change these since it's my work computer and I've no administrator access. Does anyone know what I can do to correct this?

Answer 1

CDate() is viewing 260609 as the number of days since 1-1-1900, you'll need to inject dashes or slashes to derive a recognized date format, before passing to CDate(). Note that if you rearrange it to yyyy-mm-dd, and impute the century, you won't need to worry about locale setting.

Answer 2

Dates are the number of days since 1/1/1900. To view this number as a date. Simply go into your textbox settings and set the format to date. This will make access calculate the date and display it for you in the text box.



These numbers can be easily sorted. As 28 August 2695 is 260609. 29th August would be 260610. So sorting them normally will put the dates into cronological order.



EDIT: just read the other 2 answers. Those solutions will only work if the number was in BCD format. These numbers are not so you cant just split them up to get the day month and year. For example 1/1/1901 would be 365 as it is 1 year after 1/1/1900. By splitting this up they way you said, you would get 0th of march 65 which is an invalid date and the year 65 is a long way from the year 1901.



Andrew Ls solution wont acctually do anything. It just will convert the string into a date then back into a string again. You need to convert it into a date using Cdate() then this will produce the number you need for sorting. Access will automatically convert this into a readable date if you set the textbox format to "Date".

Answer 3

ensure you first supply the referce of ADO by document>project>reference then Dim dbPath, strInfo, strDBPass As String ' it somewhat is to open the database connection at as quickly as so as that it does no longer could desire to be ' opened in each module dbPath = App.direction & "stock_control.mdb" strInfo = "provider=Microsoft.Jet.OLEDB.4.0; information source =" & dbPath & "; Jet OLEDB:DATABASE PASSWORD=babar123" Cn = New ADODB.Connection Cn.Open (strInfo) ' it somewhat is the relationship string for all the MHFlexgrids strDBPass = App.direction + "stock_control.MDB;Jet OLEDB:Database Password=babar123;" strGrid = "provider=Microsoft.Jet.OLEDB.4.0;information source=" & strDBPass frmLogin.coach

Answer 4

Create an update query with that function in it

Answer 5

I don't think it's a regional issue, as your work pc will also have European settings. Try



Cdate(left([yourdate],2) & "/" & mid([yourdate],3,2) & "/" & right([yourdate],2))



There is probably a better way, but it seems to work for me.