Date Fields (97)

Topic

New Reply

Hi can anyone help. I hav a db with three fields – DOBDay, DOBMonth and DOBYear. How can I truncate this into one field with a date format ## / ## / ####? Thanks

Reply | Quote

Replies

I assume that DOBDay is a numeric field with values 1…31, DOBMonth is a numeric field with values 1…12, and DOBYear is a numeric field with values like 1970. If not, please post back and provide detailed information.

Create a query on the table*. Add a calculated column to the query grid:

DOB: DateSerial(DOBYear, DOBMonth, DOBDay)

Set the Format property of this field the way you want it. If you base your forms and reports on the query instead of the table, you can display the complete date.

* Pedantic footnote: a database is a collection of tables, queries etc. A database doesn’t have fields, but a table does.

Reply | Quote

Actually, just to make it interesting the fields are textual at the moment, but I can get round that. Thank you.

Reply | Quote

If DOBMonth contains a month name, such as “Dec” or “December”, you can probably use

DOB: DateValue(DOBDay & ” ” & DOBMonth & ” ” & DOBYear)

Reply | Quote

If DOBMonth contains a month name, such as “Dec” or “December”, you can probably use

DOB: DateValue(DOBDay & ” ” & DOBMonth & ” ” & DOBYear)

Reply | Quote

Actually, just to make it interesting the fields are textual at the moment, but I can get round that. Thank you.

Reply | Quote

I assume that DOBDay is a numeric field with values 1…31, DOBMonth is a numeric field with values 1…12, and DOBYear is a numeric field with values like 1970. If not, please post back and provide detailed information.

Create a query on the table*. Add a calculated column to the query grid:

DOB: DateSerial(DOBYear, DOBMonth, DOBDay)

Set the Format property of this field the way you want it. If you base your forms and reports on the query instead of the table, you can display the complete date.

* Pedantic footnote: a database is a collection of tables, queries etc. A database doesn’t have fields, but a table does.

Reply | Quote