Access Query

[knitex]

So i have this problem with a database. Under a section Date of Birth there are about 110000 entries. There are some that just have the year, for example 1978, I'm wondering if there is a way where it can automatically put in /00/00 after the 1978? so it would come out to be 1978/00/00. Anybody have any ideas? This is for Microsoft Access

[BitBlt]

What's the datatype of the Date of Birth column? If it is text, you can just use some string manipulation functions (mid(), format(), etc.) to break the string apart and re-assemble it however you want.

If it's date/time it becomes a little trickier, as you have to use the datepart() functions to pick your data apart.

If it's numeric then you have to convert it to a string using Str(), then use the mid() and format() functions to break it apart and re-assemble.

Here's some sample MSAccess SQL that might get you going:

SELECT Table1.StringDate, 
Right([Table1]![StringDate],4) + "/0/0" AS StringYear,
Table1.DateTimeDate, 
CStr(Year([Table1]![DateTimeDate])) + "/0/0" AS DateTimeYear,
CStr(DatePart("yyyy",[Table1]![DateTimeDate])) + "/0/0" AS AlternateDateTimeYear, 
Table1.NumericDate, 
Mid(CStr([Table1]![NumericDate]),1,4) + "/0/0" AS NumericYear 
FROM Table1

I think you can figure out the assumptions about datatype and how the dates are stored.

Hope this helps!

[chuckc]

With datatype date/time there is really no way to store an unknown month or day. You could default to say (for example) 1978/01/01, but then there would be no way to know if the actual date was 1-Jan-1978 vs. unknown month or day. If you prefer to store the date using numeric or string datatype then you can store the date as "19780000", but you lose the ability to easily do arithmetic with the data, that is, compute or query the person's age. I suppose the choice depends on the application.
Good luck!