convert dd and mm and yy fields to one dd/mm/yy field

V

Vic33

I have 3 x character fields for dd, mm, yy. How do I update my table to show
these as one date field?
Thks
 
A

Allen Browne

Create a query, and type an expression like this into the Field row:
IIf(IsNumeric([dd]) AND IsNumeric([mm]) AND IsNumeric([yy]),
DateSerial([yy], [mm], [dd]), Null)

You don't want to store both the text and the date in the table, as this
could give you inconsistent results. If you are trying to convert the text
into a real date (so you can remove the 3 text fields), then turn the query
into an Update query, and put the expression in the Update row in query
design under your date field.
 
V

Vic33

Phew, you assume that I know what I'm doing! Can't I just use the dateserial
function? I've tried this: set [datefield] = dateserial([day],[mo],[yr]);
to no avail. My three fields are txt fields, not numeric. I'm doing
something basic wrong but not sure what.
Regds
Vic

Allen Browne said:
Create a query, and type an expression like this into the Field row:
IIf(IsNumeric([dd]) AND IsNumeric([mm]) AND IsNumeric([yy]),
DateSerial([yy], [mm], [dd]), Null)

You don't want to store both the text and the date in the table, as this
could give you inconsistent results. If you are trying to convert the text
into a real date (so you can remove the 3 text fields), then turn the query
into an Update query, and put the expression in the Update row in query
design under your date field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Vic33 said:
I have 3 x character fields for dd, mm, yy. How do I update my table to
show
these as one date field?
Thks

.
 
R

Rick Brandt

Vic33 said:
Phew, you assume that I know what I'm doing! Can't I just use the
dateserial
function? I've tried this: set [datefield] =
dateserial([day],[mo],[yr]);
to no avail. My three fields are txt fields, not numeric. I'm doing
something basic wrong but not sure what.

DateSerial's arguments go Year, Month, Day. You have them backwards.

Allen's code was simply trying to make sure that three entries are strings
that represent numeric values. If someone were to enter alpha-characters
DateSerial would raise an error.
 
V

Vic33

Ahhh...the simple explanation goes a long way. Thanks! I should be in 'new
user' forum I realise now. All sorted, much appreciated.
Regds
Vic

Rick Brandt said:
Vic33 said:
Phew, you assume that I know what I'm doing! Can't I just use the
dateserial
function? I've tried this: set [datefield] =
dateserial([day],[mo],[yr]);
to no avail. My three fields are txt fields, not numeric. I'm doing
something basic wrong but not sure what.

DateSerial's arguments go Year, Month, Day. You have them backwards.

Allen's code was simply trying to make sure that three entries are strings
that represent numeric values. If someone were to enter alpha-characters
DateSerial would raise an error.
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top