Date problems

[Rupelo]

Hello everyone, I have a little problem and would like a little assistance with it.

Here is my problem:

I am working with a database that has to store the date of birth of individuals. The pervious database adminitstrator had this field with the name"d300-dob". However, he had the Data Type as text, Field Sizse at 8 and input Mask is "99/99/99".

Now I have been charged the task of performing calculations on this field. but there is another problem the date is in the format yy/mm/dd.

My question is how do i change the d300-dob field from its current format to the standard with the Date/Time data type, so i could have the date in the format dd/mm/yy.

Any assistance would be much appreciated as my supervisor has been on my
A#$

thanx
Rupelo

 

[raskew]

Hi -

You'll need to add another field to your table, set to datetime data format. Create an update query, updating your new field to the dateserial() value of the old text field. Here's an example from the debug (immediate) window:

x = "06/11/21"
y = dateserial(left(x,2), mid(x,4,2), right(x, 2))
? y
11/21/06 
'to prove that it is datatime data type:
? cdbl(y)
 39042

Once you've performed the update (after first backing up your table) and you're satisfied with the result, you can then go in and delete your old field and rename the new field.

HTH - Bob

 

[Rupelo]

When I do this
DateSerial(Left d300-dob,2), mid(d300-dob,3,2), Right(d300-dob,2))

I am getting a an error... It says "Data type Mismachin criteria Expression"

 

[gemma-the-husky]

your syntax for left isnt quite right -not sure if its a typo, because you have 3 left brackets and four right brackets

your mid might need to strat at pos 4 not pos 3 if you have the slashes IN your data

ie 06/12/31 for 31st december 2006

DateSerial(Left (d300-dob,2), mid(d300-dob,4,2), _Right(d300-dob,2))

it should end up as dateserial(year,month,day)

 

[raskew]

Hi -

There's a syntax problem. Change your statement to read and see if that helps

DateSerial(Left[COLOR="Blue"]([/COLOR]d300-dob,2), mid(d300-dob,[COLOR="blue"]4[/COLOR],2), Right(d300-dob,2))

Bob