Working with dates

[access09]

How do I format this dates into a proper data format such as dd/mm/yyyy

Examples of the way the dates are stored in an access database are as follows:

020196 - (Stands for 02/01/1996)
160799
140502
231105
260509

 

[namliam]

1) This format is BAD, but probably cannot be helped
2) If you just want to fix the display... Just put in a format in the display
3) If you want to convert to a proper date:
Dateserial (Year, Month, Day) will do that for you.... Just use the Left, Right and Mid functions to extract the needed parts and you should be good to go...

I hope your answer is here.

Good Luck!

 

[DCrake]

Where have you obtained the said dates? have they been imported? What data type is the field in which they are stored?

First of all you need a query to convert the string date into a date format

NewFormat:CDate(Left(OldFormat,2) & "/" & Mid(OldFormat,3,2) & "/" & Right(OldFormat,2))

Then use an update query or a simple copy and paste to overwrite the incorrect format. Remember you will need to change the field type to Date/Time.

written before mailmans response.

David

 

[access09]

The dates are stored in an access database and is a text data field.

 

[namliam]

Dates should NEVER be stored as text, rather store as Dates...

CDate or Dateserial will both make the display proper (IMHO Dateserial is slightly preverable)
The field then is display only though... no more editting unless you change the source format and stop using CDate or Dateserial

 

[DCrake]

Re my earlier post if you change the field width to 10 and use the syntax to create a date out of the string by way of a query. Then highlight the converted column and select copy. Highlight the text field and select paste to overwrite the old values with the new values then change the data type of the field to Date/Time and save the changes. that will work.

Make a backup first though to be on the safe side.

David

 

[MStef]

Look at "DemoWorkingWithDatesA2000.mdb" (attachment, zip).

1) in your Table put a new field NewDate, (DateType = Date/Time, Format = ShortDate). Run Query2Update.
2) If you want in another table. Look at TableNew, (DateType, Format).
Run Query1Append.
3) Your System Date must be dd/mm/yyyy

 

[namliam]

3) Your System Date must be dd/mm/yyyy

This is why I say use Dateserial, this is independent of your system date

 

[access09]

When i use the cdate function to correct the format it cant do it properly. I think this is because it is checcking the date in the american format. See results of the function below:

230493 - 4/23/1993
230494 - 4/23/1994
310598 - 4/31/1998
310500 - 5/31/2000
310502 - 5/2/1931
310506 - 5/6/1931
250303 - 3/3/2025
190304 - 3/4/2019


Is this happening because it is using the american format? Is there anything i can do to stop it?


PS. DateSerial is changing everthing to year 2009 which is not what i want

 

[MStef]

Set your System date on dd/mm/yyyy
I think it is d/m/yyyy

 

[access09]

How do you set your system date. thanks

 

[MStef]

Look at attachment (word)

 

[namliam]

When i use the cdate function to correct the format it cant do it properly. I think this is because it is checcking the date in the american format. See results of the function below:

230493 - 4/23/1993
230494 - 4/23/1994
310598 - 4/31/1998
310500 - 5/31/2000
310502 - 5/2/1931
310506 - 5/6/1931
250303 - 3/3/2025
190304 - 3/4/2019


Is this happening because it is using the american format? Is there anything i can do to stop it?


PS. DateSerial is changing everthing to year 2009 which is not what i want

How are you doing the dateserial??? This should do your trick??!!
Dateserial (right(yourfield,2), mid(yourfield,3,2),left(yourfield,2))

 

[WIS]

How do I format this dates into a proper data format such as dd/mm/yyyy

Examples of the way the dates are stored in an access database are as follows:

020196 - (Stands for 02/01/1996)
160799
140502
231105
260509

I have attached db1 for you to look at. It is very simple to do - I used an update qry.

But you must have the following done:

1. Go to Windows Control Panel -> International and set the date as "dd/mm/yyyy".

2. In my db you'll see the Date fld formatted as "dd/mm/yyyy". Do the same in any forms as well. This seems to be an undocumented feature and I've posted a few responses on this as well as a general response about 6 weeks ago on "Dates UK format".

Delete the date entries in db1 and run the update qry again.

 

[namliam]

I have attached db1 for you to look at. It is very simple to do - I used an update qry.

No no no! Bad bad worse!

1. The need for the dd/mm/yyyy format is BAD, relying on the regional settings for a one of may be 'good enough' but in general it is bad

2. Why is the format an undocumented feature?? Any why format a table? It should never be visible to a user anyways...

3. No conversion, you are letting access do the thinking/converting for you. This is very bad, you are not using CDate, datevalue or Dateserial at all.
You MUST tell access what to do and MUST (IMHO) use a conversion function to convert your values!!!
Dateserial is the best of these because it is the only one that doesnt rely on any regional settings.

 

[WIS]

Look at "DemoWorkingWithDatesA2000.mdb" (attachment, zip).

1) in your Table put a new field NewDate, (DateType = Date/Time, Format = ShortDate). Run Query2Update.
2) If you want in another table. Look at TableNew, (DateType, Format).
Run Query1Append.
3) Your System Date must be dd/mm/yyyy

How interesting - we both arrived at a very similar method. I've just looked at your db.

Meanwhile, "Access09", has your problem been solved?

 

[MStef]

Hello WIS!
Yes, it is so similar, I notice that I don't need "IIf" in query.

 

[namliam]

Still having access doing implicit conversions

Bad bad very bad!

 

[xolo]

I have similar problem. I am trying to retrieve the date in my invoice where the price is at the highest. My windows sytem date is "dd/mm/yy" and all the date format of my project is also "dd/mm/yy".

My codes are something like this:

Function FDate(vProductID As Variant, Optional vAsOfDate As Variant) As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrProduct As String
Dim strAsOf As String
Dim strDateClause As String
Dim strSQL As String
If Not IsNull(vProductID) Then
Set db = CurrentDb()
StrProduct = vProductID
If IsDate(vAsOfDate) Then
strAsOf = "#" & Format$(vAsOfDate, "dd/mm/yy") & "#"
End If
If Len(strAsOf) > 0 Then
strDateClause = " AND (Inv.InvDate <= " & strAsOf & ")"
End If
strSQL = "SELECT TOP 1 InvSub.Price, Inv.InvDate" & _
"FROM Inv INNER JOIN InvSub ON Inv.InvID= InvSub.InvID " & _
"WHERE ((InvSub.ProductID= '" & StrProduct & "')" & strDateClause & _
") ORDER BY InvSub.Price DESC;"
Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
FDate = InvDate
End If
End With
rs.Close
End If
Set rs = Nothing
Set db = Nothing
End Function

When I call this function, it makes "20/04/09" diplayed as "30/12/99".

Can anyone help?

 

[WIS]

strAsOf = "#" & Format$(vAsOfDate, "dd/mm/yy") & "#"

Use "dd/mmm/yyyy" - I found that "dd/mm/yyyy" still gave me US dates.

When I call this function, it makes "20/04/09" diplayed as "30/12/99".

Can anyone help

If it is showing that date then may be it is returning a -1 (True) - all dates are numbers with 31/12/1899 as 0.

This is the very reason why I put "dd/mm/yyyy" in the Format ppty of all my tbls and frms - is "30/12/99" "really 30/12/1999" or "30/12/1899" and set the system date in Windows as "dd/mm/yyyy".